Sum over N days in SQL server

Question

I have below table

AccountID	Date	Amount
123	07/06/2021	2000
123	07/12/2021	9000
123	07/16/2021	500
123	07/20/2021	500
123	07/28/2021	500

I am trying to sum the amount over 5 working days and get the output like below

AccountID	Date	Sum Amount
123	07/06/2021	11000
123	07/12/2021	9500
123	07/16/2021	1000
123	07/20/2021	500
123	07/28/2021	500

Also I am trying to ignore weekends(Saturday and Sunday)

I was able to add over 5 days using the below query. But not able to skip weekends.

Select distinct
t1.accountid,
convert(datetime,t1.[date]),
t1.amount,
sum(t2.amount)
from [dbo].[HANMI_ABRIGO_TRANSACTIONS] t1
cross apply 
(
    SELECT *
    FROM [dbo].[HANMI_ABRIGO_TRANSACTIONS] a
    WHERE a.accountid= t1.accountid 
    AND 
        (
        convert(datetime,a.[date]) < DATEADD(DAY,5,convert(datetime,t1.[date])) 
        AND
        convert(datetime,a.[date]) >= convert(datetime,t1.[date])
        ) 
    And a.accountid = '123'
    And a.date like '2021-07%'
    and a.amount > 0
)t2
where t1.accountid = '123'
And t1.date like '2021-07%'
and t1.amount > 0
group by 
t1.accountid,
convert(datetime,t1.[date]),
t1.amount
order by convert(datetime,t1.[date])

Thanks!

user_2232868 · Answer

I think this is the query you are asking for:

SELECT AccountId, Date,
(
  SELECT SUM(Amount) 
  FROM HANMI_ABRIGO_TRANSACTIONS h2
  WHERE 
    h1.AccountID = h2.AccountID and 
    DATEPART(WEEKDAY, h2.Date) not in (1, 7) and
    h2.Date between h1.Date AND DATEADD(d, 5, h1.Date)
) as SumAmount
FROM HANMI_ABRIGO_TRANSACTIONS h1

The results are:

AccountId	Date	SumAmount
123	2021-07-06	2000
123	2021-07-12	9500
123	2021-07-16	1000
123	2021-07-20	500
123	2021-07-28	500

SQL Fiddle: http://sqlfiddle.com/#!18/3d6bae/8

Sum over N days in SQL server

1 Answers