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
 General SQL Server Forums
 New to SQL Server Programming
 calculating target time

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	 mentorID
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


The desired result would be

clientnr	employeeID	registered time	itemID	mentorID	target-time
121 105 60 20042 105 60
121 66 60 20042 105 0
121 304 60 20042 105 0
214 83 105 30469 240 105
214 38 110 30470 240 110
316 49 120 19746 46 30
316 142 120 19746 46 30
316 206 120 19746 46 30
316 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 this


SELECT t.*,
CASE WHEN employeeID = mentorID THEN [registered time]
WHEN MentorCnt >0 THEN 0
ELSE [registered time]/TotalCnt
END AS [target-time]
FROM table t
INNER JOIN (
SELECT ItemID,
SUM(CASE WHEN employeeID= mentorID THEN 1 ELSE 0 END) AS MentorCnt,
COUNT(*) AS TotalCnt
FROM Table
GROUP BY ItemID
)t1
ON t1.ItemID = t.ItemID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Casp
Starting Member

6 Posts

Posted - 2013-06-14 : 11:26:50
It doesnt matter anymore because your second solution
Worked perfectly! I had to create a temp table to be able to use it
But it dit the trick! Many thanks and regards.
Go to Top of Page

Casp
Starting Member

6 Posts

Posted - 2013-06-14 : 11:27:54
It doesnt matter anymore because your second solution
Worked perfectly! I had to create a temp table to be able to use it
But it dit the trick! Many thanks and regards.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 14:53:44
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 this


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 [registered time]/COUNT(1) OVER (PARTITION BY itemID)
ELSE 0
END AS [target-time]
FROM Table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-15 : 03:22:14
[code]-- Prepare sample data
DECLARE @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);

-- SwePeso
WITH 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 cteSource
ORDER BY ClientNr[/code]


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

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"
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -