Query help

  • 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

  • ...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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • thanks for the reply I have not used it before but will try it out

  • HAVE TRIED IT DIDNT WORK?

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

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