sql - SQL Server查询从前几个月的值中提取/更新丢失的数据


REV


Product ID Transaction Date Output should be


101 3/5/2018 16.8


101 3/24/2018 16.8


101 4/10/2018 16.8


101 5/30/2018 7.6


101 6/25/2018 14.3


102 1/1/2019 30.11


102 2/4/2019 30.11


102 2/11/2019 30.11


103 2/17/2019 6.62


103 2/25/2019 6.62


103 3/24/2019 6.62


103 3/30/2019 6.62



对于REV表,需要带来基于PROD ID和Month/Year Match的成本,如果不可用,它应该检查最近6个月的可用成本,


Cost


Product ID PCR Period Cost


101 Jan-18 16.8


101 May-18 7.6


101 Jun-18 14.3


101 Jul-18 301.88


101 Aug-18 6.62


101 Nov-18 0.01


102 Dec-18 6.62


102 May-18 47.95


102 Jun-18 79.8


102 Jul-18 3.49


102 Jan-19 30.11


103 Mar-19 102.11



时间:


select r.id, r.td, (


 select top 1 c.cost from cost c


 where c.id = r.id


 and datediff(day, CAST('01-' + c.td AS datetime), r.td) >= 0


 order by CAST('01-' + c.td AS datetime) desc


) as cost


from rev r



示例脚本:


declare @REV table (id int, trandate datetime)



insert into @REV (id, trandate) values (101, '3/5/2018')


insert into @REV (id, trandate) values (101, '3/24/2018')


insert into @REV (id, trandate) values (101, '4/10/2018')


insert into @REV (id, trandate) values (101, '5/30/2018')


insert into @REV (id, trandate) values (101, '6/25/2018')


insert into @REV (id, trandate) values (102, '1/1/2019')


insert into @REV (id, trandate) values (102, '2/4/2019')


insert into @REV (id, trandate) values (102, '2/11/2019')


insert into @REV (id, trandate) values (103, '2/17/2019')


insert into @REV (id, trandate) values (103, '2/25/2019')


insert into @REV (id, trandate) values (103, '3/24/2019')


insert into @REV (id, trandate) values (103, '3/30/2019')



declare @COST table (id int, pcr varchar(20), cost float)



insert into @COST (id, pcr, cost) values (101, 'Jan-18', 16.8)


insert into @COST (id, pcr, cost) values (101, 'May-18', 7.6)


insert into @COST (id, pcr, cost) values (101, 'Jun-18', 14.3)


insert into @COST (id, pcr, cost) values (101, 'Jul-18', 301.88)


insert into @COST (id, pcr, cost) values (101, 'Aug-18', 6.62)


insert into @COST (id, pcr, cost) values (101, 'Nov-18', 0.01)


insert into @COST (id, pcr, cost) values (102, 'Dec-18', 6.62)


insert into @COST (id, pcr, cost) values (102, 'May-18', 47.95)


insert into @COST (id, pcr, cost) values (102, 'Jun-18', 79.8)


insert into @COST (id, pcr, cost) values (102, 'Jul-18', 3.49)


insert into @COST (id, pcr, cost) values (102, 'Jan-19', 30.11)


insert into @COST (id, pcr, cost) values (103, 'Mar-19', 102.11)



select r.id, r.trandate, (


 select top 1 c.cost from @cost c


 where c.id = r.id


 and datediff(day, CAST('01-' + c.pcr AS datetime), r.trandate) >= 0


 order by CAST('01-' + c.pcr AS datetime) desc


) as cost


from @rev r



结果:

101 2018-03-05 16.8

101 2018-03-24 16.8

101 2018-04-10 16.8

101 2018-05-30 7.6

101 2018-06-25 14.3

102 2019-01-01 30.11

102 2019-02-04 30.11

102 2019-02-11 30.11

103 2019-02-17 NULL

103 2019-02-25 NULL

103 2019-03-24 102.11

103 2019-03-30 102.11


SELECT A.PRODUCT_ID


 ,A.TRANSACTION_DATE


 ,(


 SELECT TOP 1 X.COST


 FROM COST X


 WHERE X.PRODUCT_ID = A.PRODUCT_ID


 AND X.PCR_PERIOD < = A.TRANSACTION_DATE


 AND X.PCR_PERIOD > DATEADD(MONTH,-6,A.TRANSACTION_DATE)


 ORDER BY X.PCR_PERIOD DESC


 ) AS COST


FROM REV A



使用Outer Apply编写,并假定PCR周期为日期:


SELECT REV.[Product ID], REV.[Transaction Date], LastCost.[Cost]


FROM REV


OUTER APPLY


(


SELECT TOP 1 Cost.Cost


FROM Cost


WHERE Cost.[Product ID]= REV.[Product ID]


AND Cost.[PCR Period] BETWEEN dateadd(month,-6,REV.[Transaction Date]) and REV.[Transaction Date]


ORDER BY Cost.[PCR Period] DESC


) AS LastCost



...