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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Find maximum time an employee falls in which shift
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

baburk
Posting Yak Master

India
108 Posts

Posted - 08/26/2010 :  01:35:08  Show Profile  Visit baburk's Homepage  Reply with Quote
DECLARE @CheckInTime AS TABLE(EmpID INT, InTime DATETIME, OutTime DATETIME)

INSERT INTO @CheckInTime

SELECT 1, '1900-01-01 00:50:00.000', '1900-01-01 08:50:00.000' UNION ALL

SELECT 2, '1900-01-01 09:45:00.000', '1900-01-01 14:45:00.000' UNION ALL

SELECT 3, '1900-01-01 14:20:00.000', '1900-01-01 22:45:00.000' UNION ALL

SELECT 4, '1900-01-01 08:30:00.000', '1900-01-01 06:45:00.000'

--SELECT * FROM @CheckInTime

DECLARE @ShiftMaster AS TABLE([ShiftID] [int],

[Name] [varchar](50) NOT NULL,

[StartTime] [datetime] NOT NULL,

[EndTime] [datetime] NOT NULL)

INSERT INTO @ShiftMaster

SELECT 1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALL

SELECT 7, 'F', '1900-01-01 00:00:00.000', '1900-01-01 08:00:00.000' UNION ALL

SELECT 8, 'S', '1900-01-01 08:00:00.000', '1900-01-01 16:00:00.000' UNION ALL

SELECT 9, 'T', '1900-01-01 16:00:00.000', '1900-01-01 23:59:00.000'

There is no relationship between @ShiftMaster and @CheckInTime.

Here emp 1 should comes in F shift. He also falls in S shift. But he works more than F shift when compared to S shift
Here emp 2 should comes in G shift
Here emp 3 should comes in T shift (because he works more time in T shift)
Here emp 4 should comes in S shift

I wants to get (ShiftID) in which shift he works for more time.

Thanks,
Babu Kumarasamy.

Edited by - baburk on 08/26/2010 08:41:17

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 08/26/2010 :  07:27:32  Show Profile  Reply with Quote
Is it typo error or data is like this:

SELECT 1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000'

SELECT 7, 'F', '1900-01-01 06:00:00.000', '1900-01-01 14:00:00.000'

An employee coming at 11:30:00 falls in both shift.

Edited by - pk_bohra on 08/26/2010 07:27:57
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/26/2010 :  09:08:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE @ShiftMaster TABLE
	(
		ShiftID INT NOT NULL,
		Name VARCHAR(50) NOT NULL,
		StartTime SMALLDATETIME NOT NULL,
		EndTime SMALLDATETIME NOT NULL
	)

INSERT	@ShiftMaster
	(
		ShiftID,
		Name,
		StartTime,
		EndTime
	)
SELECT	1, 'G', '09:30', '22:00' UNION ALL
SELECT	7, 'F', '00:00', '08:00' UNION ALL
SELECT	8, 'S', '08:00', '16:00' UNION ALL
SELECT	9, 'T', '16:00', '23:59'

DECLARE @CheckInTime TABLE
	(
		EmpID INT NOT NULL,
		InTime SMALLDATETIME NOT NULL,
		OutTime SMALLDATETIME NOT NULL
	)

INSERT	@CheckInTime 
	(
		EmpID,
		InTime,
		OutTime
	)
SELECT	1, '00:50', '08:50' UNION ALL
SELECT	2, '09:45', '14:45' UNION ALL
SELECT	3, '14:20', '22:45' UNION ALL
SELECT	4, '08:30', '16:45'

;WITH cteTimings(EmpID, ShiftID, Name, InTime, OutTime)
AS (
	SELECT		cit.EmpID,
			sm.ShiftID,
			sm.Name,
			CASE
				WHEN cit.InTime < sm.StartTime THEN sm.StartTime
				ELSE cit.InTime
			END AS InTime,
			CASE
				WHEN cit.OutTime > sm.EndTime THEN sm.EndTime
				ELSE cit.OutTime
			END AS OutTime
	FROM		@ShiftMaster AS sm
	INNER JOIN	@CheckInTime AS cit ON cit.InTime <= sm.EndTime
				AND cit.OutTime >= sm.StartTime
)
SELECT	EmpID,
	ShiftID,
	Name,
	WorkMinutes
FROM	(
		SELECT	EmpID,
			ShiftID,
			Name,
			DATEDIFF(MINUTE, InTime, OutTime) AS WorkMinutes,
			RANK() OVER (PARTITION BY EmpID ORDER BY DATEDIFF(MINUTE, InTime, OutTime) DESC) AS RecID
		FROM	cteTimings
	) AS d
WHERE	RecID = 1
You have a problem with EmpID 2. He or she works 300 minutes with fits in both Shift G and S.


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

baburk
Posting Yak Master

India
108 Posts

Posted - 08/27/2010 :  01:52:14  Show Profile  Visit baburk's Homepage  Reply with Quote
quote:
Originally posted by Peso



Thanks Peso. You helped me what I have expected.

Peso, may I know what is the formatter tool that you are using?.

Edited by - baburk on 08/27/2010 01:54:50
Go to Top of Page

baburk
Posting Yak Master

India
108 Posts

Posted - 08/27/2010 :  02:42:29  Show Profile  Visit baburk's Homepage  Reply with Quote

;WITH cteTimings(EmpID, ShiftID, Name, InTime, OutTime)
AS (
	SELECT	cit.EmpID,
			sm.ShiftID,
			sm.Name,
			--When the shift name is general(G) then we should take CheckIn InTime. (Because the CheckIn and CheckOut of an employee comes with in the General shift time)
			CASE
				WHEN cit.InTime < sm.StartTime AND sm.Name != 'G' THEN sm.StartTime
				WHEN cit.InTime > sm.StartTime AND sm.Name != 'G' THEN cit.InTime
				ELSE cit.InTime
			END AS InTime,
			CASE
				WHEN cit.OutTime > sm.EndTime AND sm.Name != 'G' THEN sm.EndTime
				WHEN cit.OutTime < sm.EndTime AND sm.Name != 'G' THEN cit.OutTime
				ELSE cit.OutTime
			END AS OutTime
	FROM		@ShiftMaster AS sm
	INNER JOIN	@CheckInTime AS cit ON
			    (cit.InTime <= sm.EndTime
				AND cit.OutTime >= sm.StartTime)
)

SELECT EmpID,
	ShiftID,
	Name,
	WorkMinutes
FROM	(
		SELECT	EmpID,
			ShiftID,
			Name,
			DATEDIFF(MINUTE, InTime, OutTime) AS WorkMinutes,
			--When the start and end time falls in between the general shift we have to show general shift.
			--So we are adding ORDER BY ShiftID and in the table G should be the first record in the ShiftMaster table
			RANK() OVER (PARTITION BY EmpID ORDER BY ShiftID, DATEDIFF(MINUTE, InTime, OutTime) DESC) AS RecID
		FROM cteTimings
	) AS d
WHERE RecID = 1

Edited by - baburk on 08/30/2010 01:57:28
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/27/2010 :  03:14:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Fingers and tab button.
When I post the code on this forum, I use the |code| and |/code| tags.



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

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/30/2010 :  02:48:12  Show Profile  Reply with Quote
Are you serious Peso? Fingers and tab-button for all these posts you keep reformatting?? I thought you got tools from red-gate for free...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/30/2010 :  03:16:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh, you meant existing code? I have my own reformatter.
Luckily (or coincidentally) it produce the same format as I write manually.

Have tried to get the RedGate tool to do what I want, to no vail.



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

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/30/2010 :  03:33:03  Show Profile  Reply with Quote
Hm, your own reformatter? Is it written in t-sql?? Feel free to share if it's anything worth using :)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Edited by - Lumbago on 08/30/2010 03:33:25
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/30/2010 :  03:41:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It's a PASCAL 6.0 application, runs in DOS window (reformats text files) and I wrote in 1986.
Noone would be interested in it now.


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

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/30/2010 :  03:55:23  Show Profile  Reply with Quote
I was still in grammar school in 1986

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/30/2010 :  04:00:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
In 1986 I was in first year of High School (age 16-17).


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

khtan
In (Som, Ni, Yak)

Singapore
17636 Posts

Posted - 08/30/2010 :  04:05:04  Show Profile  Reply with Quote
Pascal 6.0 ? I remember playing around with Turbo Pascal 3 on a 8088 computer in the early 80s

Think should be around 83 - 85 together with space invader, pacman, dig-dugg and multiplan


KH
Time is always against us


Edited by - khtan on 08/30/2010 04:07:41
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/30/2010 :  04:27:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh, those were the days. No harddrive, only 2 diskettes 5.25" (360kb).
One for OS and one for Turbo Pascal and all projects.


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

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/30/2010 :  06:17:31  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
quote:
Originally posted by Peso

Oh, those were the days. No harddrive, only 2 diskettes 5.25" (360kb)......

N 56°04'39.26"
E 12°55'05.63"



Rose tinted spectacles eh? If you think a bit harder I'm sure you'll remember all the really annoying things too......

example:
640k limit on usable ram without a lot of work.
640x480 screen modes (or 320x240 if you wanted more than 16 colours).

And pascal sucked. It really, really sucked. (we used it at school along with comal).

But you could either go one of two ways: pascel et all or have to deal with all the retardedness that was C pointer arithmetic.




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 08/30/2010 :  07:02:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I'd say Pascal is one of the best educational languages.
Delphi later derived from Pascal and is widely used today.

At that time 640kb was all you needed. Heck, I wrote a fullblown BBS application and never had to use more than 640kb.
Perhaps that was due to modems operated at a maximum of 300bps. A full screen of 80x25 characters took 10 seconds or so to download.



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

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/30/2010 :  07:10:39  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
maybe we should start a reminiscing thread?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
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.11 seconds. Powered By: Snitz Forums 2000