October 24, 2014 at 1:22 am
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
October 24, 2014 at 1:57 am
can you share what you have tested and why distinct cannot be used ?
a simple group by on the 4 columns will also work and may even use available indexes
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 24, 2014 at 2:04 am
Hi,
If I use Distinct it fetches all data due to Unique Paydate. Because payrate change in some of Paydates but not in all PayDates.
Same thing happen for Group by..
Due to Paydate I can't use distinct and Group by..
Thanks
October 24, 2014 at 5:39 am
Since the date is in your result set, which date to you want it to have ?
Select EmpName, min( PayDate) as PayDate, WageType, PayRate
from Whatever
group by EmpName, WageType, PayRate
Keep in mind this result set will lack rows if a rate fluctuates over time and is re-used.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 24, 2014 at 8:09 am
Hi,
In my case the same payrate is is reused after few payrolls. How to fetch those rows also?
Here I can fetch payrate only once.
Any suggestions.
Regards,
Bala
October 24, 2014 at 9:09 am
You could start with something like this. It gives you back almost what you're looking for, it just leaves out the cases where the employee pay rate never changes :
CREATE TABLE #PayScale
(
ID INT IDENTITY PRIMARY KEY,
EmpName VARCHAR(30),
PayDate DATETIME,
WageType VARCHAR(30),
PayRate DECIMAL(18, 2)
)
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('ABC', '2014-10-22', 'Regular', '10.20')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('XYZ', '2014-10-22', 'Regular', '9.30')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('ABC', '2014-10-23', 'Regular', '10.20')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('ABC', '2014-10-24', 'Regular', '11.50')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('ABC', '2014-10-25', 'Regular', '11.20')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('XYZ', '2014-10-25', 'Regular', '9.30')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('ABC', '2014-10-26', 'Regular', '10.20')
INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)
VALUES ('XYZ', '2014-10-26', 'Regular', '9.30')
;
WITH cte AS
(
SELECT p1.*, p2.PayDate AS NextPayDate, p2.WageType AS NextWageType, p2.PayRate AS NextPayRate, ROW_NUMBER() OVER (PARTITION BY p1.EmpName, p1.PayDate ORDER BY p2.PayDate ASC) AS rowNum
FROM #PayScale p1
LEFT JOIN #PayScale p2 ON p2.PayDate > p1.PayDate AND p2.EmpName = p1.EmpName
)
SELECT EmpName, PayDate, PayRate
FROM cte
WHERErowNum = 1
AND NextPayRate <> PayRate
DROP TABLE #PayScale
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply