|
Gary Costigan
Yak Posting Veteran
95 Posts |
 Posted - 2005-02-11 : 14:56:00
|
| In a Pivot Table style query I get the results I need across the X / Y axis.I request a list of company stations and pay code groups associated with the station dropping down in rows and the hours broken out by each month of the year as columns.In some cases there may not be hours associated with a paycode group at a station and the pay code group is not displayed.Is there a way of building the query so that even if there are "zero" hours for the entire year associated with a pay code group, the query will still display the pay code group for the station and show zeros (.0) as a value in the month columns? There are a total of five groups: SK SKF FML SKU OTHERHere is the code I currently use.SelectAEStation as 'AE STATION',LOSTTIMECATEGORY as 'AE Losttime 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 losttime.Profit_Centers_Table.AirportCode as 'AEStation',losttime.Branches_Table.BranchCategorysmall as 'AEGroup',losttime.Branches_Table.BranchCode as 'AEBranch',losttime.PayCodes_Table.LostTimeReport as 'LostTimeCategory', 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 losttime.Total_Hours_dataJOIN losttime.Profit_Centers_Table ON losttime.Total_Hours_data.StationCode = losttime.Profit_Centers_Table.StationCode and losttime.Total_Hours_data.CompanyCode = losttime.Profit_Centers_Table.CompanyCodeJoin losttime.Branches_Table ON losttime.Total_Hours_data.BranchCode = losttime.Branches_Table.BranchCodeJoin losttime.PayCodes_Table ON losttime.Total_Hours_data.PayCode = losttime.PayCodes_Table.PayCode WHERE losttime.Profit_Centers_Table.COMPANYCODE NOT IN ('AA', 'TW')and losttime.Branches_Table.BranchCategorysmall = 'AE Field Service'and losttime.PayCodes_Table.LostTimeReport IN ('SK', 'SKF', 'SKU', 'FML', 'OTHER')and DATEPART (YEAR,losttime.Total_Hours_data.ReportingPeriodDate) = '2004'GROUP BY losttime.Profit_Centers_Table.AirportCode,losttime.Branches_Table.BranchCategorysmall,losttime.Branches_Table.BranchCode,losttime.PayCodes_Table.LostTimeReport,losttime.Total_Hours_data.hours,losttime.Total_Hours_data.ReportingPeriodDate) XGROUP BY AESTATION,LOSTTIMECATEGORY ORDER BY AESTATION,LOSTTIMECATEGORY Here are the current results. STATION Pay GROUP JAN FEB MAR APR MAY EX-FT MYERS FL/RSW OTHER 16.8 21.6 78.9 80.0 35.EX-FT MYERS FL/RSW SK 222.4 145.0 63.0 226.6 23 EX-FT MYERS FL/RSW SKU 105.5 46.5 13.5 .0 14EX-GENERAL OFFICE MI FML 14.0 19.7 38.9 98.0 55.EX-GENERAL OFFICE MI OTHER 427.7 309.7 70.1 167.0 430 EX-GENERAL OFFICE MI SK 1358.6 1281.1 1369.2 1052.2 114EX-GENERAL OFFICE MI SKF .0 6.0 .0 18.4 10. EX-GENERAL OFFICE MI SKU 34.8 119.0 434.0 51.5 201.7 I would like to see FML and SKF appear for the first station with .0 across the columns.As always thanks in advance.GC |
|
Pivot Table question