Casting COALESCE to INT in Stored Procedure

I currently have a stored procedure which returns data and displayed in my report viewer but my issue is that i 开发者_如何学Pythoncheck to see if students attended to class or not and i have

COALESCE(A.Attended, 0)AS Attended

This returns 1 if they attended and 0 if not - in my report it only shows 1 or 0 even though they could have attended more than once. How can i cast this to an int to get the right total.

thanks

WHOLE QUERY:

SELECT 
    P.PartyId,
    COUNT(COALESCE(A.Attended, 0))AS Attended,
    COUNT(DISTINCT H.HearingId) AS Hearings,
    O.OfficeName As OfficeName,
    CO.Name,
    P.FirstName AS FirstName,
    P.LastName AS LastName,
    P.BirthDate AS DOB
FROM Activity A 
INNER JOIN ActivityType AT On A.ActivityTypeId = AT.ActivityTypeId
INNER JOIN ActivityEntry AE ON A.ActivityEntryId = AE.ActivityEntryId
INNER JOIN HearingEntry HE ON CAE.HearingEntryId = HE.HearingEntryId
INNER JOIN Hearing H ON HE.HearingEntryId = H.HearingEntryId
INNER JOIN [Case] C ON H.CaseId = C.CaseId
INNER JOIN CaseOffice CO ON C.CaseId = CO.CaseId AND AE.OfficeId = CO.OfficeId
INNER JOIN Office O ON CO.OfficeId = O.OfficeId
INNER JOIN Attended A ON H.HearingId = A.HearingId
INNER JOIN Party P ON A.PartyId = P.PartyId
WHERE HP.PartyId = P.PartyId AND AE.OfficeId = @OfficeId AND(H.HearingDate >= @BeginDate AND (H.HearingDate <= @EndDate OR H.HearingDate IS NULL)) AND HE.HearingEntryId = CAE.HearingEntryId
GROUP BY P.PartyId, A.Attended, O.OfficeName,CO.Name,P.FirstName, P.LastName,P.BirthDate

I guess you mean that A.Attented is a bit and you want it to be a int, so that you can aggregate later?

You can cast to a bit to an int like this:

CAST(A.Attented AS INT)

Or in this case:

COALESCE(CAST(A.Attended AS INT), 0) AS Attended

You need to SUM by Attended .

SELECT 
    P.PartyId,
    (SUM(COALESCE(A.Attended, 0)))AS Attended,
    COUNT(DISTINCT H.HearingId) AS Hearings,
    O.OfficeName As OfficeName,
    CO.Name,
    P.FirstName AS FirstName,
    P.LastName AS LastName,
    P.BirthDate AS DOB
FROM Activity A 
INNER JOIN ActivityType AT On A.ActivityTypeId = AT.ActivityTypeId
INNER JOIN ActivityEntry AE ON A.ActivityEntryId = AE.ActivityEntryId
INNER JOIN HearingEntry HE ON CAE.HearingEntryId = HE.HearingEntryId
INNER JOIN Hearing H ON HE.HearingEntryId = H.HearingEntryId
INNER JOIN [Case] C ON H.CaseId = C.CaseId
INNER JOIN CaseOffice CO ON C.CaseId = CO.CaseId AND AE.OfficeId = CO.OfficeId
INNER JOIN Office O ON CO.OfficeId = O.OfficeId
INNER JOIN Attended A ON H.HearingId = A.HearingId
INNER JOIN Party P ON A.PartyId = P.PartyId
WHERE HP.PartyId = P.PartyId AND AE.OfficeId = @OfficeId AND(H.HearingDate >= @BeginDate AND (H.HearingDate <= @EndDate OR H.HearingDate IS NULL)) AND HE.HearingEntryId = CAE.HearingEntryId
GROUP BY P.PartyId, O.OfficeName,CO.Name,P.FirstName, P.LastName,P.BirthDate