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