group data by any range of 30 days (not by range of dates) in SQL Server

I got a table with a list of transactions. for the example, lets say it has 4 fields: ID, UserID, DateAddedd, Amount

I would like to run a query that checks开发者_开发技巧 if there was a time, that in 30 days, a user made transactions in the sum of 100 or more

I saw lots of samples of grouping by month or a day but the problem is that if for example a user made a 50$ transaction on the 20/4 and on the 5/5 he made another 50$ transaction, the query should show it. (its 100$ or more in a period of 30 days)

I think that this should work (I'm assuming that transactions have a date component, and that a user can have multiple transactions on a single day):

;with DailyTransactions as (
    select UserID,DATEADD(day,DATEDIFF(day,0,DateAdded),0) as DateOnly,SUM(Amount) as Amount
    from Transactions group by UserID,DATEADD(day,DATEDIFF(day,0,DateAdded),0)
), Numbers as (
    select ROW_NUMBER() OVER (ORDER BY object_id) as n from sys.objects
), DayRange as (
    select n from Numbers where n between 1 and 29
)
select
    dt.UserID,dt.DateOnly as StartDate,MAX(ot.DateOnly) as EndDate, dt.Amount + COALESCE(SUM(ot.Amount),0) as TotalSpend
from
    DailyTransactions dt
        cross join
    DayRange dr
        left join
    DailyTransactions ot
        on
            dt.UserID = ot.UserID and
            DATEADD(day,dr.n,dt.DateOnly) = ot.DateOnly
group by dt.UserID,dt.DateOnly,dt.Amount
having dt.Amount + COALESCE(SUM(ot.Amount),0) >= 100.00

Okay, I'm using 3 common table expressions. The first (DailyTransactions) is reducing the transactions table to a single transaction per user per day (this isn't necessary if the DateAdded is a date only, and each user has a single transaction per day). The second and third (Numbers and DayRange) are a bit of a cheat - I wanted to have the numbers 1-29 available to me (for use in a DATEADD). There are a variety of ways of creating either a permanent or (as in this case) temporary Numbers table. I just picked one, and then in DayRange, I filter it down to the numbers I need.

Now that we have those available to us, we write the main query. We're querying for rows from the DailyTransactions table, but we want to find later rows in the same table that are within 30 days. That's what the left join to DailyTransactions is doing. It's finding those later rows, of which there may be 0, 1 or more. If it's more than one, we want to add all of those values together, so that's why we need to do a further bit of grouping at this stage. Finally, we can write our having clause, to filter down only to those results where the Amount from a particular day (dt.Amount) + the sum of amounts from later days (SUM(ot.Amount)) meets the criteria you set out.

I based this on a table defined thus:

create table Transactions (
    UserID int not null,
    DateAdded datetime not null,
    Amount decimal (38,2)
)

If I understand you correctly, you need a calendar table and then check the sum between date and date+30. So if you want to check a period of 1 year you need to check something like 365 periods.

Here is one way of doing that. The recursive CTE creates the calendar and the cross apply calculates the sum for each CalDate between CalDate and CalDate+30.

declare @T table(ID int, UserID int, DateAdded datetime, Amount money)

insert into @T values(1, 1, getdate(),    50)
insert into @T values(2, 1, getdate()-29, 60)
insert into @T values(4, 2, getdate(),    40)
insert into @T values(5, 2, getdate()-29, 50)
insert into @T values(7, 3, getdate(),    70)
insert into @T values(8, 3, getdate()-30, 80)
insert into @T values(9, 4, getdate()+50, 50)
insert into @T values(10,4, getdate()+51, 50)

declare @FromDate datetime
declare @ToDate datetime

select
  @FromDate = min(dateadd(d, datediff(d, 0, DateAdded), 0)),
  @ToDate = max(dateadd(d, datediff(d, 0, DateAdded), 0))
from @T  

;with cal as
(
  select @FromDate as CalDate
  union all
  select CalDate + 1
  from cal
  where CalDate < @ToDate
)
select S.UserID
from cal as C
  cross apply
    (select 
       T.UserID, 
       sum(Amount) as Amount
     from @T as T
     where T.DateAdded between CalDate and CalDate + 30
     group by T.UserID) as S
where S.Amount >= 100
group by S.UserID     
option (maxrecursion 0)