Subject: Re: Cumulative result with increment
From: Shawn Tayler <stayler@washoecounty.us>
Reply-To: stayler@washoecounty.us
To: Steve Crawford <scrawford@pinpointresearch.com>,
	pqsql list <pgsql-sql@postgresql.org>
In-Reply-To: <4D506E8A.30107@pinpointresearch.com>
References: <1297113095.21916.76.camel@shop.telecom.co.washoe.nv.us>
	<4D506E8A.30107@pinpointresearch.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Organization: Washoe County Regional Communications System
Date: Tue, 08 Feb 2011 10:09:50 -0800
Message-ID: <1297188590.32163.1.camel@shop.telecom.co.washoe.nv.us>
Mime-Version: 1.0

Thanks Steve,

That did the trick!

I appreciate the help....

Shawn


On Mon, 2011-02-07 at 14:13 -0800, Steve Crawford wrote:
> On 02/07/2011 01:11 PM, Shawn Tayler wrote:
> > Hello,
> >
> > I am struggling with what is probably a simple problem but I am coming
> > up blank.  In the interest of full disclosure I am not a very savy
> > programmer by any stretch.
> >
> > I have a table of data from an external source which contains numbers o=
f
> > events per operating group per hour per day, kind of like this:
> >
> > Group |	events  | duration  | timestamp
> >
> > The Group, events, and duration (milliseconds) are all integers in char
> > format.  Timestamp is as stated.  The records are at 1 hour increments.
> > I need to sum the events and duration fields in 8 hour (shifts, mid to
> > 8AM, 8AM to 4PM, etc).
> >
> > Id like the output to be in table format ala:
> >
> > Group | events  |  duration  |   date    | shift
> > ------+---------+------------+-----------+-------
> > 100   |  26     |00:00:25.00 |2011-01-01 | Day=09
> >
> > I have a function to convert the duration to an interval already and ca=
n
> > make one to do the shift labeling.  Its the rest that has me stumped.
> >
> > Any suggestions or direction?
> >
> > As always, your help is greatly appreciated.
> >
>=20
> I'm not sure exactly what you want but it sounds like you just want an=20
> output column that has the shift instead of timestamp. You can then sum=20
> on that column. Don't know what your shifts are called, but this will=20
> give you dog-, day- and night-shifts based on your times:
>=20
> case
> when extract(hour from '2011-01-12 1600'::timestamptz)< 8 then 'dog'
> when extract(hour from now())< 16 then 'day'
> else 'night'
> end as shiftname
>=20
> This can be used for grouping as well as display.
>=20
> Cheers,
> Steve
>=20