Trying to avoid cursor - 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)

Trying to avoid cursor

Author

Topic

sql_wedge
Starting Member

18 Posts

Posted - 2005-08-23 : 12:39:26

Hi

Below is a simplification of a table I am working with.

Company Payment Payment_date
1 1 01/11/2004 00:00
1 2 01/11/2004 00:00
1 3 01/11/2004 00:00
1 4 01/11/2004 00:00
1 5 01/11/2004 00:00
2 1 01/11/2004 00:00
2 2 01/11/2004 00:00
2 3 01/11/2004 00:00
2 4 01/11/2004 00:00
3 1 01/11/2004 00:00
3 2 01/11/2004 00:00
3 3 01/11/2004 00:00

I need to be able to select the second from last payment for evry company and produce that record so that the out put is as follows

Company Payment Payment_date
1 4 01/11/2004 00:00
2 3 01/11/2004 00:00
3 2 01/11/2004 00:00

I hope i have been clear

Thanks in advance
SQL_WEDGE

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-23 : 14:50:12

SELECT 
	mt.Company, 
	mt.Payment, 
	mt.PaymentDate, 
FROM 
	dbo.MyTable AS mt
	JOIN 
	(
		SELECT 
			mt.Company, 
			MAX(mt.Payment)-1 AS Payment 
		FROM 
			dbo.MyTable AS mt
		GROUP BY 
			mt.Company
	) AS mt2
		ON mt.Company = mt2.Company
			AND mt.Payment = mt2.Payment

Mark

magesh
Starting Member

23 Posts

Posted - 2005-08-24 : 02:33:29

Hi All,

what will happen when one company has only one payment date and payment
i think Mark's query wont display that record...

any way i tried out with the below query and it is working fine but not an efficient query :)
(hope this forum experts will optimise the query to there extent)

SELECT a.Company,a.Payment,a.PaymentDate FROM MyTable a,
(SELECT COALESCE(b.Company,c.Company) AS Company,
COALESCE(MAX(b.Payment),c.Payment) AS Payment
FROM MyTable b
RIGHT OUTER JOIN
(SELECT Company,MAX(Payment) AS Payment FROM MyTable GROUP BY Company ) c
ON b.Company=c.Company AND b.Payment<c.Payment
GROUP BY b.Company,c.Company,c.Payment) d
WHERE a.Company = d.Company AND a.Payment =d.Payment
ORDER BY a.Company

DDL For this table :

CREATE TABLE [MyTable] (
[Company] [int] NOT NULL ,
[Payment] [int] NOT NULL ,
[PaymentDate] [datetime] NULL ,
CONSTRAINT [PK_MyTable_KEY] PRIMARY KEY CLUSTERED
(
[Company],
[Payment]
) ON [PRIMARY]
) ON [PRIMARY]
GO

DML for this table :

----START
INSERT INTO MyTable VALUES (1,1,'2004-01-01 00:00:00.000')
INSERT INTO MyTable VALUES (1,2,'2004-01-02 00:00:00.000')
INSERT INTO MyTable VALUES (1,3,'2004-01-03 00:00:00.000')
INSERT INTO MyTable VALUES (1,4,'2004-01-04 00:00:00.000')
INSERT INTO MyTable VALUES (1,5,'2004-01-05 00:00:00.000')
INSERT INTO MyTable VALUES (1,6,'2004-01-06 00:00:00.000')

INSERT INTO MyTable VALUES (2,1,'2004-02-01 00:00:00.000')
INSERT INTO MyTable VALUES (2,2,'2004-02-02 00:00:00.000')
INSERT INTO MyTable VALUES (2,3,'2004-02-03 00:00:00.000')
INSERT INTO MyTable VALUES (2,4,'2004-02-04 00:00:00.000')

INSERT INTO MyTable VALUES (3,1,'2004-03-01 00:00:00.000')
INSERT INTO MyTable VALUES (3,2,'2004-03-02 00:00:00.000')
INSERT INTO MyTable VALUES (3,3,'2004-03-03 00:00:00.000')
INSERT INTO MyTable VALUES (3,4,'2004-03-04 00:00:00.000')
INSERT INTO MyTable VALUES (3,5,'2004-03-05 00:00:00.000')
INSERT INTO MyTable VALUES (3,6,'2004-03-06 00:00:00.000')
INSERT INTO MyTable VALUES (3,7,'2004-03-07 00:00:00.000')
INSERT INTO MyTable VALUES (3,8,'2004-03-08 00:00:00.000')

INSERT INTO MyTable VALUES (4,1,'2004-04-01 00:00:00.000')
INSERT INTO MyTable VALUES (4,2,'2004-04-02 00:00:00.000')

INSERT INTO MyTable VALUES (5,1,'2004-05-01 00:00:00.000')
INSERT INTO MyTable VALUES (5,2,'2004-05-02 00:00:00.000')
INSERT INTO MyTable VALUES (5,3,'2004-05-03 00:00:00.000')
INSERT INTO MyTable VALUES (5,4,'2004-05-04 00:00:00.000')

INSERT INTO MyTable VALUES (6,1,'2004-06-01 00:00:00.000')

INSERT INTO MyTable VALUES (7,1,'2004-07-01 00:00:00.000')

---- END

Magesh

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-24 : 04:20:05

quote:
Originally posted by magesh

Hi All,

what will happen when one company has only one payment date and payment
i think Mark's query wont display that record...

Hi Magesh,
I was assuming this was the desired behaviour as, in such cases, there is no 'second to last payment'.

Mark

sql_wedge
Starting Member

18 Posts

Posted - 2005-08-24 : 12:21:55

Thank you both -- exactly what I needed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources