SQL Querie with Variable DatDiff by row 1

[RJL1]

OK is been a while but I am in need of some suggestions. I have a new request to get a report for out invetory records that show invetory that is whitin a certain number of day from the expiration date. The problem is thta each family of items has a different days to expoire value

Here is some sample data. This is a value set in each item

Item     Days to Expire
A1001    30
B2001    45
C3001    60

The invetoiry table that looks like this

Item    Expiration Date    Lot     Qty
A1001   1/29/2014          AX321   100
B2001   9/23/2014          BZ943   950
C3001   2/13/2014          CT361   600

I need to query using the days to expire from the itme table so for item A1001 I would need to get get all records with an expiration date (inventory table) with in 30 days. Thne for item B2001 I would need to get all records with an expiration date with in 45 days and for item C3001 the records will need to be with in 60 days of expiring.

Not sure how to get it to use the days to expire per item. My current report uses a single value for all items but we are needing a more accurate report using each items days to expire value.

Anu suggestions are appreciated

Thanks
~RJL

 

[imex]

Try something like this:

select *
from Items as it
inner join Inventory as in
    on in.Item = it.Item
where
    DATEDIFF(DAY, GETDATE(), in.ExpirationDate) <= it.DaysToExpire

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[RJL1]

Thanks for the super fast response. Will try first thing in the a.m.

 

[DaveInIowa]

You may want to consider a LEFT JOIN in case there are any Inventory Item not in Items, then use a default DaysToExpire (I used 0 in the example below).

SELECT *, DATEADD(D, ISNULL(DaysToExpire, 0), ExpirationDate) ReallyExpiresOn
  FROM Inventory
  LEFT
  JOIN Items
    ON Inventory.Item = Items.Item
 WHERE GETDATE() <= DATEADD(D, ISNULL(DaysToExpire, 0), ExpirationDate)

 

[DaveInIowa]

I see I misread your initial specs of needing inventory records within a certain number of days to expiration. My "advice" doesn't hold (except for maybe using the left join).