Re: Decode a range?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 15 Sep 1999 15:11:11 +0100
Message-ID: <937405071.7748.0.nnrp-13.9e984b29@news.demon.co.uk>

It looks as if you are using 14 days of daily data to get a 'this week'/'last week' comparison, so you could play with turning the date into the week.

Since your choice of days falls across a 'standard' week you need to massage the numbers slightly but something like
sum(

    decode(

        to_char(
            to_date(
                period_key,'yyyymmdd')-1,
                'YYYYIW'
            ),
            '199926',    total_dollars,
                            0

    )
)

i.e. turn the key into a date.
subtract one day (in this example) so the first date of your range is a Monday. Turn the date into Y/ISO Week format. Check week number against required week.

Dates from 19990629 to 19990705 will traslate into the value 199926, and dates from 19990706 to 19990712 will translate to 199927 (unless my arithmetic has gone wrong).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Ken Leach wrote in message <37DF99FC.3ABE553D_at_rs-net.com>...
>I am trying to make a normalized table horizontal..
>
>I have a HUGE query that UNIONS everthing, it works but has problems
>with parallel query... SO... I would like to make the same query work
>with decode or some other method than UNION...
>
>select i.upc_id, i.item_desc, i.item_key, i.category, i.brand,
> sum(decode(p.period_key,between 19970629 and
>19970705,p.total_dollars,0) as week1,
> sum(decode(p.period_key,between 19970706 and
>19970712,p.total_dollars,0) as week2
>from pos p, item_dim i
>where p.vendor_key = 38 and p.summary_key = 1 and p.retailer_key = 1
>and p.period_key >=1990629 and p.period_key <= 19970712
>and p.vendor_id = 2145
>and p.item_key = i.item_key
>group by i.upc_id, i.item_desc, i.item_key, i.category, i.brand
>
Received on Wed Sep 15 1999 - 09:11:11 CDT