How to get record by Date in my case using SQL Server?-CodePudding

I have 2 tables ProductLog and Product

ProductLog

ProductID    TransDate    RegNo
--------------------------------
15           2022-03-05    4
15           2022-03-08    1
15           2022-03-10    3

Product

Date          ProductID
-----------------------
2022-03-01    15
2022-03-02    15
2022-03-03    15
2022-03-04    15
2022-03-05    15
2022-03-06    15
2022-03-07    15
2022-03-08    15
2022-03-09    15
2022-03-10    15
2022-03-11    15
2022-03-12    15

The select statement RegNo value to be ProductLog.RegNo , which is Product.Date <= ProductLog.TransDate

I want to get a result like below Expected Output

Date          ProductID   RegNo
-------------------------------
2022-03-01    15          4
2022-03-02    15          4
2022-03-03    15          4
2022-03-04    15          4
2022-03-05    15          4
2022-03-06    15          1
2022-03-07    15          1
2022-03-08    15          1
2022-03-09    15          3
2022-03-10    15          3
2022-03-11    15          0
2022-03-12    15          0

I tried by ranking order Date by ASC, and where rank =1, it set all to lowest date value. (I understand it is not a correct approach.)

what is the correct way to approach this?

CodePudding user response：

use APPLY to find the RegNo

select p.Date, p.ProductID, RegNo = coalesce(l.RegNo, 0)
from   Product p
       outer apply
       (
           select top 1 l.RegNo
           from   ProductLog l
           where  l.ProductID = p.ProductID
           and    l.TransDate >= p.[Date]
           order by l.TransDate
       ) l

CodePudding user response：

use join as follows:

select 
  b.Date, 
  b.ProductID, 
  iif(RegNo is null, 0, RegNo) 
from 
  Product b 
  full join ProductLog a on a.ProductID = b.ProductID 
  and b.Date <= a.TransDate