Rolling sum / count / average over date interval- Msg 206 error [MS SQL]

Question

SELECT TOP 100000 t0._id,t0.TranDate,t0.Category,t0.TranAmount,
                t0.TranBaseType,t0.Time_Of_Day, sum(t1.TranAmount) AS trans_total, 
                count(*) AS trans_count

FROM [CreditSense].[dbo].[Transaction] AS t0
INNER JOIN [CreditSense].[dbo].[Transaction] AS t1
ON t0._id=t1._id
WHERE t0.TranDate IS NOT NULL AND t1.TranDate IS NOT NULL AND
 t1.TranDate >= t0.TranDate AND t1.TranDate < (t0.TranDate) + 30
GROUP BY t0._id
ORDER BY t0.TranDate, t0._id;

I'm trying to get this to work for my data-set, but I'm getting this following error "Msg 206, Level 16, State 2, Line 1 Operand type clash: date is incompatible with int"

Is it because of adding 30? if so please recommend an alternative method please!!

Rolling sum / count / average over date interval

Biju jose · Accepted Answer · 2018-08-13T11:19:52.697

Your this part is the problem

(t0.TranDate) + 30

The error displayed is very descriptive.

"Msg 206, Level 16, State 2, Line 1 Operand type clash: date is incompatible with int"

DECLARE @datetime DATETIME =GETDATE(),
        @date DATE =GETDATE()

     SELECT @datetime+30
     SELECT @date+30

consider the above script you can see adding integer to DATETIME will work but not for DATE datatype. So you should use DATEADD() function in sql server.

DATEADD('day',30,t0.TranDate)

Reference here

Edit 2

Based on your comment to aggregate the query based on month.

SELECT TOP 100000 t0.Category,MONTH(t0.TranDate), sum(t1.TranAmount) AS trans_total, 
                count(*) AS trans_count

FROM [CreditSense].[dbo].[Transaction] AS t0
INNER JOIN [CreditSense].[dbo].[Transaction] AS t1
ON t0._id=t1._id
WHERE t0.TranDate IS NOT NULL AND t1.TranDate IS NOT NULL AND
 t1.TranDate >= t0.TranDate AND t1.TranDate < (t0.TranDate) + 30
GROUP BY t0.Category,MONTH(t0.TranDate)
ORDER BY t0.Category ;

Rolling sum / count / average over date interval- Msg 206 error [MS SQL]

1 Answers1