Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:35 PM
Points: 15, Visits: 71
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
Post #1522356
Posted Thursday, December 12, 2013 9:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 12,917, Visits: 32,084
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1522364
Posted Thursday, December 12, 2013 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 5,369, Visits: 9,910
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
Post #1522365
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse