April 17, 2015 at 8:06 am
I have a query:
SELECT TOP (100) PERCENT gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [Did not attend], SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled,
gp.CCGRegion
FROM dbo.tblGP_Practices AS gp LEFT OUTER JOIN
dbo.tblGP_PatientLink AS pl ON gp.GPPracticeID = pl.GPPracticeID LEFT OUTER JOIN
dbo.tblPatient AS p ON pl.PatientID = p.PatientID LEFT OUTER JOIN
dbo.tblAppointments AS a ON p.PatientID = a.PatientID LEFT OUTER JOIN
dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
GROUP BY gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, gp.CCGRegion
HAVING (gp.CCGRegion = N'city ccg')
ORDER BY gp.OrganisationCode
what I want to do is add course date from a courses table linked to the appointment table for example :
SELECT TOP (100) PERCENT gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [Did not attend], SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled,
gp.CCGRegion, dbo.tblCourses.CourseDate
FROM dbo.tblGP_Practices AS gp LEFT OUTER JOIN
dbo.tblGP_PatientLink AS pl ON gp.GPPracticeID = pl.GPPracticeID LEFT OUTER JOIN
dbo.tblPatient AS p ON pl.PatientID = p.PatientID LEFT OUTER JOIN
dbo.tblAppointments AS a ON p.PatientID = a.PatientID LEFT OUTER JOIN
dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
GROUP BY gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, gp.CCGRegion, dbo.tblCourses.CourseDate
HAVING (gp.CCGRegion = N'city ccg')
ORDER BY gp.OrganisationCode
When i run the second each gp practice is returned multiple times as a row is returned for each time there is a coursedate associated with a patient at that practice how can I merge the rows to one:
Example output
C82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET000City CCGNULL
C82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET000City CCG2015-04-20 00:00:00.000
C82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET001City CCG2015-04-22 00:00:00.000
C82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET000City CCG2015-04-28 00:00:00.000
C82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET000City CCG2015-06-08 00:00:00.000
C82005GROBY ROAD MEDICAL CENTRE (ID PATCHETT)9 GROBY ROADNULL000NULL
C82005GROBY ROAD MEDICAL CENTRE (ID PATCHETT)9 GROBY ROADNULL0102015-04-02 00:00:00.000
April 20, 2015 at 4:46 am
...what I want to do is add course date from a courses table linked to the appointment
...
... each gp practice is returned multiple times as a row is returned for each time there is a coursedate associated with a patient at that practice how can I merge the rows to one:
You need to deside which Course date to return.
If you group by it you get row per each one.
Use aggregate function: MIN or MAX
April 20, 2015 at 4:48 am
thanks for the reply I have not used it before but will try it out
April 20, 2015 at 8:33 am
HAVE TRIED IT DIDNT WORK?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply