I have the following query that returns a set of counts by weeks beginning with Monday, with a row for the aggregate totals.
select distinct DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) as B2B_Week, count (distinct bm.MISSION_ID) as B2B_Mission, count (distinct dl.DRIVER) as Distinct_B2B_Drivers from DD.B2B_MISSIONS bm inner join dd.IN_GATE ig on bm.MISSION_ID = ig.IN_MISSION_ID inner join dd.XNS_DRIVER_LOGIN dl on ig.IN_DRIVER_ID = dl.driver group by DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) with rollup order by DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) ;
My Results Table looks like this:
B2B_Week B2B_Mission Distinct_B2B_Drivers 1647 357 44 717 264 45 930 301
I want it to look like this:
B2B_Week B2B_Mission Distinct_B2B_Drivers 44 717 264 45 930 301 TOTAL 1647 357
The totals row appears at the top rather than the bottom. This is probably related to the second problem.
I have been unable to use a CASE statement to return 'TOTAL' with the date column is NULL. The primary problem is that the DATE is an INT and 'TOTAL' is not. I have tried various methods of using CAST on either the entire CASE statement or just pieces of it. I've tried putting the Case statement in the SELECT or in the GROUP BY statement.
Here is an example of what happens when I try to get TOTAL to appear.
case when DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) is null then 'TOTAL' else DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) end
"Conversion failed when converting the varchar value 'TOTAL' to data type int."
What am I doing wrong?