Adding a row of Zero Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding a row of Zero Values

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-08-04 : 15:04:51

Greetings to all,

I am building a query in a Pivot Table style format and am having a small problem. I need to track 5 categories in 2005. My problem is that some stations have not coded any Hour values to the category, and the categories need to appear in each report or it throws everything off.

Here is my Query:

SELECT CATEGORY as 'CATEGORY',
SUM([JAN]) as 'JAN',
SUM([FEB]) as 'FEB',
SUM([MAR]) as 'MAR',
SUM([APR]) as 'APR',
SUM([MAY]) as 'MAY',
SUM([JUN]) as 'JUN',
SUM([JUL]) as 'JUL',
SUM([AUG]) as 'AUG',
SUM([SEP]) as 'SEP',
SUM([OCT]) as 'OCT',
SUM([NOV]) as 'NOV',
SUM([DEC]) as 'DEC'
FROM(
SELECT
CASE
WHEN PayCodes_Table.LostTimeReport = 'SK' THEN '1.SK'
WHEN PayCodes_Table.LostTimeReport = 'SKF' THEN '2.SKF'
WHEN PayCodes_Table.LostTimeReport = 'SKU' THEN '3.SKU'
WHEN PayCodes_Table.LostTimeReport = 'FML' THEN '4.FML'
WHEN PayCodes_Table.LostTimeReport = 'OTHER' THEN '5.OTHER'
ELSE 'Not Included'
END AS 'CATEGORY',

SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 01 THEN hours ELSE 0 END) as 'JAN',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 02 THEN hours ELSE 0 END) as 'FEB',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 03 THEN hours ELSE 0 END) as 'MAR',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 04 THEN hours ELSE 0 END) as 'APR',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 05 THEN hours ELSE 0 END) as 'MAY',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 06 THEN hours ELSE 0 END) as 'JUN',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 07 THEN hours ELSE 0 END) as 'JUL',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 08 THEN hours ELSE 0 END) as 'AUG',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 09 THEN hours ELSE 0 END) as 'SEP',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 10 THEN hours ELSE 0 END) as 'OCT',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 11 THEN hours ELSE 0 END) as 'NOV',
SUM (CASE DATEPART(Month,ReportingPeriodDate) WHEN 12 THEN hours ELSE 0 END) as 'DEC'
FROM Total_Hours_data
JOIN PayCodes_Table ON Total_Hours_data.PayCode = PayCodes_Table.PayCode
JOIN Branches_Table ON Total_Hours_data.BranchCode = Branches_Table.BranchCode
WHERE Total_Hours_data.StationCode = '0600'
AND Branches_Table.BranchCategorySmall = 'MNTC'
AND Branches_Table.MAINTENANCECATEGORIES = 'MNTC - QA'
AND PayCodes_Table.LostTimeReport IN ('SK', 'SKF', 'SKU', 'FML', 'OTHER')
AND (DATEPART (YY,Total_Hours_Data.ReportingPeriodDate) = DATEPART (YY,GETDATE()))
GROUP BY PayCodes_Table.LostTimeReport
)X
GROUP BY CATEGORY
ORDER BY CATEGORY ASC

Here is the result for the station I'm having a problem with:

1.SK 261.0 226.0 249.0 177.0 142.0 105.0 5.0 .0 .0 .0 .0 .0
3.SKU 37.0 24.0 21.0 13.0 8.0 5.0 5.0 .0 .0 .0 .0 .0
5.OTHER 16.0 50.0 10.0 .0 10.0 30.0 18.0 .0 .0 .0 .0 .0

My question is how do I get categories 2 and 4 to appear with a row of zeros? I've tried using a SUM(CASE inside the category area without any luck.

As always any help is greatly appreciated!

GC

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-04 : 16:24:21

you mean that Total_Hours_data.PayCode and/or Total_Hours_data.BranchCode is NULL for 2 and 4 categories ? Then of cause these rows will be elliminated from resultset.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-08-04 : 16:38:44

For the example station they have not coded any hours to the paycodes SKF and FML. I need the query to return a result for SKF and FML as '0'. This is what I'm hoping to get the result to look like:
Category Jan ... Feb ... Mar ... Apr ... May ... Jun ... Jul ... Aug ... Sep ... Oct ... Nov
1.SK .... 261.0.. 226.0.. 249.0.. 177.0.. 142.0.. 105.0... 5.0... .0... .0... .0... .0
2.SKF .0 .0 .0 .0 .0 .0 .0 .0 .0 .0 .0
3.SKU 37.0 24.0 21.0 13.0 8.0 5.0 5.0 .0 .0 .0 .0
4.FML .0 .0 .0 .0 .0 .0 .0 .0 .0 .0 .0
5.OTHER 16.0 50.0 10.0 .0 10.0 30.0 18.0 .0 .0 .0 .0

Hope this helps.

Thanks.

GC

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-04 : 17:27:14

If you post the table structures and some sample data\desired resultset you will get an answer much faster (and with higher accuracy

)

Some guidelines are here:

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources