Re: Joining time fields?

On 07/26/2012 12:14 PM, Oliveiros d'Azevedo Cristina wrote:
> Howdy, Bryan,
> I am not familiar with many constructs you employed, like crossed,
> grouped and using.
> What is the version of PostGreSQL you are using? I'm still using 8.3
> If your query works and your print out does prove so, then you found
> James an elegant solution.
> I'm an old dog but I must find the time to upgrade myself, I'm missing
> lots of constructs. I'm always trying to do queries with the concepts
> I know and I'm
> aware that probably new versions bring new constructs that can make in
> one line what I do in 10 and in a much more efficient way.

The construct you want to research is the common-table-expression (CTE).

A common table expression acts sort of like a temporary table or view
that is created just for the duration of that query. The "with crossed
as (...)" and "with grouped as (...)" make temporary "tables" (table
expressions) called "crossed" and "grouped" that are referenced in the
final part of the query as though they were tables. "Crossed" and
"grouped" are just names assigned to the CTEs, not new constructs.

An exciting capability provided through the use of CTEs is the ability
to write recursive queries which greatly simplifies dealing with
hierarchical data like org-charts.

Common table expressions
(http://www.postgresql.org/docs/current/static/queries-with.html) along
with windowing functions
(http://www.postgresql.org/docs/current/static/tutorial-window.html) are
two very useful features that were introduced in version 8.4
(http://www.postgresql.org/docs/8.4/static/release-8-4.html).

Go forth and upgrade! (8.3 is EOL in a few months, anyway.)

Cheers,
Steve