Help with Query - 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 2005 Forums

Transact-SQL (2005)

Help with Query

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2009-08-01 : 23:44:00

Gurus,
my table structure is like this,
caseid BAN Createddate
for every BAN there can be multiple caseid so that meant caseid will always be unique,and whenever the case is created the entry is done in creaated date and for every subsequesnt call from the customer new case id is generated for that customer against his BAN number(can be customerid) with the dateand time in created date.

Suppose a case was created on 1st july and entry was made in caseid column with some caseid and BAN number,now if the customer again calls 5 times we need to find the min date after creation date(i.e min date after 1 july),if the customer called on 2nd,3rd,4th and 5th, and the case was created on 1st july we need the find the date of second july.(MIN date after the case was created).

Please help
Regards

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-02 : 11:11:18

try this:


select	d.caseid
	,d.BAN
	,d.createdate
from	(
	select caseid
		,BAN
		,Createdate
		,row_number() over (partition by BAN order by Createdate) as rn
	from	<myTable>
	) as d
where d.rn = 2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources