# 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    , -----
``````

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;
``````

## 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.

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.

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
``````

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;
``````