我正试图用两个不同的表格来计算付款的总和。对于每个ID,我需要从第一个表中连续获取日期,并从第二个表中计算amt的总和。
更新----我有~200个id,每个id都需要下面的逻辑。我正在考虑使用动态SQL、DateTable上的自连接和Rank上的两个标志从Date表中提取日期(S)。
例子-
SELECT ID, SUM(AMT) FROM PAYMENT P INNER JOIN DateTable D ON P.ID = D.ID
WHERE PAYDATE BETWEEN '6/1/2019' AND '2/1/2020'接下来的两个日期是5/1/2019和6/1/2020-
SELECT ID, SUM(AMT) FROM PAYMENT P INNER JOIN DateTable D ON P.ID = D.ID
WHERE PAYDATE BETWEEN '5/1/2019' AND '6/1/2020'第一个表- DateTable:

第二表-付款:

> Expected Output:
ID Sum
111 37 (10+13+14)
111 219 (65+45+47+62)
222 165 (89+76)
and so on...发布于 2020-02-29 12:00:21
使用lag()和join
select i.id, d.date, sum(p.amt)
from (select d.*,
lag(date) over (partition by id order by date) as prev_date
from datetable d
) d cross join
(select distinct id from payment
) i left join
payment p
on p.id = i.id and
p.paydate > d.prev_date and
p.paydate <= date and
group by i.id, d.date;发布于 2020-02-29 10:49:24
您可以使用UNION ALL组合以下两个查询的结果
SELECT ID, SUM(AMT) FROM PAYMENT P INNER JOIN DateTable D ON P.ID = D.ID
WHERE PAYDATE BETWEEN '6/1/2019' AND '2/1/2020'
GROUP BY ID
UNION ALL
SELECT ID, SUM(AMT) FROM PAYMENT P INNER JOIN DateTable D ON P.ID = D.ID
WHERE PAYDATE BETWEEN '5/1/2019' AND '6/1/2020'
GROUP BY IDhttps://stackoverflow.com/questions/60463891
复制相似问题