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

General SQL Server Forums

New to SQL Server Programming

Zero Values

Author

Topic

garcost48
Starting Member

2 Posts

Posted - 2015-03-27 : 18:08:36

Doing a basic Select query off our company database where I receive the following columns:

Area, Office, Branch, and summary for each day of the month.

EAST .. A9805.. 7720. 150.00 .. 100.00 etc...

The summary provides a list of all the offices that had activity from what I'm requestiong in the Where area.

My boss would like to see the list display "all' our offices even if they don't have any sales.

I've played around with the Sum(Case feature but no luck.

SUM(CASE WHEN StationCode = '0703' and DATEPART(DD,OCCURRENCEDATE) = '01' THEN Sales ELSE 0 END)as '01',

Any suggestions?

Thanks.

GC

GCostigan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 18:10:25

Show us the query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2015-03-28 : 19:22:29

It sounds like you need an outer join in your query.

garcost48
Starting Member

2 Posts

Posted - 2015-03-30 : 16:21:03

This is what I'm using that provides a summary roll up of the offices that have the data I'm looking for.

Select
CASE
When StationsReportingToRegion IN ('HUB') Then '01:HUBS'
When StationsReportingToRegion IN ('GATEWAY') Then '02:GATEWAY'
When StationsReportingToRegion IN ('NORTHEAST') Then '03:NORTHEAST'
When StationsReportingToRegion IN ('SOUTHEAST') Then '04:SOUTHEAST'
When StationsReportingToRegion IN ('CENTRAL') Then '05:CENTRAL'
When StationsReportingToRegion IN ('WEST') Then '07:WEST'
When StationsReportingToRegion IN ('International') Then '08:Intl'
ELSE 'X - Missing Category'
END AS 'Area',
Profit_Centers_Table.OfficeCode AS 'Off',
CorpUsageDataMaster.BranchCode as 'Bra',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '01' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '01',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '02' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '02',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '03' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '03',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '04' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '04',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '05' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '05',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '06' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '06',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '07' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '07',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '08' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '08',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '09' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '09',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '10' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '10',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '11' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '11',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '12' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '12',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '13' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '13',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '14' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '14',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '15' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '15',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '16' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '16',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '17' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '17',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '18' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '18',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '19' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '19',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '20' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '20',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '21' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '21',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '22' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '22',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '23' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '23',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '24' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '24',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '25' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '25',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '26' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '26',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '27' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '27',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '28' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '28',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '29' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '29',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '30' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '30',
SUM(CASE WHEN DATEPART(DD,CorpUsageDataMaster.OCCURRENCEDATE) = '31' THEN CorpUsageDataMaster.Hours ELSE 0 END)as '31'
From CorpUsageDataMaster
JOIN Branches_Table ON CorpUsageDataMaster.BranchCode = Branches_Table.BranchCode
JOIN Profit_Centers_Table ON CorpUsageDataMaster.OffCode = Profit_Centers_Table.OffCode
and CorpUsageDataMaster.CompanyCode = Profit_Centers_Table.CompanyCode
JOIN SalesCodes_Table ON CorpUsageDataMaster.SalesCode = SalesCodes_Table.SalesCode
LEFT JOIN HrlySalary ON CorpUsageDataMaster.EMPID = HrlySalary.EmpID
Where CorpUsageDataMaster.SalesCode IN ('BPO','CI','CSH','DHH','DHO','DW','EC','ECH','FTO','HCO','HEH','NHO','NM',
'NMC','NMH','NPH','MDO','OT','OTE','OTF','OTG','OTH','OTP','OTV','PH','PTG','RE','ST','STH','TCO','TDO','TO','TTO',
'ZZ')
AND StationsReportingToRSVC IN ('HUB','GATEWAY','NORTHEAST','SOUTHEAST','CENTRAL','WEST','International')
AND Branches_Table.BranchCategorySmall = 'RSVC'
AND ((HrlySalary.PresSubarea IN ('302') and HrlySalary.PSAlpha IS NULL)
OR (HrlySalary.PresSubarea = '320' and HrlySalary.PSAlpha = 'A'))
AND DATEPART(YYYY,CorpUsageDataMaster.OCCURRENCEDATE) = DATEPART(YYYY,GETDATE())
AND DATEPART(MM,CorpUsageDataMaster.OCCURRENCEDATE) = DATEPART(MM,GETDATE())
Group By Profit_Centers_Table.STATIONSREPORTINGTOREGION,
Profit_Centers_Table.AIRPORTCODE,
CorpUsageDataMaster.BranchCode

GCostigan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources