Finding closest expiration date
I have a table with columns
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, ItemCount, ExpiredDate 1001 , 200 , 2010/01/01 1001 , 100 , 2020/02/01 1001 , 200 , 2021/03/01 1002 , 150 , 2020/03/01
ItemCode, ItemStock 1001 , 250 1002 , 40 1003 , 50 1004 , 60
I want a query that returns closest expired date according to items stock.
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;
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:
- you need only your table. In the following lines, I will call that table 'Items'
- numbering your items based on their expiration date with ROW_NUMBER(), PARTITION BY ItemCode ORDER BY ExpirationDate. MSDN: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017.
- put the previous query into a CTE and filter out only the first rows. MSND: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017
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.
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);
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
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;