CTE ResultSet counts are off - 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

SQL Server 2008 Forums

Transact-SQL (2008)

CTE ResultSet counts are off

Author

Topic

NickG21
Starting Member

6 Posts

Posted - 2013-01-18 : 12:49:59

Hey everyone,
I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables. Any help would be great;

With CTESource(Total, ID, Source, Program) AS
(
	SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
	Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
	Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
	left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
	WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
	(Inquiry.DecisionMade = 'Decision to admit') AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
	Group By Source, Program, Referral.InquiryID_fk
)
, CTEAdmission(Total, ID, Source, Program) AS
(
	SELECT count(DISTINCT Referral.InquiryID_fk) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
	Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
	Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
	left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
	WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
	(Inquiry.DecisionMade = 'Decision to admit') AND (DecAdmit.ActualAdmitDate <= GetDate()) AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
	Group By Source, Program, Referral.InquiryID_fk
)
SELECT 
A.Program, A.Source , count(A.Total) as SourceCount, IsNull(B.Total,0) as Admissions from CTESource A
left outer join CTEAdmission B on A.ID = B.ID
Group By A.Program,A.Source,  B.Total

What I want to do is connect the totals by program so that in my ReportBuilder report I can show the 0 results. Something like this;


Source:             Program1            Program2           Program3
Source 1               5                   7                   0
Source 2               0                   4                   2
Source 3               1                   0                   3
Total Inquiries       10                   11                  8
Total Admission        7                   5                   0

The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.

Thank you all for taking the time to look into this, i hope it isn't too confusing.

mandm
Posting Yak Master

120 Posts

Posted - 2013-01-18 : 13:16:15

Try changing the Group By as shown below. It could be the cause.

With CTESource(Total, ID, Source, Program) AS
(
SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
(Inquiry.DecisionMade = 'Decision to admit') AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
Group By Source, Program, Referral.InquiryID_fk
)
, CTEAdmission(Total, ID, Source, Program) AS
(
SELECT count(DISTINCT Referral.InquiryID_fk) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
(Inquiry.DecisionMade = 'Decision to admit') AND (DecAdmit.ActualAdmitDate <= GetDate()) AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
Group By Source, Program, Referral.InquiryID_fk
)
SELECT
A.Program, A.Source , count(A.Total) as SourceCount, IsNull(B.Total,0) as Admissions from CTESource A
left outer join CTEAdmission B on A.ID = B.ID
Group By A.Program,A.Source, ISNULL(B.Total, 0)

NickG21
Starting Member

6 Posts

Posted - 2013-01-18 : 13:55:08

I apprecaite your quick response, but using the isnull() in the select statement already gives me the proper result set 0 counts, it is mainly the SourceCount that is off, and I am really just unsure what approach to take at all

If the CTESource is ran by itself it gives a count of 8 but ran in the select join a count of 10, thats just one example as this result is much larger than that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 14:11:20

why do you need those CTEs? i think you need just this


SELECT count(Inquiry.ID) as SourceCount,
count(DISTINCT CASE WHEN (DecAdmit.ActualAdmitDate <= GetDate()) THEN Referral.InquiryID_fk ELSE NULL END) as Admissions, 
Referral.InquiryID_fk, Source, Inquirer.Program from Referral
	Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
	Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
	left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
	WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
	(Inquiry.DecisionMade = 'Decision to admit') AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
	Group By Source, Program, Referral.InquiryID_fk

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources