center

Script Title

Notes

The Script

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]
;

X

Manage Category List

X

Center

url

Button

idBox

Choose

Success!