Group by Shifts

[rosdan]

Hi,

I got a table in which one of the columns is of type datetime, named
"LoginDate"
I want to find the sum of logins based on three shifts, 7AM (7AM-3PM)
3PM (3PM-11PM) and 11PM (11PM to 7AM next day)

The "operational day" would be from 7AM till 7 am next day

1. How could I write a sql query that could sum the amount of logins
for each Shift?
2. How could I write a sql query that could sum the total logins for
each "operational day"?

regards

RT.

[Mark A. Parsons]

You might want to provide an example of what the final result is suppose to look like with a few days worth of data in mind.

[Mark A. Parsons]

And you may want to post the query(s) you've been working on to date.

[tartampion]

Would the following help:
select
case when (datepart(Hour,a)) >=7 and datepart(Hour,a) < 15
then "Shift 1"
when (datepart(Hour,a)) >=16 and datepart(Hour,a) < 23
then "shift 2"
else "shift 3" end
from mytable

[Bret Halford [Sybase]]

Here is a possible approach. I use a view based on dateadd()
to align the operational day with a calender day for convenience
in using the datepart() functions:

create table mytable (LoginDate datetime)
go
insert mytable values ("1/1/1900 2:22AM")
insert mytable values ("1/1/1900 5:22AM")
insert mytable values ("1/1/1900 9:22AM")
insert mytable values ("1/1/1900 9:22AM")
insert mytable values ("1/1/1900 12:22PM")
insert mytable values ("1/1/1900 20:22PM")
insert mytable values ("1/1/1900 21:22PM")
insert mytable values ("1/1/1900 22:22PM")

create view
adjusted_shift_vu
as
-- adjust the time portion back by 7 hours to align
-- the operational day with the calender day for ease
-- of using datepart().
select
dateadd(hh, -7, LoginDate) as LoginDate
from
mytable
go

create function shift @LoginDate datetime returns tinyint as

declare @shift int
select @shift = case when datepart(hour, @LoginDate) between 0 and 8 then 1
when datepart(hour, @LoginDate) between 8 and 16
then 2
else 3 end
return @shift
go

select * from adjusted_shift_vu
go

Print "Logins by shift"
select
convert(date,LoginDate) as date,
dbo.shift(LoginDate) as shift,
count(*) as login_count
from
adjusted_shift_vu
group by
convert(date, LoginDate), dbo.shift(LoginDate)
order by
date, shift
go

Print "Total logins per day"
select
convert(date, LoginDate) as date,
count(*) as login_count
from
adjusted_shift_vu
group by
convert(date, LoginDate)
order by
date
go

[rosdan]

Hi Mark,

For (1) The final result should be

OperationalDate Shift SumLogins
1/1/2009 7AM 25
1/1/2009 3PM 43
1/1/2009 11PM 21
2/1/2009 7AM 12
2/1/2009 3PM 35
2/1/2009 11PM 10
3/1/2009 7AM 11
3/1/2009 3PM 32
3/1/2009 11PM 11

for (2) I just need the operationalDate and sum

OperationalDate SumLogins
1/1/2009 89
2/1/2009 57
3/1/2009 54

regards

RT.

On May 19, 11:00 pm, "Mark A. Parsons"

[rosdan]

Thanks Bred,

I was hoping for a one-liner... :)
Looks like I need to write a stoed proc for that.

RT..

[rosdan]

for question (2), I got a one-liner... Looks OK..

select convert(varchar(12),dateadd(hh,-7,LoginDate),106), count(*)
from TABLE
where LoginDate>="1 Jan 2009 7:00"
and LoginDate<"4 Jan 2009 7:00"
group by convert(varchar(12),dateadd(hh,-7,LoginDate),106)

thanks..

RT..

[PDreyer]

Is this whar you want?
[code]
select opdt=dateadd(dd,datediff(dd,'20000101',dateadd
(hh,-7,dt)),'20000101')
,shift1=sum(case when datepart(hh, dateadd(hh,-7,dt))/8+1=1 then 1
else 0 end)
,shift2=sum(case when datepart(hh, dateadd(hh,-7,dt))/8+1=2 then 1
else 0 end)
,shift3=sum(case when datepart(hh, dateadd(hh,-7,dt))/8+1=3 then 1
else 0 end)
,total=count(*)
from (
-- test data start --
select dt=dateadd(dd,v.number,t.tm) from (select
'20090101 00:00:00' union all select
'20090101 00:15:00' union all select
'20090101 03:15:00' union all select
'20090101 07:00:00' union all select
'20090101 07:15:00' union all select
'20090101 09:15:00' union all select
'20090101 15:00:00' union all select
'20090101 15:15:00' union all select
'20090101 23:00:00' union all select
'20090101 23:15:00' )t(tm)
,master.dbo.spt_values v
where v.type='P' and v.number<10)testdata
-- test data end --
group by dateadd(dd,datediff(dd,'20000101',dateadd
(hh,-7,dt)),'20000101')
order by 1
[/code]

[rosdan]

Got the solution for question (1), even though not as exact as I want,
but acceptable...
It was based on input by PDreyer and others.

select convert(varchar(12),dateadd(hh,-7,LoginDate),106), datepart
(hh,LoginDate)/8+1,count(*)

from TABLE
where LoginDate>="1 Jan 2009 7:00"
and LoginDate<"4 Jan 2009 7:00"

group by convert(varchar(12),dateadd(hh,-7,LoginDate),106),datepart
(hh,LoginDate)/8+1

RESULT:

OperationalDate Shift Total
--------------- ----------- -----------
01 Jan 2009 1 3210
01 Jan 2009 2 15546
01 Jan 2009 3 14491
02 Jan 2009 1 3128
02 Jan 2009 2 10058
02 Jan 2009 3 11278
03 Jan 2009 1 3382
03 Jan 2009 2 13450
03 Jan 2009 3 13007

THANKS!!!!!

[ThanksButNo]

It's always been my contention that the database should
reflect reality as closely as possible. Reality includes
frequently asked questions. E.g., if column A is stored
and column B is stored, but a very frequently asked question
is, "What is A divided by B?" Well, you can always just
calculate that and return it -- or you can store the answer
in column C.

Particularly when the calculation is troublesome; like
in this example for Shift.

In this case, a piece of important reality is being asked,
I presume, frequently. But, since the information is part
of what's already stored, the "purist" answer is to make
the calculation each time the question is asked. I think
this is a good case where the answer should be pre-calculated
and stored as part of the database.

Therefore, I would suggest adding a new column called "Shift".
Set it initially to a function of "LoginDate", based on the
hour: (quick and dirty function below, you can expand on it)

1> declare @shift varchar(4)
2> select @shift = case
3> when datepart(hour,getdate()) >= 7
4> and datepart(hour,getdate()) < 15
5> then '7AM'
6> when datepart(hour,getdate()) >= 15
7> and datepart(hour,getdate()) < 23
8> then '3PM'
9> else '11PM'
10> end
11> print @shift
12> go

There's another twist in having an "OperationalDate" that
doesn't line up with the normal date. Ok, let's just add
another new column.

1> declare @operationaldate = datetime
2> select @operationaldate = dateadd(hour, -7, getdate())
3> print @operationaldate

then keep these columns maintained thereafter with a
trigger. Whenever LoginDate is inserted or updated, use above
functions to recalculate Shift and OperationalDate.

Now, suddenly the queries become very easy to write:

(1)
select OperationalDate, Shift, sum(*) as SumLogins
from table
group by OperationalDate, Shift

(2)
select OperationalDate, sum(*) as SumLogins
from table
group by OperationalDate

Of course, when and where to add extra columns and triggers
is a judgment call. It really depends on how frequently
this particular question is going to be asked, and how complex
the calculation is to bring the answer. If it's asked often
enough, and the calculation complex enough, it's worth the
slight overhead to buy a simple query. E.g., in this case
you might not want to bother with an extra OperationalDate
column, since it's simply a 7 hour offset. But I think the
Shift is an excellent candidate for an extra column.

/:-/