February 20, 2017 at 1:02 am
Top performer for that particular day is calculated by (Goodcheckin-Badcheckin) for that particular dayOverall top performer is calculated by (Goodcheckin-Badcheckin) for 1st day,similarly we have calculate Overall top performer for 2nd day =addition of 1st day +2nd day,Similarly we have calculate for 3rd day & 4th day
Input Details:
| EMPID | NAME | GOODCHECKIN | BADCHECKIN | CHECKIN DATE |
| 1 | TOM | 3 | 2 | 14/02/2017 |
| 1 | TOM | 4 | 1 | 15/02/2017 |
| 1 | TOM | 2 | 8 | 16/02/2017 |
| 1 | TOM | 3 | 1 | 17/02/2017 |
| 2 | JERRY | 4 | 1 | 14/02/2017 |
| 2 | JERRY | 0 | 0 | 15/02/2017 |
| 2 | JERRY | 1 | 3 | 16/02/2017 |
| 2 | JERRY | 0 | 4 | 17/02/2017 |
| 3 | SARAVANAN | 6 | 0 | 14/02/2017 |
| 3 | SARAVANAN | 2 | 1 | 15/02/2017 |
| 3 | SARAVANAN | 10 | 11 | 16/02/2017 |
| 3 | SARAVANAN | 5 | 1 | 17/02/2017 |
Output :Output should be
| EMPID | NAME | CHECKIN DATE | Top performer on that day | Overall Top perfomer |
| 3 | SARAVANAN | 14/02/2017 | 6 | 6 |
| 1 | TOM | 15/02/2017 | 3 | 7 |
| 3 | SARAVANAN | 16/02/2017 | -1 | 6 |
| 3 | SARAVANAN | 17/02/2017 | 4 | 10 |
Saravanan
February 20, 2017 at 2:54 am
When supplying data, you should really do so in a format that we can make use of in SQL. A table doesn't meet those requirements. I've done this for you, and included an solution I believe meets your requirements:CREATE TABLE #Employee (EMPID INT,
[NAME] VARCHAR(20),
GOODCHECKIN INT,
BADCHECKIN INT,
CHECKINDATE DATE);
INSERT INTO #Employee
VALUES (1,'TOM',3,2,'14/02/2017'),
(1,'TOM',4,1,'15/02/2017'),
(1,'TOM',2,8,'16/02/2017'),
(1,'TOM',3,1,'17/02/2017'),
(2,'JERRY',4,1,'14/02/2017'),
(2,'JERRY',0,0,'15/02/2017'),
(2,'JERRY',1,3,'16/02/2017'),
(2,'JERRY',0,4,'17/02/2017'),
(3,'SARAVANAN',6,0,'14/02/2017'),
(3,'SARAVANAN',2,1,'15/02/2017'),
(3,'SARAVANAN',10,11,'16/02/2017'),
(3,'SARAVANAN',5,1,'17/02/2017');
GO
SELECT *
FROM #Employee;
GO
WITH TopPerformers AS(
SELECT EMPID, [NAME],
CHECKINDATE, GOODCHECKIN - BADCHECKIN AS SCORE,
RANK() OVER (PARTITION BY CHECKINDATE ORDER BY GOODCHECKIN - BADCHECKIN DESC) AS DAILYRANK
FROM #Employee)
SELECT TP.EMPID, TP.NAME,
TP.CHECKINDATE, TP.SCORE, TS.TOTALSCORE
FROM TopPerformers TP
CROSS APPLY (SELECT TOP 1 SUM(GOODCHECKIN - BADCHECKIN) AS TOTALSCORE
FROM #Employee ca
WHERE ca.CHECKINDATE <= TP.CHECKINDATE
GROUP BY ca.EMPID
ORDER BY TOTALSCORE DESC) TS
WHERE TP.DAILYRANK = 1;
GO
DROP TABLE #Employee;
Note, that if two employee's have the has score on a single day, both will be returned. You didn't include logic for this so I have guessed that that would be the "expected" result.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 20, 2017 at 4:01 am
Thanks Thorn ... I really appreciate your efforts . But I am not able to understand Cross Apply... Could you pls provide any useful link for it.. If you got one ?
Saravanan
February 20, 2017 at 4:09 am
saravanatn - Monday, February 20, 2017 4:01 AMThanks Thorn ... I really appreciate your efforts . But I am not able to understand Cross Apply... Could you pls provide any useful link for it.. If you got one ?
Google, and try searching something like "T-SQL CROSS APPLY". It will give you a huge amount of information about it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 20, 2017 at 11:19 pm
Thanks Thorn
Saravanan
February 20, 2017 at 11:57 pm
You can check the below mentioned articles by Paul White for more information on CROSS-APPLY
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
The below mentioned videos from YouTube by Itzik Ben-Gan are also really good.
https://youtu.be/3Cw2fuO5TG0
https://youtu.be/qUT3r6OO2Xk
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 21, 2017 at 2:48 am
Thanks Bro..
Saravanan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply