Author |
Topic |
Casp
Starting Member
6 Posts |
Posted - 2013-06-14 : 06:51:37
|
Hello,I need to calculate te target time, being the time that is to be substracted of an emplyees target. Having the table below, how would i be able to add a column where the registered time is calculated as follows: 1. For every itemid, if an employee is "mentor", he gets all the time, the others get 0.2. For every Itemid, if mentor is not present time is divided between employees.clientnr employeeID registered time itemID mentorID121 105 60 20042 105121 66 60 20042 105121 304 60 20042 105214 83 105 30469 240214 38 110 30470 240316 49 120 19746 46316 142 120 19746 46316 206 120 19746 46316 130 120 19746 46 The desired result would beclientnr employeeID registered time itemID mentorID target-time121 105 60 20042 105 60121 66 60 20042 105 0121 304 60 20042 105 0214 83 105 30469 240 105214 38 110 30470 240 110316 49 120 19746 46 30316 142 120 19746 46 30316 206 120 19746 46 30316 130 120 19746 46 30 I think i need to use the case statement but don't know how to pull it off.I have MsSql2005.Who can help me? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 06:59:39
|
[code]SELECT *,CASE WHEN employeeID = mentorID THEN [registered time] WHEN COUNT(CASE WHEN employeeID= mentorID THEN 1 ELSE 0 END) OVER (PARTITION BY itemID) >0 THEN 0 ELSE [registered time]/COUNT(1) OVER (PARTITION BY itemID) END AS [target-time]FROM Table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 07:03:28
|
In case your compatibility level is below 90 use thisSELECT t.*,CASE WHEN employeeID = mentorID THEN [registered time] WHEN MentorCnt >0 THEN 0 ELSE [registered time]/TotalCnt END AS [target-time]FROM table tINNER JOIN ( SELECT ItemID, SUM(CASE WHEN employeeID= mentorID THEN 1 ELSE 0 END) AS MentorCnt, COUNT(*) AS TotalCnt FROM Table GROUP BY ItemID )t1ON t1.ItemID = t.ItemID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Casp
Starting Member
6 Posts |
Posted - 2013-06-14 : 07:34:55
|
Thanks for the quick reply's. I have tried the first solution. It works but it gives a 0 value to all employees if they are not the Mentor. I will now try the second solution. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 07:38:20
|
quote: Originally posted by Casp Thanks for the quick reply's. I have tried the first solution. It works but it gives a 0 value to all employees if they are not the Mentor. I will now try the second solution.
but did they've a mentro for the itemid group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Casp
Starting Member
6 Posts |
Posted - 2013-06-14 : 07:40:52
|
No, even if the mentor is not present, the value is 0. Only mentors get a value. |
|
|
Casp
Starting Member
6 Posts |
Posted - 2013-06-14 : 07:58:34
|
Perhaps its because employeeID, mentorID end itemID all come from different tables which are Inner Joined. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 10:35:13
|
quote: Originally posted by Casp Perhaps its because employeeID, mentorID end itemID all come from different tables which are Inner Joined.
Nope...so far as output is same as you showed it should work fine.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Casp
Starting Member
6 Posts |
Posted - 2013-06-14 : 11:26:50
|
It doesnt matter anymore because your second solutionWorked perfectly! I had to create a temp table to be able to use itBut it dit the trick! Many thanks and regards. |
|
|
Casp
Starting Member
6 Posts |
Posted - 2013-06-14 : 11:27:54
|
It doesnt matter anymore because your second solutionWorked perfectly! I had to create a temp table to be able to use itBut it dit the trick! Many thanks and regards. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 14:53:44
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 14:55:50
|
actually i think first suggestion wll work if you change to thisSELECT *,CASE WHEN employeeID = mentorID THEN [registered time] WHEN COUNT(CASE WHEN employeeID= mentorID THEN 1 ELSE 0 END) OVER (PARTITION BY itemID) =0 THEN [registered time]/COUNT(1) OVER (PARTITION BY itemID) ELSE 0 END AS [target-time]FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-15 : 03:22:14
|
[code]-- Prepare sample dataDECLARE @Sample TABLE ( ClientNr SMALLINT NOT NULL, EmployeeID SMALLINT NOT NULL, Registered TINYINT NOT NULL, ItemID SMALLINT NOT NULL, MentorID SMALLINT NOT NULL );INSERT @Sample ( ClientNr, EmployeeID, Registered, ItemID, MentorID )VALUES (121, 105, 60, 20042, 105), (121, 66, 60, 20042, 105), (121, 304, 60, 20042, 105), (214, 83, 105, 30469, 240), (214, 38, 110, 30470, 240), (316, 49, 120, 19746, 46), (316, 142, 120, 19746, 46), (316, 206, 120, 19746, 46), (316, 130, 120, 19746, 46);-- SwePesoWITH cteSource(ClientNr, EmployeeID, Registered, ItemID, MentorID, Items, Cnt)AS ( SELECT ClientNr, EmployeeID, Registered, ItemID, MentorID, MAX(CASE WHEN EmployeeID = MentorID THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Items, COUNT(*) OVER (PARTITION BY ItemID) AS Cnt FROM @Sample)SELECT ClientNr, EmployeeID, Registered, ItemID, MentorID, Items, CASE WHEN Items = 0 THEN Registered / Cnt WHEN EmployeeID = MentorID THEN Registered ELSE 0 END AS [Target-Time]FROM cteSourceORDER BY ClientNr[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-15 : 03:24:07
|
quote: Originally posted by visakh16
COUNT(CASE WHEN employeeID = mentorID THEN 1 ELSE 0 END) OVER (PARTITION BY itemID)
Change COUNT to SUM. N 56°04'39.26"E 12°55'05.63" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 13:20:23
|
quote: Originally posted by SwePeso
quote: Originally posted by visakh16
COUNT(CASE WHEN employeeID = mentorID THEN 1 ELSE 0 END) OVER (PARTITION BY itemID)
Change COUNT to SUM. N 56°04'39.26"E 12°55'05.63"
ah...nice catch ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|