January 1, 2011 at 2:46 pm
I have the following query...
ALTER PROCEDURE [dbo].[usp_GetClassScheduleForProgramNClass]
@ProgramID int
AS
BEGIN
SET NOCOUNT ON;
SELECT distinct DATENAME(Weekday, dateadd(day, d.ClassDay, 0) -1) as ClassDay
,StartTime, EndTime, CounselorName
, (select COUNT(*) from Cases where ClassID = d.ClassID) as Enrolled
FROM [DUIClasses] d
inner join DUIClassInstances dci on d.ClassID = dci.ClassID
inner join Counselors c on d.CounselorID = c.CounselorID
Where dci.ProgramID = @ProgramID
END
I only it to return rows where the COUNT(*) value is less than 17. Thought?
Thanks
JD
January 1, 2011 at 2:53 pm
JD,
With reference to your other recent thread you really seem to love the DISTINCT key word, do you? 😉
Due to the names used in that sproc let me ask: is that some kind of a homework or a school project?
Regarding the issue itself: als always, please provide ready to use table def and sample data together with your expected result.
January 1, 2011 at 3:14 pm
No this is a scheduling system. Don't really have any love for DISTINCT. I am a newbie T-SQL programmer who picked it up on my own.
January 2, 2011 at 10:36 pm
This is one way: (has to be 2005 and above), and remember that there are other ways too.
ALTER PROCEDURE [dbo].[usp_GetClassScheduleForProgramNClass]
@ProgramID int
AS
BEGIN
SET NOCOUNT ON;
WITH CTE
AS (
SELECT distinct DATENAME(Weekday, dateadd(day, d.ClassDay, 0) -1) as ClassDay
,StartTime, EndTime, CounselorName
, (select COUNT(*) from Cases where ClassID = d.ClassID) as Enrolled
FROM [DUIClasses] d
inner join DUIClassInstances dci on d.ClassID = dci.ClassID
inner join Counselors c on d.CounselorID = c.CounselorID
Where dci.ProgramID = @ProgramID
)
SELECT ClassDay,
StartTime,
EndTime,
CounselorName,
Enrolled
FROM CTE
WHERE Enrolled < 17
END
January 3, 2011 at 2:57 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply