How could I turn this into a total by Entity rather that just having a line total by day. I would like to group all of the days for an entity
and produce a total.
Thanks.
SELECT entity
,description
,[1st] = SUM(CASE WHEN datepart(dd,effdate) = '1' THEN expamt ELSE 0 END)
,[2nd] = SUM(CASE WHEN datepart(dd,effdate) = '2' THEN expamt ELSE 0 END)
,[3rd] = SUM(CASE WHEN datepart(dd,effdate) = '3' THEN expamt ELSE 0 END)
,[4th] = SUM(CASE WHEN datepart(dd,effdate) = '4' THEN expamt ELSE 0 END)
,[5th] = SUM(CASE WHEN datepart(dd,effdate) = '5' THEN expamt ELSE 0 END)
,[6th] = SUM(CASE WHEN datepart(dd,effdate) = '6' THEN expamt ELSE 0 END)
,[7th] = SUM(CASE WHEN datepart(dd,effdate) = '7' THEN expamt ELSE 0 END)
,[8th] = SUM(CASE WHEN datepart(dd,effdate) = '8' THEN expamt ELSE 0 END)
,[9th] = SUM(CASE WHEN datepart(dd,effdate) = '9' THEN expamt ELSE 0 END)
,[10th] = SUM(CASE WHEN datepart(dd,effdate) = '10' THEN expamt ELSE 0 END)
,[11th] = SUM(CASE WHEN datepart(dd,effdate) = '11' THEN expamt ELSE 0 END)
,[12th] = SUM(CASE WHEN datepart(dd,effdate) = '12' THEN expamt ELSE 0 END)
,[13th] = SUM(CASE WHEN datepart(dd,effdate) = '13' THEN expamt ELSE 0 END)
,[14th] = SUM(CASE WHEN datepart(dd,effdate) = '14' THEN expamt ELSE 0 END)
,[15th] = SUM(CASE WHEN datepart(dd,effdate) = '15' THEN expamt ELSE 0 END)
,[16th] = SUM(CASE WHEN datepart(dd,effdate) = '16' THEN expamt ELSE 0 END)
,[17th] = SUM(CASE WHEN datepart(dd,effdate) = '17' THEN expamt ELSE 0 END)
,[18th] = SUM(CASE WHEN datepart(dd,effdate) = '18' THEN expamt ELSE 0 END)
,[19th] = SUM(CASE WHEN datepart(dd,effdate) = '19' THEN expamt ELSE 0 END)
,[20th] = SUM(CASE WHEN datepart(dd,effdate) = '20' THEN expamt ELSE 0 END)
,[21st] = SUM(CASE WHEN datepart(dd,effdate) = '21' THEN expamt ELSE 0 END)
,[22nd] = SUM(CASE WHEN datepart(dd,effdate) = '22' THEN expamt ELSE 0 END)
,[23rd] = SUM(CASE WHEN datepart(dd,effdate) = '23' THEN expamt ELSE 0 END)
,[24th] = SUM(CASE WHEN datepart(dd,effdate) = '24' THEN expamt ELSE 0 END)
,[25th] = SUM(CASE WHEN datepart(dd,effdate) = '25' THEN expamt ELSE 0 END)
,[26th] = SUM(CASE WHEN datepart(dd,effdate) = '26' THEN expamt ELSE 0 END)
,[27th] = SUM(CASE WHEN datepart(dd,effdate) = '27' THEN expamt ELSE 0 END)
,[28th] = SUM(CASE WHEN datepart(dd,effdate) = '28' THEN expamt ELSE 0 END)
,[29th] = SUM(CASE WHEN datepart(dd,effdate) = '29' THEN expamt ELSE 0 END)
,[30th] = SUM(CASE WHEN datepart(dd,effdate) = '30' THEN expamt ELSE 0 END)
,[31st] = SUM(CASE WHEN datepart(dd,effdate) = '31' THEN expamt ELSE 0 END)
,[LineTotal] = SUM(expamt)
FROM freight_tracker_expense
GROUP BY entity,groupname,description,effdate
ORDER BY entity
September 24, 2021 at 5:32 pm
Not 100% sure what you need, but probably:
SELECT entity
,description
,[Total] = SUM(expamt)
FROM freight_tracker_expense
GROUP BY entity,description
ORDER BY entity
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 24, 2021 at 5:37 pm
Ya that gets me a total, but would like to see the running days like I had, but create a total record within the construct I already
have. So after all of and Enties daily total have a TOTAL for ENTITY ....
Thanks...
September 24, 2021 at 5:55 pm
OK. You said "instead of" so I figured you didn't want the other details in the new query.
Maybe try:
GROUP BY entity,groupname,description,effdate WITH ROLLUP
You can get rid of total lines you won't want to see using HAVING and GROUPING_ID, etc.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 24, 2021 at 6:07 pm
Ya that thru in some NULL records for totaling
Thanks.
Yeah, you can test for NULLs in columns and/or GROUPING_ID to determine: which rows are total rows and which rows are detail rows; to exclude total rows you don't want; and to sort the total rows the way you want to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 24, 2021 at 9:43 pm
Scott did you ever have a chance to re-write the cursor issue I put out there in this forum you where looking for the error table
schema at last post.
Thanks.
September 25, 2021 at 12:14 am
I got it to work....
Thanks for suggestions..
SELECT -- entity
--,description
CASE WHEN GROUPING(entity) = 1 THEN 'Day Total' ELSE ISNULL(entity, 'Unknown') END as entity
-- CASE WHEN GROUPING(description) = 1 THEN 'Total Description' ELSE ISNULL(description, 'Unknown') END as description
,[1st] = SUM(CASE WHEN datepart(dd,effdate) = '1' THEN expamt ELSE 0 END)
,[2nd] = SUM(CASE WHEN datepart(dd,effdate) = '2' THEN expamt ELSE 0 END)
,[3rd] = SUM(CASE WHEN datepart(dd,effdate) = '3' THEN expamt ELSE 0 END)
,[4th] = SUM(CASE WHEN datepart(dd,effdate) = '4' THEN expamt ELSE 0 END)
,[5th] = SUM(CASE WHEN datepart(dd,effdate) = '5' THEN expamt ELSE 0 END)
,[6th] = SUM(CASE WHEN datepart(dd,effdate) = '6' THEN expamt ELSE 0 END)
,[7th] = SUM(CASE WHEN datepart(dd,effdate) = '7' THEN expamt ELSE 0 END)
,[8th] = SUM(CASE WHEN datepart(dd,effdate) = '8' THEN expamt ELSE 0 END)
,[9th] = SUM(CASE WHEN datepart(dd,effdate) = '9' THEN expamt ELSE 0 END)
,[10th] = SUM(CASE WHEN datepart(dd,effdate) = '10' THEN expamt ELSE 0 END)
,[11th] = SUM(CASE WHEN datepart(dd,effdate) = '11' THEN expamt ELSE 0 END)
,[12th] = SUM(CASE WHEN datepart(dd,effdate) = '12' THEN expamt ELSE 0 END)
,[13th] = SUM(CASE WHEN datepart(dd,effdate) = '13' THEN expamt ELSE 0 END)
,[14th] = SUM(CASE WHEN datepart(dd,effdate) = '14' THEN expamt ELSE 0 END)
,[15th] = SUM(CASE WHEN datepart(dd,effdate) = '15' THEN expamt ELSE 0 END)
,[16th] = SUM(CASE WHEN datepart(dd,effdate) = '16' THEN expamt ELSE 0 END)
,[17th] = SUM(CASE WHEN datepart(dd,effdate) = '17' THEN expamt ELSE 0 END)
,[18th] = SUM(CASE WHEN datepart(dd,effdate) = '18' THEN expamt ELSE 0 END)
,[19th] = SUM(CASE WHEN datepart(dd,effdate) = '19' THEN expamt ELSE 0 END)
,[20th] = SUM(CASE WHEN datepart(dd,effdate) = '20' THEN expamt ELSE 0 END)
,[21st] = SUM(CASE WHEN datepart(dd,effdate) = '21' THEN expamt ELSE 0 END)
,[22nd] = SUM(CASE WHEN datepart(dd,effdate) = '22' THEN expamt ELSE 0 END)
,[23rd] = SUM(CASE WHEN datepart(dd,effdate) = '23' THEN expamt ELSE 0 END)
,[24th] = SUM(CASE WHEN datepart(dd,effdate) = '24' THEN expamt ELSE 0 END)
,[25th] = SUM(CASE WHEN datepart(dd,effdate) = '25' THEN expamt ELSE 0 END)
,[26th] = SUM(CASE WHEN datepart(dd,effdate) = '26' THEN expamt ELSE 0 END)
,[27th] = SUM(CASE WHEN datepart(dd,effdate) = '27' THEN expamt ELSE 0 END)
,[28th] = SUM(CASE WHEN datepart(dd,effdate) = '28' THEN expamt ELSE 0 END)
,[29th] = SUM(CASE WHEN datepart(dd,effdate) = '29' THEN expamt ELSE 0 END)
,[30th] = SUM(CASE WHEN datepart(dd,effdate) = '30' THEN expamt ELSE 0 END)
,[31st] = SUM(CASE WHEN datepart(dd,effdate) = '31' THEN expamt ELSE 0 END)
,[Entity Total] = SUM(expamt)
FROM freight_tracker_expense
GROUP BY ROLLUP (entity)
--GROUP BY entity,groupname,description,effdate WITH ROLLUP
--GROUP BY entity,groupname,description,effdate
ORDER BY entity
September 25, 2021 at 12:19 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply