July 4, 2012 at 6:42 am
Hi Forumer's
I have a query that required to join 3 tables.
objective:
1. I need to pullout all records from OpenPO table and check the itemid and ponumber if exist from purchase.
2. if exist the records in purchase table i would like to create a column for RPOrderQty and RPOpenQty.
3.Join tthe purchtable by PONumber and get the vendreference and place it into POReferencenum. this means no PO yet created.
I could not figure out the exact result w/c should be like my sample result below.
should be like my sample below.
THank you in advance and your reply is very much appreaciated.
Data Structure:
Create table #OPENPO (Itemid nvarchar(20), POReferenceNum nvarchar(35), PONumber nvarchar(35), USQtyOrder int, USQtyOpen int) INSERT #OPENPO VALUES
('LGE1879','PO00008028','PH0057842',600,600), ('LGE1879','PO00007908','PH0057727',400,400)
Create table #Purchase (Itemid nvarchar(20), POReferenceNum nvarchar(35), PONumber nvarchar(35), RPQtyOrder int, RPQtyOpen int) INSERT #Purchase VALUES
('LGE1879','PO00008028','PH0057842',600,600), ('LGE1879','PO00007908','PH0058227',400,400), ('LGE1879','PO00007908','PH0057727',400,400)
Create table #Purchtable (PONumber nvarchar(35), vendref nvarchar50) INSERT #Purchtable VALUES ('PH0057842','Aug FC (Marquee)'), ('PH0058227','Aug SB (LS855)'),
('PH0057727','LS855 Sept FC')
Sample Result:
Itemid----POreferenceNum--USPOnumber--USQtyOrder--USQtyOpen--RPPONumber---RPQtyORDER---RPQTYOpen
------------------------------------------------------------------------------------------------
LGE1879--PO00008028-----PH0057842----600--------600----------PH0057842----600----------600
LGE1879--PO00007908-----PH0057727----400--------400----------PH0057727----400----------400
LGE1879--LS855 Sept FC------------------------------------------PH0058227----400----------400
July 4, 2012 at 6:47 am
What have you tried so far in getting your result?
July 4, 2012 at 8:40 am
Post the query you're working in and we'll take a look at it and advise.
Mark
July 4, 2012 at 11:11 pm
Here is my Query and it's already working. Thank you guys for the reply.
SELECT
p.itemid
,COALESCE(o.POReferencenum,u.vendref) AS POReferencenum
,p.PONumber AS UPSONumber
,o.USQtyOrder
,o.USQtyOpen
,p.PONumber AS RPPONumber
, p.RPQtyOrder
, p.RPQtyOpen
FROM #Purchase p
JOIN #Purchtable u ON
p.PONumber=u.ponumber
LEFT JOIN #OpenPO o ON
p.itemid=o.itemid
and p.ponumber=o.poNumber
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply