Query Help

  • Hi,

    Hopefully someone can help me with my problem.

    I have the following query.

    SELECTintStudentId,

    txtTitle,

    txtForename,

    txtSurname,

    Gender,

    CASE WHEN dteDOB IS NULL

    THEN NULL

    WHEN Month(dteFromDate) > Month(dteDOB) OR (Month(dteFromDate) = Month(dteDOB) AND Day(dteFromDate) >= Day(dteDOB))

    THEN DateDiff(year, dteDOB, dteFromDate)

    ELSE DateDiff(year, dteDOB, dteFromDate) - 1

    END AS Age,

    dteDOB,

    Nationality,

    CASE WHEN txtName = 'Group Leader' THEN 'Yes' ELSE 'No' End as 'GroupLeader',

    CourseCode,

    dteFromDate,

    dteToDate,

    intNumberOfUnits,

    Agent,

    Returner,

    AccomNotes,

    AdditionalNotes,

    AcademicNotes,

    MAX(CASE WHEN txtname='Allergies' THEN txtcontent END) AS [AllergiesContent],

    MAX(CASE WHEN txtname='Allergies' THEN txtnote END) AS [AllergiesNote],

    MAX(CASE WHEN txtname='Any other information' THEN txtcontent END) AS [Any other information Content],

    MAX(CASE WHEN txtname='Any other information' THEN txtnote END) AS [Any other information Notes],

    MAX(CASE WHEN txtname='Asthma or cardiac condition' THEN txtcontent END) AS [Asthma or cardiac condition Content],

    MAX(CASE WHEN txtname='Asthma or cardiac condition' THEN txtnote END) AS [Asthma or cardiac condition Notes],

    MAX(CASE WHEN txtname='Boating/Watersports' THEN txtcontent END) AS [Boating/Watersports Content],

    MAX(CASE WHEN txtname='Boating/Watersports' THEN txtnote END) AS [Boating/Watersports Notes],

    MAX(CASE WHEN txtname='Bringing medicines' THEN txtcontent END) AS [Bringing medicines Content],

    MAX(CASE WHEN txtname='Bringing medicines' THEN txtnote END) AS [Bringing medicines Notes],

    MAX(CASE WHEN txtname='Food cannot eat' THEN txtcontent END) AS [Food cannot eat Content],

    MAX(CASE WHEN txtname='Food cannot eat' THEN txtnote END) AS [Food cannot eat Notes],

    MAX(CASE WHEN txtname='Origin' THEN txtcontent END) AS [Origin Content],

    MAX(CASE WHEN txtname='Origin' THEN txtnote END) AS [Origin Notes],

    MAX(CASE WHEN txtname='Painkillers if needed' THEN txtcontent END) AS [Painkillers if needed Content],

    MAX(CASE WHEN txtname='Painkillers if needed' THEN txtnote END) AS [Painkillers if needed Notes],

    MAX(CASE WHEN txtname='Photo Permission' THEN txtcontent END) AS [Photo Permission Content],

    MAX(CASE WHEN txtname='Photo Permission' THEN txtnote END) AS [Photo Permission Notes],

    MAX(CASE WHEN txtname='Physical difficulties' THEN txtcontent END) AS [Physical difficulties Content],

    MAX(CASE WHEN txtname='Physical difficulties' THEN txtnote END) AS [Physical difficulties Notes],

    MAX(CASE WHEN txtname='Pre-booked horse-riding/golf' THEN txtcontent END) AS [Pre-booked horse-riding/golf Content],

    MAX(CASE WHEN txtname='Pre-booked horse-riding/golf' THEN txtnote END) AS [Pre-booked horse-riding/golf Notes],

    MAX(CASE WHEN txtname='Reg form signed' THEN txtcontent END) AS [Reg form signed Content],

    MAX(CASE WHEN txtname='Reg form signed' THEN txtnote END) AS [Reg form signed Notes],

    MAX(CASE WHEN txtname='Serious illness within 12 mths' THEN txtcontent END) AS [Serious illness within 12 mths Content],

    MAX(CASE WHEN txtname='Serious illness within 12 mths' THEN txtnote END) AS [Serious illness within 12 mths Notes],

    MAX(CASE WHEN txtname='Serious medical condition' THEN txtcontent END) AS [Serious medical condition Content],

    MAX(CASE WHEN txtname='Serious medical condition' THEN txtnote END) AS [Serious medical condition Notes],

    MAX(CASE WHEN txtname='Sports Consent' THEN txtcontent END) AS [Sports Consent Content],

    MAX(CASE WHEN txtname='Sports Consent' THEN txtnote END) AS [Sports Consent Notes],

    MAX(CASE WHEN txtname='Swimming' THEN txtcontent END) AS [Swimming Content],

    MAX(CASE WHEN txtname='Swimming' THEN txtnote END) AS [Swimming Notes],

    MAX(CASE WHEN txtname='Teacher Development' THEN txtcontent END) AS [Teacher Development Content],

    MAX(CASE WHEN txtname='Teacher Development' THEN txtnote END) AS [Teacher Development Notes],

    MAX(CASE WHEN txtname='Tetanus vaccination' THEN txtcontent END) AS [Tetanus vaccination Content],

    MAX(CASE WHEN txtname='Tetanus vaccination' THEN txtnote END) AS [Tetanus vaccination Notes],

    MAX(CASE WHEN txtname='Undergoing medical treatment' THEN txtcontent END) AS [Undergoing medical treatment Content],

    MAX(CASE WHEN txtname='Undergoing medical treatment' THEN txtnote END) AS [Undergoing medical treatment Notes]

    FROM(SELECT TS.intStudentId,

    TS.txtTitle,

    TS.txtForename,

    TS.txtSurname,

    tblSex.txtName as 'Gender',

    TS.dteDOB,

    tblNationality.txtName as 'Nationality',

    CASE WHEN tblPosition.txtName = 'Group Leader' THEN 'Yes' ELSE 'No' End as 'GroupLeader',

    tblCourse.txtCode as 'CourseCode',

    tblEPackage.dteFromDate,

    tblEPackage.dteToDate,

    tblECourse.intNumberOfUnits,

    tblAgent.txtCode as 'Agent',

    tblEnrol.blnReturnStudent AS 'Returner',

    TS.txtNote3 AS 'AccomNotes',

    TS.txtNote1 AS 'AdditionalNotes',

    TS.txtNote2 AS 'AcademicNotes',

    tblAnalysisUDFName.txtName,

    tblAnalysisUDFItem.txtContent,

    tblAnalysisUDFItem.txtNote

    FROM tblCourse

    RIGHT OUTER JOIN tblPosition

    RIGHT OUTER JOIN tblAnalysisUDFName

    RIGHT OUTER JOIN tblStudent TS

    INNER JOIN tblEnrol ON TS.intStudentId = tblEnrol.intStudentId

    LEFT OUTER JOIN tblSex ON TS.intSexId = tblSex.intSexId

    LEFT OUTER JOIN tblNationality ON TS.intNationalityId = tblNationality.intNationalityId

    INNER JOIN tblECourseBooking ON tblEnrol.intEnrolId = tblECourseBooking.intEnrolId

    INNER JOIN tblECourse ON tblEnrol.intEnrolId = tblECourse.intEnrolId

    AND tblECourseBooking.intECourseId = tblECourse.intECourseId

    INNER JOIN tblEPackage ON tblECourse.intEPackageId = tblEPackage.intEPackageId

    INNER JOIN tblAgent ON tblEnrol.intCommissionAgentId = tblAgent.intAgentId

    INNER JOIN tblEnrolBookingStatus ON tblEnrol.intEnrolBookingStatusId = tblEnrolBookingStatus.intEnrolBookingStatusId

    LEFT OUTER JOIN tblAnalysisUDFItem ON TS.intStudentId

    = tblAnalysisUDFItem.intRecordId ON tblAnalysisUDFName.intAnalysisUDFNameId

    = tblAnalysisUDFItem.intAnalysisUDFNameId ON tblPosition.intPositionId

    = tblEnrol.intPositionId ON tblCourse.intCourseId

    = tblECourseBooking.intCourseId

    WHERE(tblEnrol.intSchoolId = 1005)

    AND (tblEnrol.intEnrolBookingStatusId < 300)

    AND (tblECourseBooking.intECourseBookingStatusId = 100)

    AND (tblECourse.intECourseStatusId = 100)

    AND (TS.intStudentID = 1529))

    as tmp

    GROUP BY intStudentId, txtTitle, txtForename, txtSurname, Gender, dteDOB, Nationality, txtName, CourseCode, dteFromDate,

    dteToDate, intNumberOfUnits, Agent, Returner,AccomNotes, AdditionalNotes, AcademicNotes

    ORDER BY intStudentId

    The results of this query are:

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLYesNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLYesNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNoNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLYesNULLNULL

    1529Miss Greta Van Aken F 151994-12-17 00:00:00.000German NoYBE10 2010-03-28 00:00:00.0002010-04-10 00:00:00.0002DGBP 0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNo

    As you can see each CASE statement's value appears on a seperate line. What I want is for all the data to appear on the one line.

    Hopefully somone can point me in the right direction.

    Thanks.

  • In you GROUP BY statement you are using the txtName column value but the select performs a CASE statement to turn the different values into 'yes' or 'no' for Group Leader.

    This is why the results look like duplicates but still appear on different rows.

    Put the same CASE logic for "Group Leader" into the GROUP BY rather than the raw txtName column and see what happens.

  • Thanks that sorted it.

    Such a simple problem and I could not see it for the want of looking.

    Thanks again.

Viewing 3 posts - 1 through 2 (of 2 total)

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