Query:
Update the variables on the first few lines of the query to match your system:
@ConfigPurposeID should match the ID of the "Check-In Template" Group Type Purpose defined value@FilterPurposeID should match the ID of the "Check-In Filter" Group Type Purpose defined value@SpecialNeedsAttrID should either be removed or it should match the attribute ID of a boolean
"Has Special Needs" person attribute. There are two other sections lower in the code that may need to be deleted as well
if you aren't going to use this feature.
DECLARE @ConfigPurposeID AS int = 142
DECLARE @FilterPurposeID AS int = 145
DECLARE @SpecialNeedsAttrID AS int = 10360 --****** Delete if not applicable ******
----------------------------------------
SET NOCOUNT ON
--parse the date range parameter
SELECT CAST(Value AS date) AS Date INTO #DATERANGE FROM STRING_SPLIT(@DateRange, ',')
DECLARE @StartDate AS date
DECLARE @EndDate AS date
SELECT @StartDate = MIN(Date) FROM #DATERANGE
SELECT @EndDate = MAX(Date) FROM #DATERANGE
DROP TABLE #DATERANGE
--make sure the start and end dates contain a value
DECLARE @RightNow AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
IF @StartDate = '1900-01-01' BEGIN
SET @StartDate = CAST(FORMAT(DATEADD(week, -1, @RightNow), 'yyyy-MM-dd') AS date)
END
IF @EndDate = '1900-01-01' BEGIN
SET @EndDate = CAST(FORMAT(@RightNow, 'yyyy-MM-dd') AS date)
END;
-- find all of the check-in areas
WITH CheckInAreas AS
(
SELECT PT.ID AS ConfigID, PT.GroupTypePurposeValueID AS ConfigPurposeID,
PT.[Name] AS Config, PT.IconCSSClass, PT.[Order] AS ConfigSort,
CT.ID AS AreaID, CT.GroupTypePurposeValueID AS AreaPurposeID,
CT.[Name] AS Area, CT.[Order] AS AreaSort, 1 AS Depth
FROM GroupTypeAssociation GTA INNER JOIN
GroupType PT ON GTA.GroupTypeId = PT.ID INNER JOIN
GroupType CT ON GTA.ChildGroupTypeId = CT.ID
WHERE PT.GroupTypePurposeValueID = @ConfigPurposeID
AND ISNULL(CT.GroupTypePurposeValueID, 0) <> @FilterPurposeID
AND (PT.ID = @ConfigID OR @ConfigID = 0)
UNION ALL
SELECT CA.ConfigID, CA.ConfigPurposeID,
CA.Config, CA.IconCSSClass, CA.ConfigSort,
GTA.ChildGroupTypeID AS AreaID, CT.GroupTypePurposeValueID AS AreaPurposeID,
CT.[Name] AS Area, CT.[Order] AS AreaSort, CA.Depth + 1 AS Depth
FROM GroupTypeAssociation GTA INNER JOIN
CheckInAreas CA on CA.AreaID = GTA.GroupTypeID INNER JOIN
GroupType CT ON GTA.ChildGroupTypeID = CT.ID
WHERE GTA.GroupTypeID <> GTA.ChildGroupTypeID
AND CA.Depth < 10 --don't fall down the rabbit hole
)
SELECT * INTO #CHECKINAREAS FROM CheckinAreas
GROUP BY ConfigID, ConfigPurposeID, Config, IconCSSClass, ConfigSort, AreaID, AreaPurposeID, Area, AreaSort, Depth
-- get all of the attendance records for each specific group
SELECT CA.ConfigID,
SC.ID AS CategoryID,
ISNULL(LC.ID, ISNULL(LB.ID, LR.ID)) AS LocationID,
GT.ID AS AreaID,
G.ID AS GroupID,
CA.Config, CA.IconCSSClass,
SC.[Name] AS Category,
ISNULL(LC.[Name], ISNULL(LB.[Name], LR.[Name])) AS [Location],
GT.[Name] AS Area,
CASE WHEN PG.Id IS NULL THEN G.[Name] ELSE PG.[Name] + ' > ' + G.[Name] END AS [Group],
CA.ConfigSort, ISNULL(SC.[Order], 0) AS CategorySort, GT.[Order] AS AreaSort,
CASE WHEN PG.Id IS NULL THEN G.[Order] ELSE PG.[Order] END AS PGroupSort,
G.[Order] AS GroupSort,
SD2.[Day], SD2.[Time],
CASE WHEN @GroupByDay = 'true' THEN -1 ELSE SUBSTRING(SD2.[Time], 1, 2) END AS [Hour],
CASE WHEN @GroupByDay = 'true' THEN -1 ELSE SUBSTRING(SD2.[Time], 3, 2) END AS [Minute],
COUNT(*) AS Total
INTO #ATTENDANCE
FROM Attendance A
INNER JOIN AttendanceOccurrence AO ON AO.Id = A.OccurrenceId
INNER JOIN [Group] G ON G.Id = AO.GroupId
INNER JOIN GroupType GT ON G.GroupTypeId = GT.ID
INNER JOIN #CHECKINAREAS CA ON CA.AreaID = G.GroupTypeID
INNER JOIN Schedule S ON AO.ScheduleID = S.ID
CROSS APPLY
(
SELECT
DATEPART(dw, AO.OccurrenceDate) AS [Day],
CASE @GroupByDay WHEN 'true' THEN '1200' ELSE
--attempt to get the most accurate schedule time
CASE WHEN AO.OccurrenceDate > S.ModifiedDateTime THEN
--if the occurrence happened after the last time the schedule was modified, then we can assume the time of day on the schedule is still accurate
CASE WHEN ISNULL(S.iCalendarContent, '') = '' THEN
FORMAT(CAST(S.WeeklyTimeOfDay AS datetime),'HHmm')
ELSE
SUBSTRING(S.iCalendarContent, PATINDEX('%DTSTART:________T______%', S.iCalendarContent) + 17, 4)
END
ELSE
--if the schedule was modified after the occurrence, then assume the schedule time may have changed.
--instead, 'estimate' the schedule time by rounding the attendence start time down to the nearest hour.
--TODO: find a better way to estimate the schedule time without using the schedule record
FORMAT(A.StartDateTime, 'HH00')
END
END AS [Time]
) AS SD
CROSS APPLY
(
SELECT
CASE SD.[Day] WHEN 1 THEN 8 ELSE SD.[Day] END AS [Day],
CASE SD.[Time] WHEN '' THEN '1200' ELSE SD.[Time] END AS [Time]
) AS SD2
--****** Delete section if not applicable ******
CROSS APPLY
(
SELECT ISNULL(SNV.Value, 0) AS HasSpecialNeeds
FROM PersonAlias PA
INNER JOIN Person P ON P.ID = PA.PersonID
LEFT JOIN AttributeValue SNV ON SNV.EntityID = P.ID AND SNV.AttributeID = @SpecialNeedsAttrID
WHERE PA.ID = A.PersonAliasID
) SN
--****** End Delete ******
LEFT JOIN Category SC ON S.CategoryID = SC.ID
LEFT JOIN [Group] PG ON PG.ID = G.ParentGroupID
LEFT JOIN Location LR ON LR.ID = AO.LocationID
LEFT JOIN Location LB ON LR.ParentLocationID = LB.ID
LEFT JOIN Location LC ON LB.ParentLocationID = LC.ID
WHERE A.DidAttend = 1
AND AO.OccurrenceDate BETWEEN @StartDate AND @EndDate
AND (S.IsActive = 1 OR @ActiveOnly = 'false')
--****** Delete line if not applicable ******
AND (SN.HasSpecialNeeds = 'true' OR @SpecialNeedsOnly <> 'true')
--****** End Delete ******
AND (ISNULL(LC.ID, ISNULL(LB.ID, LR.ID)) = @Location OR @Location = 0)
GROUP BY PG.Id, SC.ID, ISNULL(LC.ID, ISNULL(LB.ID, LR.ID)), GT.ID, G.ID, CA.ConfigID,
CA.Config, CA.IconCSSClass, CA.ConfigSort, GT.[Name], GT.[Order], PG.[Name], PG.[Order], G.[Name],
G.[Order], SC.[Name], SC.[Order], SD2.[Day], SD2.[Time],
ISNULL(LC.[Name], ISNULL(LB.[Name], LR.[Name]))
-- calculate the total counts for each group, area, location and config
-- Group Attendance
SELECT ConfigID, ISNULL(CategoryID, 0) AS CategoryID, ISNULL(LocationID, 0) AS LocationID, ISNULL(AreaID, 0) AS AreaID,
COUNT(*) AS GroupCount
INTO #GROUPCOUNT
FROM (SELECT ConfigID, CategoryID, LocationID, AreaID, GroupID
FROM #ATTENDANCE
GROUP BY ConfigID, CategoryID, LocationID, AreaID, GroupID) A
GROUP BY ConfigID, CategoryID, LocationID, AreaID
-- Area Attendance
SELECT ConfigID, CategoryID, LocationID,
SUM(GroupCount) AS AreaCount
INTO #AREACOUNT
FROM #GROUPCOUNT
GROUP BY ConfigID, CategoryID, LocationID
-- Location Attendance
SELECT ConfigID, CategoryID,
SUM(AreaCount) AS LocCount
INTO #LOCCOUNT
FROM #AREACOUNT
GROUP BY ConfigID, CategoryID
-- Configuration Attendance
SELECT ConfigID,
SUM(LocCount) AS CatCount
INTO #CATCOUNT
FROM #LOCCOUNT
GROUP BY ConfigID
-- gather all of the distinct schedule days & times
SELECT ROW_NUMBER() OVER(ORDER BY [Day], Hour24, [Minute]) AS RowNum, *
INTO #SCHEDULES
FROM (SELECT [Day],
CASE [Day] WHEN 2 THEN 'M' WHEN 3 THEN 'T' WHEN 4 THEN 'W' WHEN 5 THEN 'Th' WHEN 6 THEN 'F' WHEN 7 THEN 'S' WHEN 8 THEN 'Su' ELSE '' END AS [DayName],
CASE WHEN @GroupByDay = 'true' THEN -1 ELSE ISNULL(SUBSTRING([Time], 1, 2), 0) END AS Hour24,
CASE WHEN @GroupByDay = 'true' THEN -1 ELSE ISNULL(SUBSTRING([Time], 1, 2) % 12, 0) END AS Hour12,
CASE WHEN @GroupByDay = 'true' THEN -1 ELSE ISNULL(SUBSTRING([Time], 3, 2), 0) END AS [Minute],
SUM(Total) AS Total
FROM #ATTENDANCE
GROUP BY [Day], [Time]) A
ORDER BY [Day], Hour24, [Minute]
--select the data
--TABLE 1 - Attendance
SELECT A.ConfigID, A.CategoryID, A.LocationID, A.AreaID, A.GroupID,
A.IconCSSClass, A.Config, ISNULL(A.Category, 'N/A') AS Category,
ISNULL(A.[Location],'N/A') AS [Location], A.Area, A.[Group],
A.[Day], A.[Hour], A.[Minute], A.Total, S.RowNum AS ColNum,
CC.CatCount, LC.LocCount, AC.AreaCount, GC.GroupCount
FROM #ATTENDANCE A
INNER JOIN #SCHEDULES S ON S.[Day] = A.[Day] AND S.Hour24 = A.[Hour] AND S.[Minute] = A.[Minute]
LEFT JOIN #CATCOUNT CC ON CC.ConfigID = A.ConfigID
LEFT JOIN #LOCCOUNT LC ON LC.ConfigID = A.ConfigID AND LC.CategoryID = ISNULL(A.CategoryID, 0)
LEFT JOIN #AREACOUNT AC ON AC.ConfigID = A.ConfigID AND AC.CategoryID = ISNULL(A.CategoryID, 0) AND AC.LocationID = ISNULL(A.LocationID, 0)
LEFT JOIN #GROUPCOUNT GC ON GC.ConfigID = A.ConfigID AND GC.CategoryID = ISNULL(A.CategoryID, 0) AND GC.LocationID = ISNULL(A.LocationID, 0) AND GC.AreaID = ISNULL(A.AreaID, 0)
ORDER BY ConfigSort, Config, CategorySort, Category, [Location], AreaSort, Area, PGroupSort, GroupSort, [Group], GroupID, ColNum
--TABLE 2 - Schedules
SELECT * FROM #SCHEDULES
--clean up
DROP TABLE IF EXISTS #CHECKINAREAS
DROP TABLE IF EXISTS #ATTENDANCE
DROP TABLE IF EXISTS #SCHEDULES
DROP TABLE IF EXISTS #CATCOUNT
DROP TABLE IF EXISTS #LOCCOUNT
DROP TABLE IF EXISTS #AREACOUNT
DROP TABLE IF EXISTS #GROUPCOUNT