如何按日期计算核销余额

[小野马1209]

需求描述：
如下数据，想要根据时间进行核销
2017年1101日，应收A客户500元,20171201日，应收B客户1000元，
2017年12月3日，应收A客户600元，2018年1月1日收了A客户700元

DECLARE @T TABLE (BillNo varchar(20),BillDate int,DC SmallInt,
Amount numeric(19,6),ClientId varchar(20))
INSERT INTO @T
SELECT '01',20171101,1,500,'A客户' UNION ALL
SELECT '02',20171201,1,1000,'B客户' UNION ALL
SELECT '03',20171203,1,600,'A客户' UNION ALL
SELECT '04',20180101,-1,700,'A客户'

--想要的效果
BillNo	BillDate	DC	Amount	ClientId   核销余额
01	20171101	1	500.000000	A客户        0 
02	20171201	1	1000.000000	B客户        1000
03	20171203	1	600.000000	A客户        400
04	20180101	-1	700.000000	A客户         0

[二月十六]

核销余额的计算规则是什么？

[小野马1209]

引用 1 楼 sinat_28984567 的回复:

核销余额的计算规则是什么？

根据客户及日期，如18年1月1日收款700，从最早的日期开始核销2017年1月1日500，核销的金额有剩下200，继续核销2017年12月3日的600

[二月十六]

引用 2 楼 kaijie_wu1209 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 核销余额的计算规则是什么？

根据客户及日期，如18年1月1日收款700，从最早的日期开始核销2017年1月1日500，核销的金额有剩下200，继续核销2017年12月3日的600[/quote] 还是不太懂，最后那一列是怎么得到的数据？0、1000、400和0？

[小野马1209]

引用 3 楼 sinat_28984567 的回复:

[quote=引用 2 楼 kaijie_wu1209 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 核销余额的计算规则是什么？

根据客户及日期，如18年1月1日收款700，从最早的日期开始核销2017年1月1日500，核销的金额有剩下200，继续核销2017年12月3日的600[/quote] 还是不太懂，最后那一列是怎么得到的数据？0、1000、400和0？[/quote] 我把余额规则调整下，如果多收改为负数 第一条是用收款的700-500 结果>0 用负数体现 为-200，代表预收 第二条没收款所以是1000 第三条用应收600-剩下的200 结果为 400 第四条为收款记录 未核销不用统计为为0 结果改下 BillNo BillDate DC Amount ClientId 未核销余额 01 20171101 1 500.000000 A客户 -200 02 20171201 1 1000.000000 B客户 1000 03 20171203 1 600.000000 A客户 400 04 20180101 -1 700.000000 A客户 0

[文盲老顾]

DECLARE @T TABLE (BillNo varchar(20),BillDate int,DC SmallInt,
Amount numeric(19,6),ClientId varchar(20))
INSERT INTO @T
SELECT '01',20171101,1,500,'A客户' UNION ALL
SELECT '02',20171201,1,1000,'B客户' UNION ALL
SELECT '03',20171203,1,600,'A客户' UNION ALL
SELECT '04',20180101,-1,700,'A客户'

--select * from @t

;with t as (
	select ClientId,sum(amount) as amounts from @t where dc=-1 group by ClientId
),tt as (
	select *,ROW_NUMBER() over(partition by clientId order by BillNo) as rowid from @t where dc=1
),ttt as (
	select a.*,a.Amount-b.Amounts as ye from tt a left join t b on a.ClientId=b.ClientId where rowid=1
	union all
	select a.*,a.amount+b.ye from tt a,ttt b where a.ClientId=b.ClientId and a.rowid=b.rowid+1
)
select BillNo,BillDate,DC,Amount,ClientId,(case when ye<0 then 0 else ye end) as 核销余额 from ttt

[文盲老顾]

DECLARE @T TABLE (BillNo varchar(20),BillDate int,DC SmallInt,
Amount numeric(19,6),ClientId varchar(20))
INSERT INTO @T
SELECT '01',20171101,1,500,'A客户' UNION ALL
SELECT '02',20171201,1,1000,'B客户' UNION ALL
SELECT '03',20171203,1,600,'A客户' UNION ALL
SELECT '04',20180101,-1,700,'A客户'

--select * from @t

;with t as (
	select ClientId,sum(amount) as amounts from @t where dc=-1 group by ClientId
),tt as (
	select *,ROW_NUMBER() over(partition by clientId order by BillNo) as rowid from @t where dc=1
),ttt as (
	select a.*,a.Amount-isnull(b.Amounts,0) as ye from tt a left join t b on a.ClientId=b.ClientId where rowid=1
	union all
	select a.*,a.amount+b.ye from tt a,ttt b where a.ClientId=b.ClientId and a.rowid=b.rowid+1
)
select BillNo,BillDate,DC,Amount,ClientId,(case when ye is null then 0 when ye<0 then 0 else convert(int,ye) end) as 核销余额 from ttt
union all
select *,0 from @t where dc=-1

刚才的有点小问题

[文盲老顾]

DECLARE @T TABLE (BillNo varchar(20),BillDate int,DC SmallInt,
Amount numeric(19,6),ClientId varchar(20))
INSERT INTO @T
SELECT '01',20171101,1,500,'A客户' UNION ALL
SELECT '02',20171201,1,1000,'B客户' UNION ALL
SELECT '03',20171203,1,600,'A客户' UNION ALL
SELECT '04',20180101,-1,700,'A客户'

--select * from @t

;with t as (
	select ClientId,sum(amount) as amounts from @t where dc=-1 group by ClientId
),tt as (
	select *,ROW_NUMBER() over(partition by clientId order by BillNo) as rowid from @t where dc=1
),ttt as (
	select a.*,a.Amount-isnull(b.Amounts,0) as ye from tt a left join t b on a.ClientId=b.ClientId where rowid=1
	union all
	select a.*,a.amount+b.ye from tt a,ttt b where a.ClientId=b.ClientId and a.rowid=b.rowid+1
)
select BillNo,BillDate,DC,Amount,ClientId,(case when ye is null then 0 when ye<0 then 0 else convert(int,ye) end) as 核销余额 from ttt
union all
select *,0 from @t where dc=-1

刚才的有点小问题

[RINK_1]

DECLARE @T TABLE (BillNo varchar(20),BillDate int,DC SmallInt,
Amount numeric(19,6),ClientId varchar(20))
INSERT INTO @T
SELECT '01',20171101,1,500,'A客户' UNION ALL
SELECT '02',20171201,1,1000,'B客户' UNION ALL
SELECT '03',20171203,1,600,'A客户' UNION ALL
SELECT '04',20180101,-1,700,'A客户'
 
;WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (ORDER BY BILLDATE) AS SEQ FROM @T A)

SELECT A.*,
CASE WHEN (DC=-1 OR (DC=1 AND (SUB_PAYMENT>=SUB_BALANCE))) THEN 0 
     WHEN DC=1 AND SUB_PAYMENT<SUB_BALANCE THEN SUB_BALANCE-SUB_PAYMENT
     END AS 核销余额
FROM CTE A
OUTER APPLY (SELECT ISNULL(SUM(AMOUNT),0) AS SUB_BALANCE FROM CTE WHERE ClientId=A.ClientId AND SEQ<=A.SEQ AND DC=1) AS B
OUTER APPLY (SELECT ISNULL(SUM(AMOUNT),0) AS SUB_PAYMENT FROM CTE WHERE ClientId=A.ClientId AND BILLDATE>=A.BILLDATE AND DC=-1) AS C

[ChinaITOldMan]

There are 2 ways to meet your requirement, but if you use in production env't, you need to change your table structure. 1.using funtion 2. using subquery