Results 1 to 2 of 2

Thread: Need SQL Help

  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Question Unanswered: Need SQL Help

    I have following query which is joining couple of tables. i have a field "Status" in MeetingAttendees table. I have to add one more check (probably one more case statement) that if MA.Status=4 then Count(A.AttendeeID) as NoofAttendees. So NoofRSVPs (doesn't matter what is the status in MeetingAttendees table) will return total RSVPs and NoofAttendee will return only # of Attendees. how can i add do that? please help...

    SELECT

    M.State AS MeetingState,

    CASE
    WHEN MA.AttendeeType = 1 THEN 'Participant'
    WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
    WHEN MA.AttendeeType = 3 THEN 'Client'
    WHEN MA.AttendeeType = 4 THEN 'Staff'
    END AS AttendeeType,

    Count(A.AttendeeID) as NoofRSVPs

    FROM
    Programs P
    INNER
    JOIN eCDReservations M
    ON P.SubCompanyCode = M.SubCompanyCode
    AND P.ProgramCode = M.ProgramCode
    left outer
    JOIN MeetingAttendees MA
    ON M.ReservationID = MA.MeetingID
    left outer
    JOIN Attendees A
    ON MA.AttendeeID = A.AttendeeID
    left outer
    JOIN Regions R
    ON MA.RegionCode = R.RegionCode
    WHERE
    P.SubCompanyCode = @SubCompanyCode AND
    P.ProgramCode = @ProgramCode




    GROUP BY

    M.State,

    MA.AttendeeType

    ORDER BY

    MA.AttendeeType

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    COUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •