I have below table

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

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!

CodePudding user response：

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:

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