[SOLVED] Select which has matching date or latest date record

Select which has matching date or latest date record

Here are two tables.

ItemInfo

Id  Description
1   First Item  
2   Second Item

ItemInfoHistory

Id  ItemInfoId  Price   StartDate    EndDate
1   1           45      2020-09-01   2020-09-15
2   2           55      2020-09-26   null
3   1           50      2020-09-16   null

Here is SQL query.

SELECT i.Id, Price, StartDate, EndDate 
FROM Itemsinfo i 
LEFT JOIN ItemInfoHistory ih ON i.id= ih.ItemsMasterId AND  CONVERT(DATE, GETDATE()) >= StartDate AND ( CONVERT(DATE, GETDATE()) <= EndDate OR EndDate IS NULL)

Which gives following results, when runs the query on 9/20

Id  Price   StartDate   EndDate
1   50      2020-09-16  NULL
2   NULL    NULL        NULL

For the second item, I want to get latest record from history table, as shown below.

Id  Price   StartDate   EndDate
1   50      2020-09-16  NULL
2   55      2020-09-26  NULL

Thanks in advance.

Solution

Probably the most efficient method is two joins. Assuming the "latest" record has a NULL values for EndDate, then:

SELECT i.Id,
       COALESCE(ih.Price, ih_last.Price) as Price,
       COALESCE(ih.StartDate, ih_last.StartDate) as StartDate,
       COALESCE(ih.EndDate, ih_last.EndDate) as EndDate
FROM Itemsinfo i LEFT JOIN
     ItemInfoHistory ih
     ON i.id = ih.ItemsMasterId AND 
        CONVERT(DATE, GETDATE()) >= StartDate AND
        (CONVERT(DATE, GETDATE()) <= EndDate OR EndDate IS NULL) LEFT JOIN
     ItemInfoHistory ih_last
     ON i.id = ih_last.ItemsMasterId AND 
        ih_last.EndDate IS NULL;

Actually, the middle join doesn't need to check for NULL, so that could be removed.