CODEWITHSUNDEEP
sqlexcelsql-server-2008pivot-table
user3486773
user3486773

Reputation: 1246

Date specific calculation sql

using SQL 2008 r2 and using my Q data in excel pivot chart (joy)!

So I have a query that gives me results like this: (among many other results but this is the data minus the 'noise'. So I cannot remove/filter any data but only add to it in new columns):

    month  user  target   
    1      001    1000
    1      001    1000
    1      002    2000
    1      001    1000
    2      002    2000
    2      002    2000
    3      002    2000
    3      002    2000
    4      002    2000

What I want would look like this:

    month  user  target  New col
    1      001    1000    1000
    1      001    1000       0
    1      002    2000    2000
    1      001    1000       0
    2      002    2000    2000
    2      001    2000    1000
    3      002    2000    2000
    3      002    2000       0
    3      002    2000       0
    3      001    2000    1000
    4      002    2000    2000
    4      001    1000    1000

So basically I want each user's target to only display for the first instance of a new month, then display '0' for every instance of month thereafter. This query takes an annual target and divides it by 12 to get on a monthly level. The target amount for user 001 in my example would be the sum of the 'new col' and not the 'target' column as when calculating the annual / 12 it just puts that amount in wherever there is a user record and summarizing that column would be far more than the actual target.

So this is saying for every month, user 001 has a monthly target of 1000, and user 002 has a monthly target of 2000. This is why I just cannot summarize the target column.

Why do I need it in this format? because this data is being used in a pivot chart in excel with other data, and I need to summarize it and aggregate as running total in excel. So I do not want to summarize the 'target column' but rather the 'new col' and then when I look at all employees on my pivot, it totals the target for those employees and when I drill down to an individual, it only shows me that individual's target.

This has had me stumped for days searching all over the nets with no joy. If anyone is really trying to help but confused by what I'm asking/wanting I will be here to update/edit anything that is needed. This would be so huge if I could get this resolved!!!

Upvotes: 0

Views: 85

Answers (1)

Steve Jackson
Steve Jackson

Reputation: 93

Is this in line with what you are after?

CREATE TABLE [dbo].[Users](
[month] [int] NOT NULL,
[user] [int] NOT NULL,
[target] [int] NOT NULL
) ON [PRIMARY]

GO

DELETE FROM users

insert into users ([month], [user], [target]) values
(1,001,1000), (1,001,1000),(1,002,2000),(1,001,1000), (2,002,2000), (2,002,2000), 
(3,002, 2000), (3,002,2000), (4, 002, 2000);

With RowAdded AS (
SELECT ROW_NUMBER() OVER( Partition By [user],[month] order by [user]) AS RowNum,
U.*
FROM users U),

Targeted AS (
    SELECT
   RA.[month],
   RA.[user],
   CASE WHEN RowNum = 1 THEN [target]
    ELSE 0 END AS target
   FROM RowAdded RA)

SELECT * FROM Targeted

Upvotes: 1

Related Questions