Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: Selecting and ordering data over multiple date ranges

by steves (Curate)
on Feb 07, 2003 at 16:25 UTC ( #233511=note: print w/replies, xml ) Need Help??


in reply to Selecting and ordering data over multiple date ranges

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.

Replies are listed 'Best First'.
Re: Re: Selecting and ordering data over multiple date ranges
by peppiv (Curate) on Feb 07, 2003 at 16:45 UTC
    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://233511]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (5)
As of 2021-06-17 00:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (82 votes). Check out past polls.

    Notices?