June 8, 2007 at 8:00 am
Hi
I need to show items which are not invoiced more then 60days. so i have two tables one is invoice table and other is stock table
Query For Invoice table
Select item, Max(Invoicedate) as LastInvoicedate from Invoicedet
group by item.
Stock table
Select item,Backorders,Allocated,Physical, PurchaseOrders from Stock
Where Physical>0
In stock table i have some items which are not invoiced on system but we have in stock. So i want to run a query which will give me items which are invoiced>60 days and neverinvoiced on system and want to extract items which invoiced within 60 days.
Regards
June 8, 2007 at 8:13 am
Is something like this you are looking for?
SELECT S.item, MAX(Invoicedate)
FROM Stock AS S
LEFT OUTER JOIN Invoicedet AS I ON I.item = S.item
GROUP BY S.item
HAVING ISNULL(DATEDIFF(day, MAX(Invoicedate), getdate()), 61) > 60
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply