我必须在SQL中实现类似FIFO方法的东西。问题是,我在不同的月份发放积分,同时在其他日期交换积分。问题是,我必须知道多少个月前被发行的积分,直到交易所。因此,首先要兑换的积分是老年人,但如果在2014年6月30日兑换200点,在2014年7月31日兑换100点,那么我兑换了250点。200分有2个月的发展期(6月的200分),50分有1个月的开发期(7月发布的100分中有50分)。我如何对此进行编码?
我留了一些桌子来更好地理解!
感谢
Date of issue Number of account Issued points
30-abr 1 300
31-may 1 50
30-jun 1 100
30-jun 2 100
30-jun 3 120
31-may 4 20
30-jun 4 200
Date of exchange Number of account Exchanged points
30-jun 1 250
31-jul 1 200
31-jul 3 30
30-jun 4 30
31-jul 2 10
30-jun 3 30
31-ago 4 10
我需要的最后一张桌子是
Issue date Months after issue Exchanged points
30-abr 2,00 250,00
30-abr 3,00 50,00
31-may 2,00 50,00
31-may 1,00 20,00
30-jun 1,00 100,00
30-jun 1,00 30,00
30-jun 0,00 10,00
30-jun 1,00 10,00
30-jun 0,00 30,00
30-jun 2,00 10,00
编辑:添加示例以澄清帐户1:的问题
在3个不同月份内发布的积分:
Date of issue Points
30-apr 300
31-may 50
30-jun 100
这些在两种情况下交换:
Date of exchange Points
30-jun 250
31-jul 200
由于交换是根据不同月份发布的积分进行的,因此结果应该是4个操作,以便首先使用最早的积分:
Date of exchange Date of issue Points Months
30-jun 30-apr 250 2
31-jul 30-apr 50 3
31-jul 31-may 50 2
31-jul 30-jun 100 1
我试过这个,这个SQL返回正确的结果。不知怎的,我觉得它可能有漏洞,但至少这是一个开始(或者完全错误的方式):
;with I as (
select
date,
points,
account,
isnull((select sum(points) from issue i2
where i2.account = i1.account and i2.date < i1.date),0) as Cumulative
from
issue i1
),
E as (
select
date,
points,
account,
isnull((select sum(points) from Exchange e2
where e2.account = e1.account and e2.date < e1.date),0) as Cumulative
from
Exchange e1
),
X as (
select
I.Account,
I.Date as IDate, I.Points as IPoints, I.Cumulative as ICumulative,
E.Date as EDate, E.Points as EPoints, E.Cumulative as ECumulative
from I
join E on I.account = E.account
and I.Points + I.Cumulative > E.Cumulative
and E.Cumulative + E.Points > I.Cumulative
)
select
Account, IDate, datediff(month, IDate, EDate) as After,
case when EPoints + ECumulative < IPoints + ICumulative
then EPoints
else IPoints + ICumulative - ECumulative end
- case when ICumulative > ECumulative
then (ICumulative - ECumulative)
else 0 end as Points
from X
order by IDate, EDate
前两个CTE(I和E)计算点的运行总数,第三个(X)根据发布和交换的内容收集所需的行。计算最终点列的公式只是试验和错误,所以我不能100%确定它在所有情况下都是准确的:)
您可以在SQL Fiddle 中尝试此操作