Re: SQL help!

From: Jerry Gitomer <jgitomer_at_hbsrx.com>
Date: Mon, 19 Jul 1999 12:22:53 -0400
Message-ID: <7mvjbh$54d$1@autumn.news.rcn.net>

Hi Ken,

    If I understand your problem correctly the following should do the trick.

SELECT ty.week_nbr,

        ty.week_dsc,
        ty.store_nbr,
        ty.store_name,
        ly.sum(nvl(regsales,0)) as ly_reg_sales,
        ty.sum(nvl(regsales,0)) as ty_reg_sales,
        ly.sum(nvl(regunits,0)) as ly_reg_units,
        ty.sum(nvl(regunits,0)) as ty_reg_units,
        ly.sum(nvl(promosales,0)) as ly_promosales,
        ty.sum(nvl(promosales,0)) as ty_promosales,
        ly.sum(nvl(promounits,0)) as ly_promounits,
        ty.sum(nvl(promounits,0)) as ty_promounits
 FROM   RSNET.JC_STORE_BY_WEEK ty,
        RSNET.JC_STORE_BY_WEEK ly
 WHERE  ty.week_nbr >= 199828
        AND ty.week_nbr <=199927
        AND ly.week_nbr = ty.week_nbr + 100
        AND ty.store_nbr = ly.store_nbr

GROUP BY ty.week_nbr, ty.week_dsc, ty.store_nbr, ty.store_name  ORDER BY ty.week_nbr, ty.store_nbr

regards
Jerry Gitomer

---

KTL wrote in message <37932714.16EA1282_at_rs-net.com>...
>I need to return a result set for this year/last year data.. all
from
>the same table..
>I need the information in the same line..
>How do I do this in Oracle?
>
>SELECT week_nbr,
> week_dsc,
> store_nbr,
> store_name,
> sum(nvl(regsales,0)) as ty_reg_sales,
> null,
> sum(nvl(regunits,0)) as ty_reg_units,
> null,
> sum(nvl(promosales,0)) as ty_promosales,
> null,
> sum(nvl(promounits,0)) as ty_promounits,
> null
> FROM RSNET.JC_STORE_BY_WEEK
> WHERE week_nbr >= 199828 and week_nbr <=199927
>
> UNION
>
> SELECT week_nbr,
> week_dsc,
> store_nbr,
> store_name,
> null,
> sum(nvl(regsales,0)) as ly_reg_sales,
> null,
> sum(nvl(regunits,0)) as ly_reg_units,
> null,
> sum(nvl(promosales,0)) as ly_promosales,
> null,
> sum(nvl(promounits,0)) as ly_promounits
> FROM RSNET.JC_STORE_BY_WEEK
> WHERE week_nbr >= 199728 and week_nbr <=199827
> GROUP BY week_nbr,week_dsc,store_nbr,store_name
> ORDER BY week_nbr,store_nbr
>
>The nulls are there as place holders so the other query can fill
the
>value...
>
>
>suggestions?
>-Ken
>
Received on Mon Jul 19 1999 - 11:22:53 CDT