{% 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