Re: Can I simplify this somehow?

Are you looking for something like this for the result for the last 45 days
or something else?

TIME MON TUE WED ...
09:00 1 3 4
10:00 5 0 8

On Fri, Sep 27, 2013 at 10:22 AM, Larry Rosenman <ler(at)lerctr(dot)org> wrote:

> I tried(!) to write this as a with (CTE), but failed.
>
> Can one of the CTE experts (or better SQL writer) help me here?
>
> -- generate a table of timestamps to match against
> select
> generate_series(date_trunc('**day',now()-'45 days'::interval),now()+'1
> hour'::inte
> rval,'1 hour')
> AS thetime into temp table timestamps;
>
> -- get a count of logged in users for a particular time
> SELECT thetime,case extract(dow from thetime)
> when 0 then 'Sunday'
> when 1 then 'Monday'
> when 2 then 'Tuesday'
> when 3 then 'Wednesday'
> when 4 then 'Thursday'
> when 5 then 'Friday'
> when 6 then 'Saturday' end AS "Day", count(*) AS "#LoggedIn"
> FROM timestamps,user_session
> WHERE thetime BETWEEN login_time AND COALESCE(logout_time, now())
> GROUP BY thetime
> ORDER BY thetime;
>
> Thanks for any help at all.
>
>
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 214-642-9640 (c) E-Mail: ler(at)lerctr(dot)org
> US Mail: 108 Turvey Cove, Hutto, TX 78634-5688
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql>
>