-1
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

closed as off-topic by hot2use, Philᵀᴹ, Colin 't Hart, RDFozz, Marco Aug 14 '18 at 9:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – hot2use, Philᵀᴹ, Colin 't Hart, RDFozz, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Since you first tried to post under this related question: You must know that Postgres can add date + integer like demonstrated there. But that does not work the same way in SQL Server. – Erwin Brandstetter Aug 13 '18 at 8:31
1

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 ;  
  • Now im getting this following error "Msg 8120, Level 16, State 1, Line 1 Column 'CreditSense.dbo.Transaction.TranDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" – Harish Mohan Aug 13 '18 at 10:50
  • @HarishMohan, the group by clause of yours is wrong, compared to your select clause. Any columns not in the GROUP BY clause, if referenced without an aggregation function will throw this error. Try running your GROUP BY clause like GROUP BY t0._id,t0.TranDate,t0.Category,t0.TranAmount, t0.TranBaseType,t0.Time_Of_Day – Biju jose Aug 13 '18 at 10:54
  • @HarishMohan, also check your result is what you expect. – Biju jose Aug 13 '18 at 10:54
  • Thanks Man, yea i removed all of then expect the sum and count.It summarizing per day, but i want it for every 30 days. For instance if i have a date field with 90 entries I want to get the Sum(t1.TranAmount) per 30 days and Count(t1.TranAmount) per 30days so i should end with 3 rows. right?? – Harish Mohan Aug 13 '18 at 11:07
  • @HarishMohan,Do a GROUP BY MONTH(t0.TranDate) . that should hep. – Biju jose Aug 13 '18 at 11:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.