RESOLVED -- Max Date and less than ( < ) together

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources