我有下表
| 帳戶ID | 日期 | 數量 |
|---|---|---|
| 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 |
我試圖在 5 個作業日內對金額求和并獲得如下輸出
| 帳戶ID | 日期 | 金額 |
|---|---|---|
| 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 |
我也試圖忽略周末（周六和周日）
我能夠使用以下查詢添加超過 5 天。但不能跳過周末。
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])
謝謝！
uj5u.com熱心網友回復：
我認為這是您要求的查詢：
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
結果是：
| 帳戶ID | 日期 | 金額 |
|---|---|---|
| 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 小提琴：http ://sqlfiddle.com/#!18/3d6bae/8
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/343707.html
標籤：sql sql-server 查询语句