June 6, 2012 at 9:22 am
The following script is returning all PO's within the last eighteen months. How do I filter it down to only return the most recent PO within the last 18 months? Any help will be greatly appreciated.
SELECT v.ACCTNO
,v.NAME
,vs.TERMS
,po.ENTER_DATE
,po.ID
FROM VENDOR v
LEFT JOIN VEND_SET vs on v.ACCTNO = vs.ACCTNO
LEFT JOIN PURCH_ORDER po on v.ACCTNO = po.ACCTNO
WHERE po.ENTER_DATE>= DATEADD(mm, DATEDIFF(mm,0,getdate())-18, 0)
AND po.ENTER_DATE< DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
ORDER BY v.ACCTNO ASC
June 6, 2012 at 9:27 am
Not quite sure what you are asking for here. Are you asking for the most recent PO regardless of account number or are you looking for the most recent for each account no?
June 6, 2012 at 9:33 am
If you are asking the latter, then give this a try. It isn't tested as I have nothing to test it against.
WITH CurrentPO AS (
SELECT
v.ACCTNO,
v.NAME,
vs.TERMS,
po.ENTER_DATE,
po.ID,
ROW_NUMBER() OVER (PARTITION BY v.ACCTNO ORDER BY po.EnterDate DESC) rn
FROM
dbo.VENDOR v
LEFT JOIN dbo.VEND_SET vs
on v.ACCTNO = vs.ACCTNO
LEFT JOIN dbo.PURCH_ORDER po
on v.ACCTNO = po.ACCTNO
WHERE
po.ENTER_DATE >= DATEADD(mm, DATEDIFF(mm,0,getdate())-18, 0)
AND po.ENTER_DATE < DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
)
SELECT
cpo.ACCTNO,
cpo.NAME,
cpo.TERMS,
cpo.ENTER_DATE,
cpo.ID
FROM
CurrentPO cpo
WHERE
cpo.rn = 1
ORDER BY
v.ACCTNO ASC;
June 6, 2012 at 9:37 am
If you want the most recent records, simply change the ORDER BY
SELECT v.ACCTNO
,v.NAME
,vs.TERMS
,po.ENTER_DATE
,po.ID
FROM VENDOR v
LEFT JOIN VEND_SET vs on v.ACCTNO = vs.ACCTNO
LEFT JOIN PURCH_ORDER po on v.ACCTNO = po.ACCTNO
WHERE po.ENTER_DATE>= DATEADD(mm, DATEDIFF(mm,0,getdate())-18, 0)
AND po.ENTER_DATE< DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
ORDER BY po.ENTER_DATE DESC
In add, if you only need a certain number of records, add TOP n to the SELECT clause.
SELECT TOP 5 v.ACCTNO
,v.NAME
,vs.TERMS
,po.ENTER_DATE
,po.ID
FROM VENDOR v
LEFT JOIN VEND_SET vs on v.ACCTNO = vs.ACCTNO
LEFT JOIN PURCH_ORDER po on v.ACCTNO = po.ACCTNO
WHERE po.ENTER_DATE>= DATEADD(mm, DATEDIFF(mm,0,getdate())-18, 0)
AND po.ENTER_DATE< DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
ORDER BY po.ENTER_DATE DESC
Best regards!
June 6, 2012 at 9:50 am
I was refferring to the latter and your code worked great with the exception of the Order By clause, which I can live without. Returned an multi-part identifier error.
What type of query is this? CTE?
I appreciate your help very much.
Thanks
June 6, 2012 at 9:52 am
bpowers (6/6/2012)
I was refferring to the latter and your code worked great with the exception of the Order By clause, which I can live without. Returned an multi-part identifier error.What type of query is this? CTE?
I appreciate your help very much.
Thanks
Forgot to change the V to cpo in the order by. Yes, I used a CTE to add the row_number function so I could select the most current PO for each account.
June 6, 2012 at 9:55 am
Worked great. Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply