WA_Bothell_RSVP_55+_Volunteers

use WEB_WA_BOTHELLWEB;
/*
Tim requested this report which they then toss into a pivot table
Sums the total hours per person per volunteer category over the date range
*/
DECLARE @StartDate DATE = '7/1/2022',
@EndDate DATE = '7/31/2022';
SELECT
-- Name
--CONCAT_WS(' ', Seniors_FirstName, Seniors_LastName) AS [Full Name],
CONCAT_WS(', ', Seniors_LastName, Seniors_FirstName) [Volunteer Name],
--(0 + Convert(Char(8),GETDATE(),112) - Convert(CHAR(8),Seniors_DOB,112)) / 10000 AS [Age],
--FORMAT(Staff_CheckinHours_Date, 'd') AS [Date],
ISNULL(Staff_Hours_Name, '*Category Deleted*') AS [Volunteer Category],
--ROUND(CAST(Staff_CheckinHours_Hours/60 as float),2) as [Hours]
SUM(ROUND(CAST(Staff_CheckinHours_Hours / 60 as float), 2)) as [Hours]
FROM Staff_CheckinHours
LEFT JOIN Staff_Categories_Hours ON Staff_CheckinHours_CategoryID = Staff_Hours_ID
LEFT JOIN Seniors ON Seniors_PersonID = Staff_CheckinHours_PersonID
WHERE Staff_CheckinHours_Date BETWEEN @StartDate AND @EndDate
AND ((0 + Convert(Char(8), GETDATE(), 112) - Convert(CHAR(8), Seniors_DOB, 112)) / 10000) >= 55
GROUP BY CONCAT_WS(', ', Seniors_LastName, Seniors_FirstName), Staff_Hours_Name
ORDER BY
--Staff_CheckinHours_Date,
--[Full Name]
[Volunteer Name],
[Volunteer Category]
;