Re: Selecting and ordering data over multiple date ranges

[steves]

I was thinking you might be able to get the headings as desired by using in-line views if your DB supports them. I tried this sort of thing against an Oracle DB here. But it was taking so long I killed it:

SELECT o.state, dec.total, jan.total, feb.total, count(*)
FROM
(
    SELECT state, to_char(order_date, 'Mon'), count(*) total
    FROM web_order
    WHERE order_date BETWEEN '01-DEC-02' AND '31-DEC-02'
    GROUP BY state, to_char(order_date, 'Mon')
) dec,
(
    SELECT state, to_char(order_date, 'Mon'), count(*) total
    FROM web_order
    WHERE order_date BETWEEN '01-JAN-03' AND '31-JAN-03'
    GROUP BY state, to_char(order_date, 'Mon')
) jan,
(
    SELECT state, to_char(order_date, 'Mon'), count(*) total
    FROM web_order
    WHERE order_date BETWEEN '01-FEB-03' AND '28-FEB-03'
    GROUP BY state, to_char(order_date, 'Mon')
) feb,
web_order o
WHERE o.order_date BETWEEN '01-DEC-02' AND '28-FEB-03'
GROUP BY o.state, dec.total, jan.total, feb.total

As an alternative, it may be easier to do the state/month grouping and reformat yourself after getting the rows the DB hands back. I personally find Perl much better at more complex reformatting than most DB's.

[peppiv]

Yeah, I may try the state/month grouping and reformat with Perl. Friday's never a good day to have to use your brain.

Thanks steves

peppiv