SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


display columns with no data


display columns with no data

Author
Message
boehnc
boehnc
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 102
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
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73243 Visits: 40960
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35195 Visits: 16671
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search