AnsweredAssumed Answered

Advanced Reports MS SQL Server DECLARE variables

Question asked by Joe Carter on Jan 8, 2017
Latest reply on Jan 8, 2017 by Joe Carter

Hi

 

Can anyone help with syntax as I have an issue with advanced reports that seems very odd!

 

This Query is valid

DECLARE @date1 DATE,@date2 DATE
SET @date1 = '2016-12-01'
SET @date2 = '2016-12-31'
SELECT 'Calls' AS [Type], COUNT(calls.id) AS [Calls] FROM calls WHERE calls.created_by = 'userid' AND calls.date_entered BETWEEN @date1 AND @date2

 

This Query is valid

(

SELECT 'Calls' AS [Type], COUNT(calls.id) AS [Calls] FROM calls WHERE calls.created_by = 'userid' AND calls.date_entered BETWEEN '2016-12-01' AND '2016-12-31'

)

UNION

(

SELECT 'Calls' AS [Type], COUNT(calls.id) AS [Calls] FROM calls WHERE calls.created_by = 'userid' AND calls.date_entered BETWEEN '2016-12-01' AND '2016-12-31'

)

 

This Query is INVALID

DECLARE @date1 DATE,@date2 DATE
SET @date1 = '2016-12-01'
SET @date2 = '2016-12-31'

(

SELECT 'Calls' AS [Type], COUNT(calls.id) AS [Calls] FROM calls WHERE calls.created_by = 'userid' AND calls.date_entered BETWEEN @date1 AND @date2

)

UNION

(

SELECT 'Calls' AS [Type], COUNT(calls.id) AS [Calls] FROM calls WHERE calls.created_by = 'userid' AND calls.date_entered BETWEEN @date1 AND @date2

)

 

I have tried various different formats of the above query but nothing works and Sugar gives no clue to why it is invalid.

 

Any pointers much appreciated. 

Outcomes