display columns with no data

Question

Post reply

display columns with no data

boehnc

Ten Centuries

Points: 1001
More actions
December 12, 2013 at 8:55 am

#301367

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
di.DivisionRegion,int
P.PSIKey,int
P.PSIOutcome,int
P.PSIExpected,int
-------------------------------------------------------------------------
--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(d.date) as month,
year(d.Date) as Year,
p.PSIKey,ps.PSIname,
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(d.date),year(d.date),p.PSIKey,ps.PSIName
order by di.divisionregion,year(d.date),month(d.date),p.PSIKey,ps.PSIName

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

LEFT OUTER JOIN cte d

ON CalendarTable.TheDate = d.Date

...

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

http://www.sqlservercentral.com/search/?q=calendar+table

Lowell

--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!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

This...

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.

John