QUERY HELP minus from max allowed

Question

QUERY HELP minus from max allowed

ayh1

Mr or Mrs. 500

Points: 581
More actions
January 12, 2015 at 3:13 am

#314348

Hi
I have a query
SELECT COUNT(dbo.tblAppointments.PatientID) AS NumberOfPatientsCurrentlyEnrolled, dbo.tblAppointments.CourseID, dbo.tblLkup_Location.LocationDesc,
dbo.tblCourses.CourseDate
FROM dbo.tblCourses INNER JOIN
dbo.tblLkup_Location ON dbo.tblCourses.Location_ID = dbo.tblLkup_Location.LocationID INNER JOIN
dbo.tblAppointments ON dbo.tblCourses.Course_ID = dbo.tblAppointments.CourseID
GROUP BY dbo.tblAppointments.CourseID, dbo.tblLkup_Location.LocationDesc, dbo.tblCourses.CourseDate
this returns the number of patients on each course, I am allowed to have by default a max of 12 patients on a course how can I write a query to say 12 MINUS (the count)? or what would be alternatively an easier way
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Assuming that I understood what you want, Just add 12 – before the count key word:

SELECT 12 - COUNT(dbo.tblAppointments.PatientID) AS NumberOfPatientsCurrentlyEnrolled, dbo.tblAppointments.CourseID, dbo.tblLkup_Location.LocationDesc,

dbo.tblCourses.CourseDate

FROM dbo.tblCourses INNER JOIN

dbo.tblLkup_Location ON dbo.tblCourses.Location_ID = dbo.tblLkup_Location.LocationID INNER JOIN

dbo.tblAppointments ON dbo.tblCourses.Course_ID = dbo.tblAppointments.CourseID

GROUP BY dbo.tblAppointments.CourseID, dbo.tblLkup_Location.LocationDesc, dbo.tblCourses.CourseDate

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ayh1 Mr or Mrs. 500 Points: 581 More actions · Answer 2

ayh1

Mr or Mrs. 500

Points: 581

January 12, 2015 at 4:47 am

#1769445

thanks 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You might want to read about table aliases - they will make your code much easier to read:

SELECT

12-COUNT(a.PatientID) AS NumberOfPatientsCurrentlyEnrolled,

a.CourseID,

l.LocationDesc,

c.CourseDate

FROM dbo.tblCourses c

INNER JOIN dbo.tblLkup_Location l

ON c.Location_ID = l.LocationID

INNER JOIN dbo.tblAppointments a

ON c.Course_ID = a.CourseID

GROUP BY a.CourseID, l.LocationDesc, c.CourseDate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden