Webhook Code

{% capture cacheKey %}CAMPUS-{{ QueryString.Year }}-{{ QueryString.AccountType }}-{{ QueryString.GroupBy }}{% endcapture %}
{% cache key:'{{ cacheKey }}' duration:'10800' %}
{% sql %}
SET DATEFIRST 2 -- Tuesday
DECLARE @GroupBy INT = {{ QueryString.GroupBy }}

SELECT 
	 SUM(FTD.Amount) AS [Value]
	,CASE
			WHEN @GroupBy = 1 THEN 
			    -- If the weekly start date happens to be before the current year, clamp it to the first day of the year
				CASE 
					WHEN DATEPART(YEAR, CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) - 1, FT.TransactionDateTime)), 101)) < DATEPART(Year, FT.TransactionDateTime)
					THEN CONVERT(varchar(50), DATEFROMPARTS(DATEPART(Year, FT.TransactionDateTime),1,1), 101)
					ELSE CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) - 1, FT.TransactionDateTime)), 101)
				END
				+ ' - ' + 
				-- If the weekly end date happens to be after the current year, clamp it to the last day of the year
				CASE 
					WHEN DATEPART(YEAR, CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) + 5, FT.TransactionDateTime)), 101)) > DATEPART(Year, FT.TransactionDateTime)
					THEN CONVERT(varchar(50), DATEFROMPARTS(DATEPART(Year, FT.TransactionDateTime),12,31), 101)
					ELSE CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) + 5, FT.TransactionDateTime)), 101)
				END
			WHEN @GroupBy = 2 THEN FORMAT(FT.TransactionDateTime, 'MMMM yyyy')
			WHEN @GroupBy = 3 THEN 'Q' + CAST(DATEPART(Q, FT.TransactionDateTime) AS VARCHAR) + ' ' + FORMAT(FT.TransactionDateTime, 'yyyy')
		 END AS [XValueLabel]
	,CASE
		WHEN @GroupBy = 1 THEN DATEPART(WEEK, FT.TransactionDateTime)
		WHEN @GroupBy = 2 THEN DATEPART(MONTH, FT.TransactionDateTime)
		WHEN @GroupBy = 3 THEN DATEPART(Q, FT.TransactionDateTime)	 
	END AS [XValue]
	,C.Name AS [Campus]
FROM FinancialTransaction FT
INNER JOIN FinancialTransactionDetail FTD
	ON FTD.TransactionId = FT.Id
INNER JOIN FinancialAccount FA
	ON FA.Id = FTD.AccountId
INNER JOIN Campus C
	ON C.Id = FA.CampusId
WHERE
	 ('{{ QueryString.Year | Replace:"'","''" }}' = DATEPART(YEAR, FT.TransactionDateTime))
GROUP BY CASE
            WHEN @GroupBy = 1 THEN 
			    -- If the weekly start date happens to be before the current year, clamp it to the first day of the year
				CASE 
					WHEN DATEPART(YEAR, CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) - 1, FT.TransactionDateTime)), 101)) < DATEPART(Year, FT.TransactionDateTime)
					THEN CONVERT(varchar(50), DATEFROMPARTS(DATEPART(Year, FT.TransactionDateTime),1,1), 101)
					ELSE CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) - 1, FT.TransactionDateTime)), 101)
				END
				+ ' - ' + 
				-- If the weekly end date happens to be after the current year, clamp it to the last day of the year
				CASE 
					WHEN DATEPART(YEAR, CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) + 5, FT.TransactionDateTime)), 101)) > DATEPART(Year, FT.TransactionDateTime)
					THEN CONVERT(varchar(50), DATEFROMPARTS(DATEPART(Year, FT.TransactionDateTime),12,31), 101)
					ELSE CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, FT.TransactionDateTime) + 5, FT.TransactionDateTime)), 101)
				END
			WHEN @GroupBy = 2 THEN FORMAT(FT.TransactionDateTime, 'MMMM yyyy')
			WHEN @GroupBy = 3 THEN 'Q' + CAST(DATEPART(Q, FT.TransactionDateTime) AS VARCHAR) + ' ' + FORMAT(FT.TransactionDateTime, 'yyyy')
		 END
		,CASE
			WHEN @GroupBy = 1 THEN DATEPART(WEEK, FT.TransactionDateTime)
			WHEN @GroupBy = 2 THEN DATEPART(MONTH, FT.TransactionDateTime)
			WHEN @GroupBy = 3 THEN DATEPART(Q, FT.TransactionDateTime)	 
		END
		,C.[Name]
ORDER BY C.[Name]
	,CASE
		WHEN @GroupBy = 1 THEN DATEPART(WEEK, FT.TransactionDateTime)
		WHEN @GroupBy = 2 THEN DATEPART(MONTH, FT.TransactionDateTime)
		WHEN @GroupBy = 3 THEN DATEPART(Q, FT.TransactionDateTime)	 
	END
SET DATEFIRST 7 -- Back to Sunday
{% endsql %}
{{ results | ToJSON }}
{% endcache %}