{% assign dateRange = 'Global' | PageParameter:'DateRange' %}
{% if dateRange and dateRange != '' %}
{% assign dates = dateRange | Split:',',false %}
{% assign startDate = dates[0] %}
{% assign endDate = dates[1] %}
DECLARE @StartDate DATE {% if startDate and startDate != '' %}= '{{ startDate | Date:'yyyy-MM-dd' }}'{% endif %}
DECLARE @EndDate DATE {% if endDate and endDate != '' %}= '{{ endDate | Date:'yyyy-MM-dd' }}'{% endif %}
{% else %}
DECLARE @StartDate DATE {% if startDate and startDate != '' %}= '{{ startDate | Date:'yyyy-MM-dd' }}'{% endif %}
DECLARE @EndDate DATE {% if endDate and endDate != '' %}= '{{ endDate | Date:'yyyy-MM-dd' }}'{% endif %}
{% endif %}
DECLARE @MyCampusId int
IF ISNULL(@CampusId,'') <> '' SET @MyCampusId = ( SELECT TOP 1 [Id] FROM [Campus] WHERE [Guid] = TRY_CAST( @CampusId as uniqueidentifier ) )
SELECT
'Water Baptisms' AS [Title]
,CONVERT(VARCHAR(MAX),COUNT(*)) AS [Value]
FROM [Person] P
INNER JOIN AttributeValue AV1 ON P.[Id] = AV1.[EntityId] AND AV1.[AttributeId] = 174
WHERE
P.[PrimaryCampusId] = @MyCampusId
AND CAST(AV1.[ValueAsDateTime] AS Date) BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT
'Weekend' AS [Title]
,CONVERT(VARCHAR(MAX),CAST(SUM(MV.[YValue]) AS int)) AS [Value]
FROM [MetricValue] MV
WHERE
MV.[MetricId] = 39
AND MV.[MetricValueDateTime] BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT
'Starting Point'
,CONVERT(VARCHAR(MAX),COUNT(*)) AS [Value]
FROM [Step] S
WHERE
S.[StepTypeId] = 2
AND S.[CampusId] = @MyCampusID
AND CAST(S.[CompletedDateTime] AS Date) BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT
'Giving' AS [Title]
,CONVERT(VARCHAR(MAX),SUM(FTD.[Amount])) AS [Value]
FROM [FinancialTransactionDetail] FTD
JOIN [FinancialTransaction] FT ON FTD.[TransactionId] = FT.[Id]
JOIN [FinancialAccount] FA ON FTD.[AccountId] = FA.[Id]
JOIN [Campus] C ON C.[Id] = FA.[CampusId]
WHERE
C.[Id] = @MyCampusID
AND CAST(FT.[TransactionDateTime] AS Date) BETWEEN @StartDate and @EndDate
To see the code in raw format, click here