Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Group by Shifts

11 posts in General Discussion Last posting was on 2009-05-21 09:12:09.0Z
rosdan Posted on 2009-05-19 13:55:48.0Z
From: rosdan <rosdan@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Group by Shifts
Date: Tue, 19 May 2009 06:55:48 -0700 (PDT)
Organization: http://groups.google.com
Lines: 19
Message-ID: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
NNTP-Posting-Host: 203.82.79.102
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1242741348 333 127.0.0.1 (19 May 2009 13:55:48 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 19 May 2009 13:55:48 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: y10g2000prc.googlegroups.com; posting-host=203.82.79.102; posting-account=3FGPywkAAABXx2FS8FpxBLY_ym3T4qS5
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/528.16 (KHTML, like Gecko) Version/4.0 Safari/528.16,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!news.glorb.com!postnews.google.com!y10g2000prc.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27735
Article PK: 77022

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" <iron_horse Posted on 2009-05-19 15:00:47.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
In-Reply-To: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090509-0, 05/09/2009), Outbound message
X-Antivirus-Status: Clean
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a12b8de$1@forums-3-dub.sybase.com>
X-Original-Trace: 19 May 2009 06:49:18 -0700, vip152.sybase.com
Lines: 22
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-Trace: 19 May 2009 08:00:23 -0700, forums-3-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 May 2009 08:00:47 -0700
X-Trace: forums-1-dub 1242745247 10.22.108.75 (19 May 2009 08:00:47 -0700)
X-Original-Trace: 19 May 2009 08:00:47 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27736
Article PK: 77023

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.

rosdan wrote:
> 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" <iron_horse Posted on 2009-05-19 15:27:43.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com> <4a12b8de$1@forums-3-dub.sybase.com>
In-Reply-To: <4a12b8de$1@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090509-0, 05/09/2009), Outbound message
X-Antivirus-Status: Clean
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a12bec3@forums-3-dub.sybase.com>
X-Original-Trace: 19 May 2009 07:14:27 -0700, vip152.sybase.com
Lines: 26
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-Trace: 19 May 2009 08:25:31 -0700, forums-3-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 May 2009 08:27:43 -0700
X-Trace: forums-1-dub 1242746863 10.22.108.75 (19 May 2009 08:27:43 -0700)
X-Original-Trace: 19 May 2009 08:27:43 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27737
Article PK: 77024

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

Mark A. Parsons wrote:
> 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.
>
> rosdan wrote:
>> 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.
>>
>>


rosdan Posted on 2009-05-20 04:13:06.0Z
From: rosdan <rosdan@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
Date: Tue, 19 May 2009 21:13:06 -0700 (PDT)
Organization: http://groups.google.com
Lines: 55
Message-ID: <adb7e2e5-f3b9-4b86-9d3a-97d95925cc99@s38g2000prg.googlegroups.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com> <4a12b8de$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: 60.50.69.246
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242792786 12289 127.0.0.1 (20 May 2009 04:13:06 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 20 May 2009 04:13:06 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: s38g2000prg.googlegroups.com; posting-host=60.50.69.246; posting-account=3FGPywkAAABXx2FS8FpxBLY_ym3T4qS5
User-Agent: G2/1.0
X-HTTP-UserAgent: Opera/9.64 (Windows NT 5.1; U; en) Presto/2.1.1,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!s38g2000prg.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27740
Article PK: 77027

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"

<iron_horse@no_spamola.compuserve.com> wrote:
> 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.
>
>
>
> rosdan wrote:
> > 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.


Bret Halford [Sybase] Posted on 2009-05-19 22:02:09.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
In-Reply-To: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a132c61$1@forums-1-dub>
Date: 19 May 2009 15:02:09 -0700
X-Trace: forums-1-dub 1242770529 10.22.241.152 (19 May 2009 15:02:09 -0700)
X-Original-Trace: 19 May 2009 15:02:09 -0700, vip152.sybase.com
Lines: 84
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27739
Article PK: 77025


rosdan wrote:
> 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"?
>

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 Posted on 2009-05-20 04:14:17.0Z
From: rosdan <rosdan@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
Date: Tue, 19 May 2009 21:14:17 -0700 (PDT)
Organization: http://groups.google.com
Lines: 93
Message-ID: <dfdd512d-1fdf-4716-a352-de7db38895f3@y33g2000prg.googlegroups.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com> <4a132c61$1@forums-1-dub>
NNTP-Posting-Host: 60.50.69.246
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242792857 12418 127.0.0.1 (20 May 2009 04:14:17 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 20 May 2009 04:14:17 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: y33g2000prg.googlegroups.com; posting-host=60.50.69.246; posting-account=3FGPywkAAABXx2FS8FpxBLY_ym3T4qS5
User-Agent: G2/1.0
X-HTTP-UserAgent: Opera/9.64 (Windows NT 5.1; U; en) Presto/2.1.1,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!y33g2000prg.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27741
Article PK: 77028

Thanks Bred,

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


RT..

On May 20, 6:02 am, "Bret Halford [Sybase]" <b...@sybase.com> wrote:
> rosdan wrote:
> > 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"?
>
> 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 Posted on 2009-05-20 06:41:41.0Z
From: rosdan <rosdan@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
Date: Tue, 19 May 2009 23:41:41 -0700 (PDT)
Organization: http://groups.google.com
Lines: 109
Message-ID: <8579769c-e254-461c-bed7-419f7ecb78b0@j18g2000prm.googlegroups.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com> <4a132c61$1@forums-1-dub> <dfdd512d-1fdf-4716-a352-de7db38895f3@y33g2000prg.googlegroups.com>
NNTP-Posting-Host: 60.50.69.246
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242801702 27639 127.0.0.1 (20 May 2009 06:41:42 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 20 May 2009 06:41:42 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: j18g2000prm.googlegroups.com; posting-host=60.50.69.246; posting-account=3FGPywkAAABXx2FS8FpxBLY_ym3T4qS5
User-Agent: G2/1.0
X-HTTP-UserAgent: Opera/9.64 (Windows NT 5.1; U; en) Presto/2.1.1,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!j18g2000prm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27742
Article PK: 77029

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

On May 20, 12:14 pm, rosdan <ros...@gmail.com> wrote:
> Thanks Bred,
>
> I was hoping for a one-liner... :)
> Looks like I need to write a stoed proc for that.
>
> RT..
>
> On May 20, 6:02 am, "Bret Halford [Sybase]" <b...@sybase.com> wrote:
>
>
>
> > rosdan wrote:
> > > 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"?
>
> > 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 Posted on 2009-05-20 10:16:48.0Z
From: rosdan <rosdan@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
Date: Wed, 20 May 2009 03:16:48 -0700 (PDT)
Organization: http://groups.google.com
Lines: 145
Message-ID: <35afbf67-ce36-4fd0-8965-07a22799273d@d7g2000prl.googlegroups.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com> <4a132c61$1@forums-1-dub> <dfdd512d-1fdf-4716-a352-de7db38895f3@y33g2000prg.googlegroups.com> <8579769c-e254-461c-bed7-419f7ecb78b0@j18g2000prm.googlegroups.com>
NNTP-Posting-Host: 60.50.69.246
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242814608 4014 127.0.0.1 (20 May 2009 10:16:48 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 20 May 2009 10:16:48 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: d7g2000prl.googlegroups.com; posting-host=60.50.69.246; posting-account=3FGPywkAAABXx2FS8FpxBLY_ym3T4qS5
User-Agent: G2/1.0
X-HTTP-UserAgent: Opera/9.64 (Windows NT 5.1; U; en) Presto/2.1.1,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!d7g2000prl.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27744
Article PK: 77031

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!!!!!

On May 20, 2:41 pm, rosdan <ros...@gmail.com> wrote:
> 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..
>
> On May 20, 12:14 pm, rosdan <ros...@gmail.com> wrote:
>
>
>
> > Thanks Bred,
>
> > I was hoping for a one-liner... :)
> > Looks like I need to write a stoed proc for that.
>
> > RT..
>
> > On May 20, 6:02 am, "Bret Halford [Sybase]" <b...@sybase.com> wrote:
>
> > > rosdan wrote:
> > > > 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"?
>
> > > 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


tartampion Posted on 2009-05-19 21:29:34.0Z
Sender: 32be.4a13098a.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a1324be.3640.1681692777@sybase.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 May 2009 14:29:34 -0700
X-Trace: forums-1-dub 1242768574 10.22.241.41 (19 May 2009 14:29:34 -0700)
X-Original-Trace: 19 May 2009 14:29:34 -0700, 10.22.241.41
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27738
Article PK: 77026

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

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


PDreyer Posted on 2009-05-20 08:16:52.0Z
From: PDreyer <petrus.dreyer@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
Date: Wed, 20 May 2009 01:16:52 -0700 (PDT)
Organization: http://groups.google.com
Lines: 50
Message-ID: <8f5e554c-e653-44a1-8cc1-fc949abbb7f7@n4g2000vba.googlegroups.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
NNTP-Posting-Host: 196.38.174.2
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242807412 24704 127.0.0.1 (20 May 2009 08:16:52 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 20 May 2009 08:16:52 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: n4g2000vba.googlegroups.com; posting-host=196.38.174.2; posting-account=uvRB_goAAABa1DQ8tZcD-HotUMfl3hu1
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!n4g2000vba.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27743
Article PK: 77030


On May 19, 3:55 pm, rosdan <ros...@gmail.com> wrote:
> 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.

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]


ThanksButNo Posted on 2009-05-21 09:12:09.0Z
From: ThanksButNo <no.no.thanks@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Group by Shifts
Date: Thu, 21 May 2009 02:12:09 -0700 (PDT)
Organization: http://groups.google.com
Lines: 89
Message-ID: <84661789-12c5-40e6-b6ae-68258fb996c6@n21g2000vba.googlegroups.com>
References: <367e8739-da75-4ffa-8eae-f6847198a42b@y10g2000prc.googlegroups.com>
NNTP-Posting-Host: 71.165.35.183
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1242897130 8009 127.0.0.1 (21 May 2009 09:12:10 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 21 May 2009 09:12:10 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: n21g2000vba.googlegroups.com; posting-host=71.165.35.183; posting-account=wjKAPwoAAABtEbTff5o9OO7GYdigbDts
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!n21g2000vba.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27749
Article PK: 77034


On May 19, 6:55 am, rosdan <ros...@gmail.com> wrote:
> 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.

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.

/:-/