Multi Row Sum and calculated field

Question

I am trying to find a way to insert a calculated row into SQL query that is based off of a subtracted sum from a separate total

For example:

Name | Title | eID | Spend_Category |Jan|Feb|...|Nov|Dec|Sum of Months|
Jay  |  CEO  | 1   | Lodging        |10 |20 |...| 0 |30 | 60          |
Jay  |  CEO  | 1   | Airlines       |20 |40 |...| 0 |60 | 120         |
Jay  |  CEO  | 1   | Auto           |10 |20 |...| 0 |30 | 60          |
Paul |  VP   | 2   | Lodging        |10 |20 |...| 0 |30 | 60          |

On a separate table, I have each employee ID's total spend. What I am trying to do is create a new row and for each user(eID) with a new Spend_Category of "Other" that sums up all of the different Spend for that user and subtracts it from the total. So if Jay had a total spend of $500

For example:

Name | Title | eID | Spend_Category |Jan|Feb|...|Nov|Dec|Sum of Months|
Jay  |  CEO  | 1   | Lodging        |10 |20 |...| 0 |30 | 60          |
Jay  |  CEO  | 1   | Airlines       |20 |40 |...| 0 |60 | 120         |
Jay  |  CEO  | 1   | Auto           |10 |20 |...| 0 |30 | 60          |
Jay  |  CEO  | 1   | Other          |...................|$500-240=$260|
Paul |  VP   | 2   | Lodging        |10 |20 |...| 0 |30 | 60          |
...etc...

I am certainly open to ideas and I am very new to SQL. Here is where I am at thus far and would appreciate the group's expertise.

SELECT 
SC.Full_Name, 
TS.Job_Title,
TS.Card_Type,
SC.Employee_ID, 
SC.Genesis_Industry,
SC.May,
SC.June, 
SC.July, 
SC.August,
SC.September,
SC.October,
SC.November,
SC.December,
SC.January,
SC.February,
SC.March,
SC.April, 
(NZ(SC.May,0)+NZ(SC.June,0)+ NZ(SC.July,0)+ NZ(SC.August,0)+NZ(SC.September,0)+NZ(SC.October,0)+NZ(SC.November,0)+NZ(SC.December,0)+NZ(SC.January,0)+NZ(SC.February,0)+NZ(SC.March,0)+NZ(SC.April,0)) AS Category_Spend,
TS.Total_Spend
FROM Spend_Category SC
left join Top_Spender TS
on SC.Employee_ID=TS.Employee_ID

Kyle Mac Kyle Mac · Accepted Answer · 2014-05-30T21:44:01

I created my own quick table and wrote a brief example of how this might be done. However, in working through this I noticed that with the way I have written this you have to know which categories you want as there own line item and then throw the other categories into the "Other" field.

create table #test (firstname varchar(max), 
                category varchar(max), 
                jan int, feb int, mar int, apr int, may int, jun int, jul int, aug int, sep int, oct int, nov int, dec int)

insert into #test values
('Joe','Lodging',100,50,874,7852,50,50,6,0,0,0,0,0),
('Joe','Airlines',100,510,874,60,20,50,6,0,0,0,0,0),
('Joe','Auto',100,50,874,60,50,50,6,0,0,0,0,0),
('Joe','Lunch',500,50,874,60,50,50,6,0,0,500,0,0),
('Joe','Insurance',100,50,874,4111,50,50,6,0,0,0,0,0)

select 
    firstname, 
    Other = sum(case when category not in ('lodging','airlines','auto') 
                     then yearly else 0 end)
into #tbl
from #test t
cross apply (select yearly = isnull(jan,0) + isnull(feb,0) + 
    isnull(mar,0) + isnull(apr,0) + isnull(may,0) + isnull(jun,0) + 
    isnull(jul,0) + isnull(aug,0) + isnull(sep,0) + isnull(oct,0) + 
    isnull(nov,0) + isnull(dec,0)) y
group by firstname

select 
    t.firstname,
    category, 
    yearlyTotal

from #test t
cross apply (select yearlyTotal = isnull(jan,0) + isnull(feb,0) + 
    isnull(mar,0) + isnull(apr,0) + isnull(may,0) + isnull(jun,0) + 
    isnull(jul,0) + isnull(aug,0) + isnull(sep,0) + isnull(oct,0) + 
    isnull(nov,0) + isnull(dec,0)) y
where category in ('lodging','airlines','auto')

UNION 

select 
    firstname, 
    'Other' as Category, 
    other as yearlyTotal
from #tbl t

Multi Row Sum and calculated field

1 Answers