January 17, 2011 at 5:27 am
Hey.
So for this odbc spreedsheet im making here is the columns they want:
Date, Open PO, Vendor, JO Assigned, PO w/o ship to Us , Dollar Ammount.
PO = Purchase Order
JO = Job Order
The one in bold, PO w/o ship to Us , is the one I had to make a seperate query for because I could not use a subquery:
qNoShipTo
SELECT fpono
FROM dbo.pomast
WHERE (fstatus = 'Open') AND (fcshipto <> 'SELF') OR
(fstatus = 'Released')
Seems simple enough. When the status of the Purchase Order is 'Open' or 'Released' and fcship to <> SELF. Now, I need to combine it with this query:
SELECT dbo.pomast.forddate AS [Open Date], dbo.pomast.fpono AS [Open PO], dbo.pomast.fcompany AS Vendor,
dbo.jomast.fjobno AS [JO Assigned], SUM(dbo.poitem.fucost * dbo.poitem.fordqty) AS [Dollar Amount]
FROM dbo.pomast INNER JOIN
dbo.poitem ON dbo.pomast.fpono = dbo.poitem.fpono INNER JOIN
dbo.jomast ON dbo.poitem.fpartno = dbo.jomast.fpartno AND dbo.poitem.frev = dbo.jomast.fpartrev LEFT OUTER JOIN
dbo.jodbom ON dbo.jomast.fpartno = dbo.jodbom.fbompart AND dbo.jomast.fpartrev = dbo.jodbom.fbomrev
WHERE (dbo.pomast.fstatus = 'OPEN')
GROUP BY dbo.pomast.fpono, dbo.pomast.fcompany, dbo.jomast.fjobno, dbo.pomast.forddate
HAVING (dbo.pomast.forddate > CONVERT(DATETIME, '2010-01-14 00:00:00', 102))
ORDER BY dbo.pomast.forddate DESC
I am having trouble doing so. I tried using a subquery but it say's I can with a groub by clause. I tried using 2 queries and combinging them by their 'key' (fpono) but it yields no results... any advice/tips? Thanks 🙂
January 18, 2011 at 3:51 am
Can u provide sample table relevant to ur problem?.. so that it wil be easy to help out
January 18, 2011 at 5:05 am
I appreciate the help. I talked with the lady that knows SQL here. I didnt even need to add the JOB side of the project. Just the PO side. Here is the completed code if you're interested:
SELECT dbo.pomast.forddate AS [Open Date], dbo.pomast.fpono AS [Open PO], dbo.pomast.fcompany AS Vendor,
dbo.pomast.fcshcompan AS [PO shipped to], dbo.poitem.fjokey AS [JO Assigned], SUM(dbo.poitem.fucost * dbo.poitem.fordqty) AS [Dollar Amount]
FROM dbo.pomast INNER JOIN
dbo.poitem ON dbo.pomast.fpono = dbo.poitem.fpono
WHERE (dbo.pomast.fstatus = 'OPEN')
GROUP BY dbo.pomast.fpono, dbo.pomast.fcompany, dbo.pomast.forddate, dbo.poitem.fjokey, dbo.pomast.fcshcompan, dbo.pomast.fcshipto
HAVING (dbo.pomast.fcshipto <> 'SELF')
ORDER BY dbo.pomast.forddate DESC
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply