Comparing Rows in same table - 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)

Comparing Rows in same table

Author

Topic

BalakrishnanShanmugham
Starting Member

6 Posts

Posted - 2014-10-24 : 02:51:44

I have a table with 4 columns.

EmpName PayDate WageType PayRate
ABC 10/22/2014 Regular 10.20
XYZ 10/22/2014 Regular 09.30
ABC 10/23/2014 Regular 10.20
ABC 10/24/2014 Regular 11.50
ABC 10/25/2014 Regular 11.20
XYZ 10/25/2014 Regular 09.30
ABC 10/26/2014 Regular 10.20
XYZ 10/26/2014 Regular 09.30

The output will be as below
EmpName PayDate WageType PayRate
ABC 10/22/2014 Regular 10.20
XYZ 10/22/2014 Regular 09.30
ABC 10/24/2014 Regular 11.50
ABC 10/26/2014 Regular 10.20

I have to write query to fetch the data whenever Payrate is changing for particular employee. Distinct can'y be used in this scenario.
Here for the Employee ABC, Initially payrate is 10.20 then change to 11.50 and again change to 10.20. So three rows need to be shown.
But for Employee XYZ initially payrate is 09.30 and not changing for further paydates. So XYZ shown only once.

Could You please suggest me how to write query to get this result..

Regards,
Bala

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-10-25 : 00:16:44

What you need is Row_Number partition

CREATE TABLE #temp(EmpName VARCHAR(50),PayDate VARCHAR(MAX),WageType VARCHAR(100),PayRate decimal(5,2))

insert into #temp
SELECT 'ABC','10/22/2014','Regular',10.20
UNION ALL
SELECT 'XYZ','10/22/2014','Regular',09.30
UNION ALL
SELECT 'ABC','10/23/2014','Regular',10.20
UNION ALL
SELECT 'ABC','10/24/2014','Regular',11.50
UNION ALL
SELECT 'ABC','10/25/2014','Regular',11.20
UNION ALL
SELECT 'XYZ','10/25/2014','Regular',09.30
UNION ALL
SELECT 'ABC','10/26/2014','Regular',10.20
UNION ALL
SELECT 'XYZ','10/26/2014','Regular',09.30

select EmpName
,PayDate
,WageType
,PayRate
FROM
(
SELECT *,ROW_NUMBER () over (partition by EmpName,payrate order by (select 1)) as rn FROM #temp
)
as t where Rn=1 order by PayDate

DROP TABLE #temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources