query remove null rows

vendredi 24 avril 2015

I have a query

Code:

SELECT    TOP (100) PERCENT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, 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 LEFT OUTER JOIN
                      dbo.tblPatient AS p ON pl.PatientID = p.PatientID LEFT OUTER JOIN
                      dbo.tblAppointments AS a ON p.PatientID = a.PatientID ON gp.GPPracticeID = pl.GPPracticeID LEFT OUTER JOIN
                      dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
WHERE    (a.AttendanceStatus IN (1, 2, 3, 4, 6, 7, 8, 9, 10)) OR
                      (a.AttendanceStatus IS NULL)
GROUP BY gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, gp.CCGRegion, YEAR(dbo.tblCourses.CourseDate), CONVERT(char(3), dbo.tblCourses.CourseDate, 0)
ORDER BY gp.OrganisationCode

what i want is to remove rows where if attended did not attend and cancelled all have values >1 remove rows where the month a year is null

sample output :

May 2015 C82001 KIBWORTH HEALTH CENTRE KIBWORTH HEALTH CENTRE SMEETON ROAD 0 0 0 East CCG
NULL NULL C82002 COUNTESTHORPE HEALTH CENTRE COUNTESTHORPE HEALTH CTR.CENTRAL STREET 0 0 0 City CCG
Apr 2015 C82002 COUNTESTHORPE HEALTH CENTRE COUNTESTHORPE HEALTH CTR.CENTRAL STREET 1 0 1 City CCG
Jun 2015 C82002 COUNTESTHORPE HEALTH CENTRE COUNTESTHORPE HEALTH CTR.CENTRAL STREET 0 0 0 City CCG
NULL NULL C82003 GREENGATE MEDICAL CENTRE GREENGATE MEDICAL CENTRE1 GREENGATE LANE 0 0 0W est CCG
NULL NULL C82005 GROBY ROAD MEDICAL CENTRE (ID PATCHETT)9 GROBY ROAD NULL 0 0 0 City CCG
Apr 2015 C82005 GROBY ROAD MEDICAL CENTRE (ID PATCHETT)9 GROBY ROAD NULL 0 1 0 City CCG


taking the above output as an example as countesthorpe has two rows that have attended, did not attend and cancelled values i want to remove the first row that has 000 values
query remove null rows

0 commentaires:

Enregistrer un commentaire

Labels