Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 RESOLVED -- Max Date and less than ( < ) together

Author  Topic 

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-11-09 : 19:49:19
Hi.

I am trying to get the max payment date (the last time the vendor was paid) - the end result of which is that I really only want those vendors whose max payment date is less than 06/30/06 because I will be updating these vendors to be inactive. Please note that the code below is only trying to find these vendors - it is not an update statement yet.


My expected results would be:
Vend Name VendCode LastPymtDate
-------------------------------
Vendor A VendA 02/04/03
Vendor B VendB 03/17/03
Vendor C VendC 04/04/04
Vendor D VendD 05/13/05
Vendor E VendE 05/14/05
Vendor F VendF 06/30/06


My problem is that no matter how I word my query I seem to be getting the vast majority of the payments (100s of 1000s) rather than the 4200 +/- vendors I should be getting.

I have spent a good chunk of my day searching google for examples and trying these examples and have failed. My most recent failure is:

select distinct
PMM_TEST.dbo.VEND_REMIT.NAME AS RemitVendorName,
PMM_TEST.dbo.VEND_REMIT.VEND_NO AS RemitCode,
PFM_TEST.dbo.PYMT.DOC_DATE AS 'Last Payment Date'

from PMM_TEST.dbo.VEND_REMIT
join PMM_TEST.dbo.ADDR
on PMM_TEST.dbo.VEND_REMIT.ADDR_ID = PMM_TEST.dbo.ADDR.ADDR_ID
join PMM_TEST.dbo.VEND_TERMS
on PMM_TEST.dbo.VEND_REMIT.VEND_TERMS_ID = PMM_TEST.dbo.VEND_TERMS.VEND_TERMS_ID
join PFM_TEST.dbo.PYMT
on PFM_TEST.dbo.PYMT.VEND_CODE = PMM_TEST.dbo.VEND_REMIT.VEND_NO

where PFM_TEST.dbo.PYMT.DOC_DATE IN
(select MAX(PFM_TEST.dbo.PYMT.DOC_DATE)
FROM PFM_TEST.dbo.PYMT
where PFM_TEST.dbo.PYMT.DOC_DATE < '06/30/2006'

group by PFM_TEST.dbo.PYMT.DOC_DATE,
PMM_TEST.dbo.VEND_REMIT.NAME,
PMM_TEST.dbo.VEND_REMIT.VEND_NO,
PFM_TEST.dbo.PYMT.DOC_DATE)

order by PMM_TEST.dbo.VEND_REMIT.NAME


Any help anyone could provide would be greatly appreciate.

Regards,
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-09 : 20:16:14
[code]
select
RemitVendorName,
RemitCode,
[Last Payment Date]
from
(
select
PMM_TEST.dbo.VEND_REMIT.NAME AS RemitVendorName,
PMM_TEST.dbo.VEND_REMIT.VEND_NO AS RemitCode,
MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'Last Payment Date'

from
PMM_TEST.dbo.VEND_REMIT
join PMM_TEST.dbo.ADDR
on PMM_TEST.dbo.VEND_REMIT.ADDR_ID = PMM_TEST.dbo.ADDR.ADDR_ID
join PMM_TEST.dbo.VEND_TERMS
on PMM_TEST.dbo.VEND_REMIT.VEND_TERMS_ID = PMM_TEST.dbo.VEND_TERMS.VEND_TERMS_ID
join PFM_TEST.dbo.PYMT
on PFM_TEST.dbo.PYMT.VEND_CODE = PMM_TEST.dbo.VEND_REMIT.VEND_NO
group by
PMM_TEST.dbo.VEND_REMIT.NAME,
PMM_TEST.dbo.VEND_REMIT.VEND_NO
) AS p
where
[Last Payment Date] < '20060630'
order by
RemitVendorName
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-11-10 : 14:18:36
Khtan:

Thank you very much for your help! This worked perfectly. I've been reviewing the code changes you made and it has been a great learning tool for me to see what you did to make this work. Thanks again.

Regards,
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA
Go to Top of Page
   

- Advertisement -