Finding closest expiration date

I have a table with columns ItemCode , ItemCount, ExpiredDate in which the expired date of items are saved when stocks of items increase. I have a View which shows current stocks of my items and it's columns are ItemCode and ItemStock.

Table

ItemCode, ItemCount, ExpiredDate
1001    , 200      , 2010/01/01
1001    , 100      , 2020/02/01
1001    , 200      , 2021/03/01
1002    , 150      , 2020/03/01

View

ItemCode, ItemStock
1001    , 250
1002    , 40
1003    , 50
1004    , 60

I want a query that returns closest expired date according to items stock.

Result

ItemCode, ClosestExpirationDate
1001    , 2020/02/01
1002    , 2020/03/01
1003    , -----
1004    , -----

4 answers

  • answered 2018-07-11 05:58 Tim Biegeleisen

    Try using the absolute difference of dates:

    WITH cte AS (
        SELECT t1.ItemCode, t2.ExpiredDate,
            ROW_NUMBER() OVER (PARTITION BY t1.ItemCode
                ORDER BY ABS(DATEDIFF(day, GETDATE(), COALESCE(t2.ExpiredDate, GETDATE())))) rn
        FROM [yourView] t1
        LEFT JOIN [yourTable] t2
            ON t1.ItemCode = t2.ItemCode
    )
    
    SELECT ItemCode, ExpiredDate AS ClosestExpirationDate
    FROM cte
    WHERE rn = 1
    ORDER BY ItemCode;
    

    enter image description here

    Demo

    Note: I assumed you want expiry dates regardless of whether they occur in the past or future. If you only want future expiry dates, then the above query can be slightly modified.

  • answered 2018-07-11 06:07 Morzel

    Unfortunately, I cannot provide the exact query for you but I can tell you how I would solve that puzzle:

    I cannot validate that right now but the solution is something like this:

    ;with numberedItems as (
       select ItemCode, ExpirationDate,
       row_number() over(partition by ItemCode order by ExpirationDate) as RowNo
       from Items 
    )
    select ItemCode, ExpirationDate
    from numberedItems
    where RowNo = 1
    

    The benefit of this solution is the SQL server will read your table only once, you don't have to do two queries to get the single resultset.

    I hope it helps.

  • answered 2018-07-11 06:23 Michał Turczyn

    Try this:

    Sample data:

    declare @tbl1 table (ItemCode int, ItemCount int, ExpiredDate date);
    insert into @tbl1 values
    (1001, 200, '2010/01/01'),
    (1001, 100, '2020/02/01'),
    (1001, 200, '2021/03/01'),
    (1002, 150, '2020/03/01');
    
    declare @tbl2 table (ItemCode int, ItemStock int);
    insert into @tbl2 values
    (1001, 250),
    (1002, 40),
    (1003, 50),
    (1004, 60);
    

    T-SQL:

    select t2.ItemCode, min(t1.ExpiredDate) ClosestExpirationDate from (
        select ItemCode, ItemCount, ExpiredDate,
               SUM(ItemCount) over (partition by ItemCode order by ExpiredDate) CumSum
        from @tbl1
    ) t1 right join @tbl2 t2 on t1.ItemCode = t2.ItemCode and t1.CumSum > ItemStock
    group by t2.ItemCode
    

    For SQL Server versions earlier than 12:

    select t2.ItemCode, min(t1.ExpiredDate) ClosestExpirationDate from (
        select t1.ItemCode, t1.ItemCount, t1.ExpiredDate, SUM(t2.ItemCount) CumSum 
        from @tbl1 t1
        join @tbl1 t2 on t1.ItemCode = t2.ItemCode and t1.ExpiredDate >= t2.ExpiredDate
        group by t1.ItemCode, t1.ItemCount, t1.ExpiredDate
    ) t1 right join @tbl2 t2 on t1.ItemCode = t2.ItemCode and t1.CumSum > ItemStock
    group by t2.ItemCode
    

  • answered 2018-07-11 11:37 Gordon Linoff

    Use outer apply. I think you want the next date in the future:

    select v.*, t.ExpiredDate
    from view v outer apply
         (select top (1) t.*
          from table t
          where t.ExpiredDate > getdate()
          order by t.ExpiredDate desc
         ) t;
    

    If you want to include past dates as well, the structure is very similar:

    select v.*, t.ExpiredDate
    from view v outer apply
         (select top (1) t.*
          from table t
          order by datediff(day, getdate(), t.ExpiredDate) asc
         ) t;