union help 1

[LloydDobler]

HI all, How would I create a column indicating the scrap type in the union statement below?

SELECT SUM(CASE datepart(mm, [prddate]) WHEN 1 THEN prdscrap ELSE 0 END) AS JANSCRAP, SUM(CASE datepart(mm, [prddate]) WHEN 2 THEN prdscrap ELSE 0 END) AS FEBSCRAP
FROM tbl_production
WHERE division = 1 AND datepart(yy, prddate) = datepart(yy, getdate())
GROUP BY DIVISION
UNION ALL
SELECT SUM(CASE datepart(mm, [prddate]) WHEN 1 THEN maintscrap ELSE 0 END) AS JANSCRAP, SUM(CASE datepart(mm, [prddate]) WHEN 2 THEN maintscrap ELSE 0 END) AS FEBSCRAP...

I'd like to add a column called 'Scrap Type' and the values in this case would be PRDSCRAP and MAINTSCRAP as shown in example below

SCRAPTYPE JANSCRAP FEBSCRAP
PRDSCRAP 200 150
MAINTSCRAP 50 47

Any help is much appreciated.


"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[hmckillop]

If there is only two rows returned, one for each part of union you can simply add in the fields.
e.g.

SELECT  'PRDSCRAP' As ScrapType,   SUM(CASE datepart(mm, [prddate]) WHEN 1 THEN prdscrap ELSE 0 END) AS JANSCRAP, SUM(CASE datepart(mm, [prddate]) WHEN 2 THEN prdscrap ELSE 0 END) AS FEBSCRAP
FROM         tbl_production
WHERE     division = 1 AND datepart(yy, prddate) = datepart(yy, getdate())
GROUP BY DIVISION
UNION ALL
SELECT  'MAINTSCRAP' As ScrapType   SUM(CASE datepart(mm, [prddate]) WHEN 1 THEN maintscrap ELSE 0 END) AS JANSCRAP, SUM(CASE datepart(mm, [prddate]) WHEN 2 THEN maintscrap ELSE 0 END) AS FEBSCRAP...

"I'm living so far beyond my income that we may almost be said to be living apart