April 22, 2014 at 10:17 am
I have a table named item that looks like this:
PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014
And a second table named orders that looks like this:
OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated.
I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL
April 22, 2014 at 10:20 am
OUTER APPLY instead of CROSS APPLY.
April 22, 2014 at 2:10 pm
Now I am trying to figure out how to combine these two queries.
SELECT I.PartNumber, I.Description, I.TimeDateCreated as [Date Part Created], OI.TimeDateCreated as [Last Req Date]
FROM Item I
OUTER APPLY (SELECT TOP 1 OI.OrderNo, OI.TimeDateCreated
FROM OrderItem OI
WHERE I.PartNumber=OI.PartNumber ORDER BY OI.TimeDateCreated DESC) OI
WHERE (OI.TimeDateCreated is NULL or OI.TimeDateCreated >= '11/01/2013')
SELECT ii.PartNumber, SUM (ii.QuantityAvailable) as [QuantityAvailable]
from InventoryItems ii
where ii.sitename = 'Warehouse'
group by ii.PartNumber
Now that I have all my parts and the last order/date, I need to see what is available.
April 22, 2014 at 2:26 pm
jennigirl (4/22/2014)
Now I am trying to figure out how to combine these two queries.SELECT I.PartNumber, I.Description, I.TimeDateCreated as [Date Part Created], OI.TimeDateCreated as [Last Req Date]
FROM Item I
OUTER APPLY (SELECT TOP 1 OI.OrderNo, OI.TimeDateCreated
FROM OrderItem OI
WHERE I.PartNumber=OI.PartNumber ORDER BY OI.TimeDateCreated DESC) OI
WHERE (OI.TimeDateCreated is NULL or OI.TimeDateCreated >= '11/01/2013')
SELECT ii.PartNumber, SUM (ii.QuantityAvailable) as [QuantityAvailable]
from InventoryItems ii
where ii.sitename = 'Warehouse'
group by ii.PartNumber
Now that I have all my parts and the last order/date, I need to see what is available.
What do you mean by combine these two queries? What are you wanting as output? Is there a way to link rows from Item to rows in InventoryItems? I would assume PartNumber maybe?!?
Since you didn't provide ddl or sample data this is a shot in the dark....
with Available as
(
SELECT ii.PartNumber, SUM (ii.QuantityAvailable) as [QuantityAvailable]
from InventoryItems ii
where ii.sitename = 'Warehouse'
group by ii.PartNumber
)
SELECT I.PartNumber,
I.Description,
I.TimeDateCreated as [Date Part Created],
OI.TimeDateCreated as [Last Req Date],
a.QuantityAvailable
FROM Item I
OUTER APPLY (SELECT TOP 1 OI.OrderNo, OI.TimeDateCreated
FROM OrderItem OI
WHERE I.PartNumber=OI.PartNumber ORDER BY OI.TimeDateCreated DESC) OI
join Available a on a.PartNumber = i.PartNumber
WHERE (OI.TimeDateCreated is NULL or OI.TimeDateCreated >= '11/01/2013')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2014 at 2:34 pm
This is perfect..thank you so much for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply