Sql 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)

Sql Query

Author

Topic

skc40
Starting Member

34 Posts

Posted - 2012-08-10 : 17:39:36

Hello,

Below is my table structure and sample data.

Declare @Table Table (EmployeeID Int,Unique_Num Int,CID int,EffectiveDate DateTime,ExpirationDate DateTime)
Insert Into @Table Values (37,160,2,'2012-01-01','2012-06-30')
Insert Into @Table Values (37,108,3,'2012-01-01','2012-06-30')
Insert Into @Table Values (37,99,4,'2012-01-01','2012-06-30')
Insert Into @Table Values (37,101,5,'2012-01-01',' 2012-06-30')
Insert Into @Table Values (21,682,2,'2012-01-04','2012-03-31')
Insert Into @Table Values (21,690,3,'2012-01-04','2012-03-31')
Insert Into @Table Values (21,681,4,'2012-01-04','2012-03-31')
Insert Into @Table Values (21,683,5,'2012-01-04','2012-03-31')
Insert Into @Table Values (21,682,2,'2012-02-04','2012-12-31')
Insert Into @Table Values (21,690,3,'2012-02-04','2012-12-31')
Insert Into @Table Values (21,681,4,'2012-02-04','2012-12-31')
Insert Into @Table Values (21,683,5,'2012-02-04','2012-12-31')
Insert Into @Table Values (37,457,5,'2012-12-21','2012-12-31')
Insert Into @Table Values (37,449,4,'2012-12-21','2012-12-31')
Insert Into @Table Values (37,455,3,'2012-12-21','2012-12-31')
Insert Into @Table Values (37,450,2,'2012-12-21','2012-12-31')
Insert Into @Table Values (37,492,2,'2012-01-01','2012-02-28')
Insert Into @Table Values (37,204,3,'2012-01-01','2012-02-28')
Insert Into @Table Values (37,721,4,'2012-01-01','2012-02-28')
Insert Into @Table Values (37,100,5,'2012-01-01','2012-02-28')

select employeeID,Unique_Num,CID,effectivedate,expirationdate,
Row_Number() Over (partition by employeeid,CID Order by ExpirationDate Desc) as Row_Num
from @Table
Order by Row_Num,EmployeeID

From above query, need to get results where Row_Num=1

Please help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 17:46:14

just add a where condition


select *
from
(
select employeeID,Unique_Num,CID,effectivedate,expirationdate,
Row_Number() Over (partition by employeeid,CID Order by ExpirationDate Desc) as Row_Num
from @Table
)t
WHERE Row_Num=1
Order by EmployeeID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skc40
Starting Member

34 Posts

Posted - 2012-08-10 : 17:47:50

Got It Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 22:38:14

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources