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...
M.State AS MeetingState,
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
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
JOIN Regions R
ON MA.RegionCode = R.RegionCode
P.SubCompanyCode = @SubCompanyCode AND
P.ProgramCode = @ProgramCode