Click on the button below to copy the code.
Copy Code
or
Download 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 %}