SQL problem with stored proc with common table expressions inside

SQL remove groupings from CTE in stored procedure--

Have to remove the groupings from a stored procedure, I keep getting a syntax error that I can't figure out.   I will post the original and the 2nd one as the one to be converted.

This line has the problem
SELECT s.[Service], s.MEPERSCode, (DATEDIFF(MINUTE,p.AnesStartTime,p.AnesEndTime)/sc.svcCount) * ac.stfCount AS MinutesRaw, 1.0 / sc.svcCount AS CasesRaw

sc.svcCount and  ac.stfCount has Invalid column nameSPConverted.txtSPOriginal.txt
JohnMac328Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
The first thing I notice is that the column names returned by the CTE's differ from the column names you are referencing below.  Change one or the other to use the same column names:

SELECT s.PatientID, COUNT(*) AS serviceCount

SELECT a.PatientID, COUNT(*) AS staffCount

SELECT s.[Service], s.MEPERSCode, (DATEDIFF(MINUTE,p.AnesStartTime,p.AnesEndTime)/sc.svcCount) * ac.stfCount AS MinutesRaw, 1.0 / sc.svcCount AS CasesRaw


»bp
JohnMac328Author Commented:
The one you are referring to is the one that works correctly, the converted file is the one that needs to be corrected.  If there are two dashes in front of a statement, it has been commented out.
Bill PrewIT / Software Engineering ConsultantCommented:
Ah, and I didn't notice the columns being specified on the CTE definition.  Okay, try this (can't really test here without the schema), changed the CTE definition line to name the count column the way you are using it below.

Also, I think you still need the last line GROUP BY since you are doing aggregation functions in that query.

IF (@fromsearch = 0)
	BEGIN
		-- Clear existing rows for this user
		DELETE FROM UserSearchResultRows
		WHERE SearchNumber = @searchNumber
			AND SecurityID = @securityID;

		-- Add new rows for this user and search
		INSERT INTO UserSearchResultRows (SecurityID, PatientID, SearchNumber)
		SELECT  @securityID, PatientID, @searchNumber
		FROM viewPatient_Room
		WHERE LUHospitalID = @LUhospitalID
			AND FlagCaseStage = 4
			AND DateSurgery BETWEEN @sDate AND @eDate;
	END;

-- Process report data
WITH svcCount_cte (svcCount )
AS 
(
	SELECT COUNT(s.PatientID) AS svcCount 
	FROM Service s 
		INNER JOIN [usersearchresultrows] usrr ON usrr.PatientID = s.PatientID 
	WHERE usrr.SecurityID = @securityID
		AND usrr.SearchNumber = @searchNumber

	--GROUP BY s.PatientID
),
staffCount_cte (stfCount)
AS 
(	
	SELECT COUNT(a.PatientID) AS staffCount 
	FROM AnesStaff a 
		INNER JOIN [usersearchresultrows] usrr ON usrr.PatientID = a.PatientID 
	WHERE usrr.SecurityID = @securityID
		AND usrr.SearchNumber = @searchNumber
		AND a.FlagRelief = 0
	--GROUP BY a.PatientID
),
svcList_cte ([Service], FlagPrimary, MEPERSCode, IsInpatient)
AS
( 
	SELECT [Service], FlagPrimary, MEPERSCode_InPatient AS MEPERSCode, 1 AS IsInpatient FROM LUService
	UNION
	SELECT [Service], FlagPrimary, MEPERSCode_OutPatient AS MEPERSCode, 0 AS IsInpatient FROM LUService
),
anesMinutesRaw_cte ([Service], MEPERSCode, MinutesRaw, CasesRaw)
AS
(
	SELECT s.[Service], s.MEPERSCode, (DATEDIFF(MINUTE,p.AnesStartTime,p.AnesEndTime)/sc.svcCount) * ac.stfCount AS MinutesRaw, 1.0 / sc.svcCount AS CasesRaw
	FROM Patient p
		INNER JOIN [usersearchresultrows] usrr ON usrr.PatientID = p.PatientID
		INNER JOIN [Service] s ON s.PatientID = p.PatientID
		INNER JOIN [svcCount_cte] sc ON sc.PatientID = p.PatientID   
		INNER JOIN [staffCount_cte] ac ON ac.PatientID = p.PatientID
	WHERE usrr.SecurityID = @securityID
		AND usrr.SearchNumber = @searchNumber
		AND p.AnesStartTime IS NOT NULL
		AND p.AnesEndTime IS NOT NULL
		AND DATEDIFF(MINUTE,p.AnesStartTime,p.AnesEndTime) >= 1
)	

-- Return results to application
SELECT a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient, SUM(ISNULL(MinutesRaw,0)) AS [Minutes], FLOOR(SUM(ISNULL(CasesRaw,0)) + 0.5) AS [Cases]
FROM svcList_cte a
	LEFT OUTER JOIN anesMinutesRaw_cte b ON b.[Service] = a.[Service] AND b.MEPERSCode = a.MEPERSCode
    GROUP BY a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient;

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

JohnMac328Author Commented:
I made some changes that did clear up the previous errors, here is the new one - I did not anticipate it being this difficult to display the individual records, group by is what usually gives me problems :)

Column 'svcList_cte.Service' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. line 58
 
IF (@fromsearch = 0)
	BEGIN
		-- Clear existing rows for this user
		DELETE FROM UserSearchResultRows
		WHERE SearchNumber = @searchNumber
			AND SecurityID = @securityID;

		-- Add new rows for this user and search
		INSERT INTO UserSearchResultRows (SecurityID, PatientID, SearchNumber)
		SELECT  @securityID, PatientID, @searchNumber
		FROM viewPatient_Room
		WHERE LUHospitalID = @LUhospitalID
			AND FlagCaseStage = 4
			AND DateSurgery BETWEEN @sDate AND @eDate;
	END;

-- Process report data
WITH svcCount_cte (svcCount)
AS 
(
	SELECT COUNT(s.PatientID)AS svcCount 
	FROM Service s 
		INNER JOIN [usersearchresultrows] usrr ON usrr.PatientID = s.PatientID 
	WHERE usrr.SecurityID = @securityID
		AND usrr.SearchNumber = @searchNumber

	--GROUP BY s.PatientID
),
staffCount_cte (staffCount)
AS 
(	
	SELECT COUNT(a.PatientID)AS staffCount 
	FROM AnesStaff a 
		INNER JOIN [usersearchresultrows] usrr ON usrr.PatientID = a.PatientID 
	WHERE usrr.SecurityID = @securityID
		AND usrr.SearchNumber = @searchNumber
		AND a.FlagRelief = 0
	--GROUP BY a.PatientID
),
svcList_cte ([Service], FlagPrimary, MEPERSCode, IsInpatient)
AS
( 
	SELECT [Service], FlagPrimary, MEPERSCode_InPatient AS MEPERSCode, 1 AS IsInpatient FROM LUService
	UNION
	SELECT [Service], FlagPrimary, MEPERSCode_OutPatient AS MEPERSCode, 0 AS IsInpatient FROM LUService
),
anesMinutesRaw_cte ([Service], MEPERSCode, MinutesRaw, CasesRaw)
AS
(
	SELECT s.[Service], s.MEPERSCode, (DATEDIFF(MINUTE,p.AnesStartTime,p.AnesEndTime)/sc.svcCount) * ac.staffCount AS MinutesRaw, 1.0 / sc.svcCount AS CasesRaw
	FROM Patient p
		INNER JOIN [usersearchresultrows] usrr ON usrr.PatientID = p.PatientID
		INNER JOIN [Service] s ON s.PatientID = p.PatientID
		INNER JOIN [svcCount_cte] sc ON sc.svcCount = p.PatientID   
		INNER JOIN [staffCount_cte] ac ON ac.staffCount = p.PatientID
	WHERE usrr.SecurityID = @securityID
		AND usrr.SearchNumber = @searchNumber
		AND p.AnesStartTime IS NOT NULL
		AND p.AnesEndTime IS NOT NULL
		AND DATEDIFF(MINUTE,p.AnesStartTime,p.AnesEndTime) >= 1
)	

-- Return results to application
SELECT a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient, SUM(ISNULL(MinutesRaw,0)) AS [Minutes], FLOOR(SUM(ISNULL(CasesRaw,0)) + 0.5) AS [Cases]
FROM svcList_cte a
	LEFT OUTER JOIN anesMinutesRaw_cte b ON b.[Service] = a.[Service] AND b.MEPERSCode = a.MEPERSCode; 
--GROUP BY a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient;

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
I think as I mentioned, you need to uncomment out line 68:

--GROUP BY a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient;


»bp
JohnMac328Author Commented:
I see but I will have to take another look since this report has to have the details.
Bill PrewIT / Software Engineering ConsultantCommented:
Understand.  Just keep in mind, as written:

SELECT a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient, SUM(ISNULL(MinutesRaw,0)) AS [Minutes], FLOOR(SUM(ISNULL(CasesRaw,0)) + 0.5) AS [Cases]

would require grouping of the columns not in the aggregation function (SUM)

GROUP BY a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient;


»bp
JohnMac328Author Commented:
Good point, I ran this (took out SUM)

SELECT a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient, MinutesRaw AS [Minutes], FLOOR((ISNULL(CasesRaw,0)) + 0.5) AS [Cases]
FROM svcList_cte a
      LEFT OUTER JOIN anesMinutesRaw_cte b ON b.[Service] = a.[Service] AND b.MEPERSCode = a.MEPERSCode
GROUP BY a.[Service], a.FlagPrimary, a.MEPERSCode, a.IsInPatient;

and it is still giving me the same record set as the original sp with the grouping.  You answered my question so I will close it.  I appreciate your help and have a good weekend.
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad to be of some help.


»bp
JohnMac328Author Commented:
He showed me how to use the correct syntax with a SQL cte with only one parameter.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.