-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

Harish Mohan
  • 3
  • 3

1 Answers1

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 ;
Biju jose
  • 2,088
  • 2
  • 18
  • 27
  • 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
  • Getting the Msg8120 error again and also added month(t0.TranData) in the select statement – Harish Mohan Aug 13 '18 at 11:17
  • @HarishMohan, you still forgot to add this in your SELECT list. I have updated the answer with a new one. – Biju jose Aug 13 '18 at 11:20
  • Bro You are the man!!! – Harish Mohan Aug 13 '18 at 11:26
  • @HarishMohan, If you find the answer helpful. Accept it as answer by clicking the tick mark. – Biju jose Aug 13 '18 at 11:27
  • trans_total and trans_counts are too big i reckon. I just chose a random _id and counted the transactions in only like 215 but from this query its 2746. You reckon the WHERE statement is right? – Harish Mohan Aug 13 '18 at 11:31
  • @HarishMohan, if you can post a sample data and the expected data from it. I can easily help. Till now I was guessing based on your input. Try posting your DDL, data and expected output. – Biju jose Aug 13 '18 at 11:36
  • Sorry, I cannot. The data doesn't belong to me. Thanks for your help have a nice day bud! – Harish Mohan Aug 13 '18 at 11:44