SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 calculating target time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Casp
Starting Member

Netherlands
6 Posts

Posted - 06/14/2013 :  06:51:37  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/14/2013 :  06:59:39  Show Profile  Reply with Quote

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


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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/14/2013 :  07:03:28  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/14/2013 07:03:49
Go to Top of Page

Casp
Starting Member

Netherlands
6 Posts

Posted - 06/14/2013 :  07:34:55  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/14/2013 :  07:38:20  Show Profile  Reply with Quote
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

Netherlands
6 Posts

Posted - 06/14/2013 :  07:40:52  Show Profile  Reply with Quote
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

Netherlands
6 Posts

Posted - 06/14/2013 :  07:58:34  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/14/2013 :  10:35:13  Show Profile  Reply with Quote
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

Netherlands
6 Posts

Posted - 06/14/2013 :  11:26:50  Show Profile  Reply with Quote
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

Netherlands
6 Posts

Posted - 06/14/2013 :  11:27:54  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/14/2013 :  14:53:44  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/14/2013 :  14:55:50  Show Profile  Reply with Quote
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

Sweden
30241 Posts

Posted - 06/15/2013 :  03:22:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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



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

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 06/15/2013 :  03:24:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 06/15/2013 :  13:20:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000