SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CTE ResultSet counts are off
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NickG21
Starting Member

USA
6 Posts

Posted - 01/18/2013 :  12:49:59  Show Profile  Reply with Quote
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
Yak Posting Veteran

72 Posts

Posted - 01/18/2013 :  13:16:15  Show Profile  Reply with Quote
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)
Go to Top of Page

NickG21
Starting Member

USA
6 Posts

Posted - 01/18/2013 :  13:55:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/18/2013 :  14:11:20  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 3.55 seconds. Powered By: Snitz Forums 2000