March 5, 2015 at 5:04 am
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
SWL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion, dbo.tblCourses.CourseDate
FROM dbo.tblGP_Practices gp INNER JOIN
dbo.tblGP_PatientLink pl ON gp.GPPracticeID = pl.GPPracticeID INNER JOIN
dbo.tblPatient p ON pl.PatientID = p.PatientID INNER JOIN
dbo.tblAppointments a ON p.PatientID = a.PatientID INNER JOIN
dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
WHERE (a.AttendanceStatus IN (1, 2, 3, 4, 6, 7, 9))
GROUP BY gp.CCGRegion, dbo.tblCourses.CourseDate, YEAR(dbo.tblCourses.CourseDate), CONVERT(char(3), dbo.tblCourses.CourseDate, 0)
March 5, 2015 at 7:05 am
It is hard to say for sure because we don't have the tables and data to work with but you are grouping by dbo.tblCourses.CourseDate which is not returned in the query. Remove that from the group by.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2015 at 7:07 am
I get an SQL Execution error I just got rid of returning the course date aswell
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply