Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

display columns with no data Expand / Collapse
Posted Thursday, December 12, 2013 8:55 AM


Group: General Forum Members
Last Login: Wednesday, August 31, 2016 1:10 PM
Points: 24, Visits: 94
Newbie..sorry, posted this somewhere else, but couldn't find it. Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey. Any help is appreciated. Thank you.

Table schematics



--define start and end limits
Declare @todate datetime, @fromdate datetime
Select @fromdate='2013-01-01', @todate='2013-02-01'

;With cte as
Select @fromdate as Date
union all
Select dateadd(day, 1, Date)
from cte da
where Date < @todate

select di.DivisionRegion,
month( as month,
year(d.Date) as Year,
sum(p.PSIOutcome) as PSIOutcome,
isnull(SUM(p.PSIExpected),0) as Expected,
isnull(((sum([PSIOutcome]))/(sum ([psiexpected]))),0) as PSIIndex

from cte d
left outer join quality.dbo.Fact_PatientSafetyIndicators p on cast(p.DischargeDateTime as date) = d.Date
left outer join LukesReporting.UserView.vwAccount a on a.OriginAccountId = p.VisitID
left outer join Quality.dbo.Dim_PatientSafetyIndicators ps on ps.PSIKey = p.PSIKey
left outer join LukesReporting.UserView.vwDivisionCode di on di.DivisionCode = a.DivisionCode

where p.DivisionCode in ('$','1','2','{','#','L','6','t','[')
and p.PSIKey in ('1','2','3','4' ,'5','6','7','8','12','13','14','15','16','17','18','19','20','21')

group by di.divisionregion,month(,year(,p.PSIKey,ps.PSIName

order by di.divisionregion,year(,month(,p.PSIKey,ps.PSIName
Post #1522356
Posted Thursday, December 12, 2013 9:10 AM



Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 14,540, Visits: 38,379
you have to change your query to query first from a Calendar table of some sort, which contains all possible dates.
then your CTE left joins to that Calendar table.

with that in place, you'll be able to get zero totals for dates that had no data form the other tables.

FROM CalendarTable
ON CalendarTable.TheDate = d.Date

there's quite a few example of it here on ssc:


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1522364
Posted Thursday, December 12, 2013 9:10 AM



Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 7,030, Visits: 14,716
where p.DivisionCode in ('$','1','2','{','#','L','6','t','[')
and p.PSIKey in ('1','2','3','4' ,'5','6','7','8','12','13','14','15','16','17','18','19','20','21')
... is turning your outer join into an inner join. Try putting those two conditions in the join predicate instead.

Post #1522365
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse