There are a set of data
ItemCode TransDate Price
LC0998 4/1/2020 5.3
LC0998 6/5/2020 6.7
LC0998 6/28/2020 6.2
LC0998 7/9/2020 7.8
LC9876 4/1/2020 2.3
LC9876 5/7/2020 12.4
LC9876 6/20/2020 11.7
LC9876 7/5/2020 15.65


If the date is 5/25/2020, then take 5.3 LC0998, LC9876 take 12.4
If the date is 7/5/2020, then take 6.2 LC0998, LC9876 take 15.65


How can I change the SQL statements to write?

CodePudding user response:

 
The create table # t (ItemCode varchar (10), TransDate varchar (10), Price a decimal (10, 2)) 

Insert into # t (ItemCode, TransDate, Price) 
Select 'LC0998', '4/1/2020, 5.3 union all 
Select 'LC0998', '6/5/2020, 6.7 union all 
Select 'LC0998', '6/28/2020, 6.2 union all 
Select 'LC0998', '7/9/2020, 7.8 union all 
Select 'LC9876', '4/1/2020, 2.3 union all 
Select 'LC9876', '5/7/2020, 12.4 union all 
Select 'LC9876', '6/20/2020, 11.7 union all 
Select 'LC9876', '7/5/2020, 15.65 


- if the date is 5/25/2020, then take 5.3 LC0998, LC9876 take 12.4 
Declare @ fdate date 
Select @ fdate='5/25/2020' 

The select ItemCode, Price 
The from (select ItemCode, TransDate, Price, rn=row_number () over (partition by ItemCode order by cast (TransDate as date) desc) 
The from # t 
Where cast (TransDate as date) & lt; T=@ fdate) 
Where t.r n=1 

/* 
ItemCode Price 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
LC0998 5.30 
LC9876 12.40 

(2 rows affected) 
*/


- if the date is 7/5/2020, then take 6.2 LC0998, LC9876 take 15.65 
Declare @ fdate date 
Select @ fdate='7/5/2020' 

The select ItemCode, Price 
The from (select ItemCode, TransDate, Price, rn=row_number () over (partition by ItemCode order by cast (TransDate as date) desc) 
The from # t 
Where cast (TransDate as date) & lt; T=@ fdate) 
Where t.r n=1 

/* 
ItemCode Price 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
LC0998 6.20 
LC9876 15.65 

(2 rows affected) 
*/

CodePudding user response:

Thank you for your reply, sorry, I have not describe the problem of complete

Have the following the second group of data

Table PriceList
ItemCode TransDate Price
LC0998 4/1/2020 5.3
LC0998 6/5/2020 6.7
LC0998 6/28/2020 6.2
LC0998 7/9/2020 7.8
LC9876 4/1/2020 2.3
LC9876 5/7/2020 12.4
LC9876 6/20/2020 11.7
LC9876 7/5/2020 15.65

Another table ItemList
ItemCode CreatedDate
LC9876 5/1/2020
LC9876 6/21/2020
LC9876 7/9/2020
LC9876 7/10/2020
LC0998 5/2/2020
LC0998 7/10/2020


Results:
ItemCode CreatedDate Price
LC9876 5/1/2020 2.3
LC9876 6/21/2020 11.7
LC9876 7/9/2020 15.65
LC9876 7/10/2020 15.65
LC0998 5/2/2020 5.3
LC0998 7/10/2020 7.8

Requirements:
According to the ItemList table find PirceList date recent Price value in the table,

CodePudding user response:

 
The create table PriceList (ItemCode varchar (10), TransDate varchar (10), Price a decimal (10, 2)) 

Insert into PriceList (ItemCode, TransDate, Price) 
Select 'LC0998', '4/1/2020, 5.3 union all 
Select 'LC0998', '6/5/2020, 6.7 union all 
Select 'LC0998', '6/28/2020, 6.2 union all 
Select 'LC0998', '7/9/2020, 7.8 union all 
Select 'LC9876', '4/1/2020, 2.3 union all 
Select 'LC9876', '5/7/2020, 12.4 union all 
Select 'LC9876', '6/20/2020, 11.7 union all 
Select 'LC9876', '7/5/2020, 15.65 

The create table ItemList (ItemCode varchar (10), CreatedDate varchar (10)) 

Insert into ItemList (ItemCode, CreatedDate) 
Select 'LC9876', '5/1/2020' union all 
Select 'LC9876', '6/21/2020' union all 
Select 'LC9876', '7/9/2020' union all 
Select 'LC9876', '7/10/2020' union all 
Select 'LC0998', '5/2/2020' union all 
Select 'LC0998', '7/10/2020' 


The select Anderson temCode, a.C reatedDate, Price=t.P rice 
The from ItemList a 
The outer apply 
(select top 1 x.P rice 
The from PriceList x 
Where cast (x.T ransDate as date) & lt;=cast (a.C reatedDate as date) 
And x.I temCode=Anderson temCode 
The order by cast (x.T ransDate as date) desc) t 

/* 
ItemCode CreatedDate Price 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
LC9876 5/1/2020 2.30 
LC9876 6/21/2020 11.70 
LC9876 7/9/2020 15.65 
LC9876 7/10/2020 15.65 
LC0998 5/2/2020 5.30 
LC0998 7/10/2020 7.80 

(6 rows affected) 
*/