Find maximum time an employee falls in which shift

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

SQL Server 2005 Forums

Transact-SQL (2005)

Find maximum time an employee falls in which shift

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2010-08-26 : 01:35:08

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.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-26 : 07:27:32

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 09:08:50

[code]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[/code]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"

baburk
Posting Yak Master

108 Posts

Posted - 2010-08-27 : 01:52:14

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?.

baburk
Posting Yak Master

108 Posts

Posted - 2010-08-27 : 02:42:29

[code]
;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
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-27 : 03:14:18

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"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-30 : 02:48:12

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 03:16:11

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"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-30 : 03:33:03

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 03:41:52

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"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-30 : 03:55:23

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 04:00:06

In 1986 I was in first year of High School (age 16-17).

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-30 : 04:05:04

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
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 04:27:40

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"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-30 : 06:17:31

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:02:59

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"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-30 : 07:10:39

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources