Help - SQL Server Forums

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)

Help

Author

Topic

temp2escape
Starting Member

6 Posts

Posted - 2007-09-08 : 02:50:10

Hi. table name is tblpayment

RegNo PaymentDate
12 02/12/07
12 12/12/07
12 03/05/06
15 12/12/05
15 12/07/07
15 01/31/07
17 01/01/06
17 06/09/05

The ouput should be like this:

RegNo PaymentDate
12 03/05/06
15 12/12/05
17 01/01/06

As you can see. It only selects one RegNo followed with its latest PaymentDate excluding PaymentDate with the year 2007.

I tried this:
SELECT *
FROM tblPayment
WHERE (YEAR(PaymentDate) <> 2007) IN
(SELECT TOP 1 *
FROM tblPayment
GROUP BY RegNo
HAVING COUNT(*) > 1)
ORDER BY PaymentDate DESC

But its not working. Any idea?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-08 : 04:05:49

Try

Select Regno, max(paymentdate) as paymentdate from table
WHERE YEAR(PaymentDate) <> 2007

Madhivanan

Failing to plan is Planning to fail

temp2escape
Starting Member

6 Posts

Posted - 2007-09-08 : 04:14:03

nope. not working.

show this error.

column 'tblpayment.regno' is invalid in the select list because it is not contained in an aggregate function and there is no group by clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-08 : 04:16:36

Well. I missed group by. Use this

Select Regno, max(paymentdate) as paymentdate from table
WHERE YEAR(PaymentDate) <> 2007
group by Regno

Madhivanan

Failing to plan is Planning to fail

temp2escape
Starting Member

6 Posts

Posted - 2007-09-08 : 09:26:50

thanks dude. It works. Highly appreciated.

temp2escape
Starting Member

6 Posts

Posted - 2007-09-08 : 10:49:03

Erm... there is one problem left madhivanan.

How the exclude entirely those who made payment in the year 2007?

For example:

RegNo PaymentDate
12 02/12/07
12 12/12/07
12 03/05/06

From the SQL command u gave me... it still shows the payment made for the above RegNo (for year 2006). I mean, since RegNo=12 already made the payment for the year 2007. So, he shouldnt be on the list.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-08 : 11:00:40

Try this

Select Regno, max(paymentdate) as paymentdate from table t
WHERE not exists(Select * from table where regno=T.regno and YEAR(PaymentDate) =2007)
group by Regno

Madhivanan

Failing to plan is Planning to fail

temp2escape
Starting Member

6 Posts

Posted - 2007-09-08 : 11:37:43

nope... return no record at all.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-08 : 12:32:06

quote:
Originally posted by temp2escape

nope... return no record at all.

Why?


declare @t table(RegNo int, PaymentDate datetime)
insert into @t
select 12, '02/12/07' union all
select 12, '12/12/07' union all
select 12, '03/05/06' union all
select 15, '12/12/05' union all
select 15, '12/07/07' union all
select 15, '01/31/07' union all
select 17, '01/01/06' union all
select 17, '06/09/05'

Select Regno, max(paymentdate) as paymentdate from @t t
WHERE not exists(Select * from @t where regno=T.regno and YEAR(PaymentDate) =2007)
group by Regno

Madhivanan

Failing to plan is Planning to fail

temp2escape
Starting Member

6 Posts

Posted - 2007-09-08 : 18:46:22

I dont know. I'm not familiar with the EXIST or NOT EXIST syntax.

It didnt show any result.

The code should only take out single RegNo followed with their latest payment. But if the RegNo already make the payment for year 2007, it should be exclude entirely from the output.

The output should be (based on the table from my 1st post):

RegNo PaymentDate
17 01/01/06

Because RegNo 12 and 15 already make payment for 2007.

This is the SQL command you gave me.

SELECT RegNo, MAX(PaymentDate) AS paymentdate
FROM tblPayment
WHERE (NOT EXISTS
(SELECT *
FROM tblPayment
WHERE regno = tblpayment.regno AND YEAR(PaymentDate) = 2007))
GROUP BY RegNo

Correct me if I am wrong.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-10 : 06:40:45

quote:
Originally posted by temp2escape

I dont know. I'm not familiar with the EXIST or NOT EXIST syntax.

It didnt show any result.

The code should only take out single RegNo followed with their latest payment. But if the RegNo already make the payment for year 2007, it should be exclude entirely from the output.

The output should be (based on the table from my 1st post):

RegNo PaymentDate
17 01/01/06

Because RegNo 12 and 15 already make payment for 2007.

This is the SQL command you gave me.

SELECT RegNo, MAX(PaymentDate) AS paymentdate
FROM tblPayment
WHERE (NOT EXISTS
(SELECT *
FROM tblPayment
WHERE regno = tblpayment.regno AND YEAR(PaymentDate) = 2007))
GROUP BY RegNo

Correct me if I am wrong.

It is becuase you didnt use the alias as I used in my query

Run this

SELECT RegNo, MAX(PaymentDate) AS paymentdate
FROM tblPayment t
WHERE (NOT EXISTS
(SELECT *
FROM tblPayment
WHERE regno = t.regno AND YEAR(PaymentDate) = 2007))
GROUP BY RegNo

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:48:36

And if you wish to utilize any present index


SELECT		t.RegNo,
		MAX(t.PaymentDate) AS PaymentDate
FROM		@t AS t
LEFT JOIN	(
			SELECT	RegNo
			FROM	@t
			WHERE	PaymentDate >= '20070101'
				AND PaymentDate < '20080101'
		) AS e ON e.RegNo = t.RegNo
WHERE		e.RegNo IS NULL
GROUP BY	t.RegNo
ORDER BY	t.RegNo

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources