sql server programming:
I have an SQL table that contains records like:
[quoted text, click to view]

We've defined "morning-shift" as anyone that is at work at 11am.
We've defined "afternoon-shift" as anyone that is at work at 7pm.
We've defined "midnight-shift" as anyone that is at work at 3am.
(Regardless of their actual start-time or end-time.)

We need some output that would look at the past 90 days and count
how many times each user has worked each shift:
User-name Morning-shift Afternoon-shift Midnight-shift
Bob 12 17 33
Gary 2 44 23
Joe 0 11 37

I can do something similar if I just think about the start-time... but =
that's not
what I need to do:
[quoted text, click to view]

The problem comes in when I try to determine each of the 3 shifts:
An "afternoon-shift" is any shift that occurs when the employee is at =
work at 7pm. =20
Regardless if he started at 4,5,6pm... or leaves at 8,9,10pm. As long =
as it "crosses"
the 7pm mark... that should be counted at "afternoon-shift".

How can I "GROUP BY" "Start-time to end-time" will cross the 7pm mark?
(As well as for 11am, and 3am, for the other 2 shifts.)

Hello A_Michigan_User,

I came up with a SQL script to demonstrate what you are trying to =
achieve...

DECLARE @temp TABLE
(
UserName VARCHAR(100),
StartDTM DATETIME,
EndDTM DATETIME
)

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'22-Jan-2005 9:26am', '22-Jan-2005 3:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'21-Jan-2005 3:05pm', '21-Jan-2005 9:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'20-Jan-2005 3:05pm', '20-Jan-2005 9:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'19-Jan-2005 3:05pm', '19-Jan-2005 9:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'10-Jan-2005 4:05pm', '19-Jan-2005 10:06pm');

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Gary', =
'22-Jan-2005 7:45am', '22-Jan-2005 2:25pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Gary', =
'21-Jan-2005 9:08am', '21-Jan-2005 2:10pm');

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Joe', =
'23-Jan-2005 3:16am', '23-Jan-2005 1:11pm');

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred', =
'23-Jan-2005 11:47am', '23-Jan-2005 5:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred', =
'24-Jan-2005 12:05am', '24-Jan-2005 7:34am');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred', =
'25-Jan-2005 12:05pm', '25-Jan-2005 9:10pm');

SELECT
UserName,

SUM((CASE
WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 11:00:00 =
am') BETWEEN StartDTM AND EndDTM
THEN 1
ELSE 0
END)) AS MorningShifts,

SUM((CASE
WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 7:00:00 =
pm') BETWEEN StartDTM AND EndDTM
THEN 1
ELSE 0
END)) AS AfternoonShift,

SUM((CASE
WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 3:00:00 =
am') BETWEEN StartDTM AND EndDTM
THEN 1
ELSE 0
END)) AS MidnightShift
FROM
@temp
WHERE StartDTM BETWEEN '01/20/2005 12:00:00 am' AND '01/25/2005 11:59:59 =
pm'
GROUP BY
UserName

I'm one those "show me an example kind of people"... so if you need some =
more help from me on this just email me cp.hayes@sbcglobalNOSPAMTOME.net =
(just remove the NOSPAMTOME part of the email address) and I'll help =
with it.

Good luck,

Chris


[quoted text, click to view]

I have an SQL table that contains records like:
[quoted text, click to view]

We've defined "morning-shift" as anyone that is at work at 11am.
We've defined "afternoon-shift" as anyone that is at work at 7pm.
We've defined "midnight-shift" as anyone that is at work at 3am.
(Regardless of their actual start-time or end-time.)

We need some output that would look at the past 90 days and count
how many times each user has worked each shift:
User-name Morning-shift Afternoon-shift Midnight-shift
Bob 12 17 33
Gary 2 44 23
Joe 0 11 37

I can do something similar if I just think about the start-time... but =
that's not
what I need to do:
[quoted text, click to view]

The problem comes in when I try to determine each of the 3 shifts:
An "afternoon-shift" is any shift that occurs when the employee is at =
work at 7pm. =20
Regardless if he started at 4,5,6pm... or leaves at 8,9,10pm. As long =
as it "crosses"
the 7pm mark... that should be counted at "afternoon-shift".

How can I "GROUP BY" "Start-time to end-time" will cross the 7pm mark?
(As well as for 11am, and 3am, for the other 2 shifts.)

You may want to incorporate the last posting into a function called
ftn_GetShift with the parameters start and end time.

Sounds like you will use it again

Jim
[quoted text, click to view]

I have an SQL table that contains records like:
[quoted text, click to view]

We've defined "morning-shift" as anyone that is at work at 11am.
We've defined "afternoon-shift" as anyone that is at work at 7pm.
We've defined "midnight-shift" as anyone that is at work at 3am.
(Regardless of their actual start-time or end-time.)

We need some output that would look at the past 90 days and count
how many times each user has worked each shift:
User-name Morning-shift Afternoon-shift Midnight-shift
Bob 12 17 33
Gary 2 44 23
Joe 0 11 37

I can do something similar if I just think about the start-time... but
that's not
what I need to do:
[quoted text, click to view]

The problem comes in when I try to determine each of the 3 shifts:
An "afternoon-shift" is any shift that occurs when the employee is at work
at 7pm.
Regardless if he started at 4,5,6pm... or leaves at 8,9,10pm. As long as it
"crosses"
the 7pm mark... that should be counted at "afternoon-shift".

How can I "GROUP BY" "Start-time to end-time" will cross the 7pm mark?
(As well as for 11am, and 3am, for the other 2 shifts.)

Thanks

Wow, Chris (and others) thanks... I haven't tried it yet... but that =
looks like it might just do it.

I was going to try to simplify things and forget about trying the =
start-time and end-time ranges method....
and instead do something like:
Only look at the start-time (ignore the end-time).
If the start-time occurs between 3am-11am that's AM shift.
If the start-time occurs between 11am-7pm that's PM shift.
If the start-time occurs between 7pm-3am that's Midnight shift.

But it looks like (with a slight change) I might be able to do either =
method.

Thanks again.

[quoted text, click to view]
Hello A_Michigan_User,

I came up with a SQL script to demonstrate what you are trying to =
achieve...

DECLARE @temp TABLE
(
UserName VARCHAR(100),
StartDTM DATETIME,
EndDTM DATETIME
)

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'22-Jan-2005 9:26am', '22-Jan-2005 3:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'21-Jan-2005 3:05pm', '21-Jan-2005 9:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'20-Jan-2005 3:05pm', '20-Jan-2005 9:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'19-Jan-2005 3:05pm', '19-Jan-2005 9:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Bob', =
'10-Jan-2005 4:05pm', '19-Jan-2005 10:06pm');

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Gary', =
'22-Jan-2005 7:45am', '22-Jan-2005 2:25pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Gary', =
'21-Jan-2005 9:08am', '21-Jan-2005 2:10pm');

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Joe', =
'23-Jan-2005 3:16am', '23-Jan-2005 1:11pm');

INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred', =
'23-Jan-2005 11:47am', '23-Jan-2005 5:15pm');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred', =
'24-Jan-2005 12:05am', '24-Jan-2005 7:34am');
INSERT INTO @temp (UserName, StartDTM, EndDTM) VALUES ('Fred', =
'25-Jan-2005 12:05pm', '25-Jan-2005 9:10pm');

SELECT
UserName,

SUM((CASE
WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 11:00:00 =
am') BETWEEN StartDTM AND EndDTM
THEN 1
ELSE 0
END)) AS MorningShifts,

SUM((CASE
WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 7:00:00 =
pm') BETWEEN StartDTM AND EndDTM
THEN 1
ELSE 0
END)) AS AfternoonShift,

SUM((CASE
WHEN CONVERT(DATETIME, CONVERT(CHAR(10), StartDTM, 101) + ' 3:00:00 =
am') BETWEEN StartDTM AND EndDTM
THEN 1
ELSE 0
END)) AS MidnightShift
FROM
@temp
WHERE StartDTM BETWEEN '01/20/2005 12:00:00 am' AND '01/25/2005 =
11:59:59 pm'
GROUP BY
UserName

I'm one those "show me an example kind of people"... so if you need =
some more help from me on this just email me =
cp.hayes@sbcglobalNOSPAMTOME.net (just remove the NOSPAMTOME part of the =
email address) and I'll help with it.

Good luck,

Chris


""A_Michigan_User"" <Michigan_RE_M0VE@ameritech.net> wrote in =
message news:%23IBvrfQAFHA.1188@tk2msftngp13.phx.gbl...

I have an SQL table that contains records like:
[quoted text, click to view]

We've defined "morning-shift" as anyone that is at work at 11am.
We've defined "afternoon-shift" as anyone that is at work at 7pm.
We've defined "midnight-shift" as anyone that is at work at 3am.
(Regardless of their actual start-time or end-time.)

We need some output that would look at the past 90 days and count
how many times each user has worked each shift:
User-name Morning-shift Afternoon-shift Midnight-shift
Bob 12 17 33
Gary 2 44 23
Joe 0 11 37

I can do something similar if I just think about the start-time... =
but that's not
what I need to do:
[quoted text, click to view]

The problem comes in when I try to determine each of the 3 shifts:
An "afternoon-shift" is any shift that occurs when the employee is =
at work at 7pm. =20
Regardless if he started at 4,5,6pm... or leaves at 8,9,10pm. As =
long as it "crosses"
the 7pm mark... that should be counted at "afternoon-shift".

How can I "GROUP BY" "Start-time to end-time" will cross the 7pm =
mark?
(As well as for 11am, and 3am, for the other 2 shifts.)