Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: Help with Pivot?

    There is a second post to follow with the query

    I got a period-to-date report with thet following dates:
    --===== Current Year Dates
    Beginning of period: (BOP) Mon Aug 24
    Week 1: Mon Aug 24 - Aug 30
    Week 2: Mon Aug 11 - Sept 6
    Week 3: Mon Sep 7 - Sept 13
    Week 4: Mon Sept 14 - Sept 20
    Week 5: Mon Sept 21 - Sept 27
    Beginning of fiscal year( BOY) = '28-Dec-08'

    --===== Last Year Dates
    Beginning of period: (BOP_LY) Mon Aug 25
    Week 1: Mon Aug 25 - Aug 31
    Week 2: Mon Aug 1 - Sept 7
    Week 3: Mon Sep 8 - Sept 14
    Week 4: Mon Sept 15 - Sept 21
    Week 5: Mon Sept 22 - Sept 28
    Beginning of fiscal year( BOY) = '31-Dec-07'

    I can't get the last year next week sales column to work. The query I'm testing is commented out.

    Code:
    drop table my_csh_main;
     
     CREATE TABLE MY_CSH_MAIN
       (	"FK_STR_MAIN_ID" NUMBER, 
    	"BUSI_DATE" DATE, 
    	"CONF_NUMB2" NUMBER, 
    	"CONF_NUMB49" NUMBER, 
    	"CONF_NUMB44" NUMBER, 
    	"CONF_NUMB3" NUMBER, 
    	"CONF_NUMB4" NUMBER, 
    	"CONF_NUMB38" NUMBER, 
    	"CONF_NUMB56" NUMBER
       );
     
    REM INSERTING into MY_CSH_MAIN
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('28-AUG-08','DD-MON-RR'),22103.69,0,0,119,0,4605.21,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('27-AUG-09','DD-MON-RR'),18081.37,0,0,0,0,3533.45,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('17-SEP-09','DD-MON-RR'),18211.29,0,0,0,0,3806.32,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('04-SEP-08','DD-MON-RR'),24244.37,0,0,284.94,0,0,9395.63);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('03-SEP-09','DD-MON-RR'),18644.73,0,0,85.48,0,0,7228.13);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('24-SEP-09','DD-MON-RR'),16809.21,0,0,64.99,0,3014.61,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('25-SEP-08','DD-MON-RR'),24440.87,0,0,0,0,0,9469.64);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('28-AUG-08','DD-MON-RR'),23515.95,0,0,0,80.38,0,9379.9);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('24-SEP-09','DD-MON-RR'),20825.63,0,0,73.97,0,0,7958.53);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('17-SEP-09','DD-MON-RR'),18749.29,0,0,0,0,0,7699.53);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('11-SEP-08','DD-MON-RR'),22839.3,0,0,206.39,116.74,4493.28,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('04-SEP-08','DD-MON-RR'),22627.74,0,0,279.98,0,4423.83,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('27-AUG-09','DD-MON-RR'),18603.96,0,0,81.25,0,0,7118.17);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('11-SEP-08','DD-MON-RR'),23962.74,0,0,153.1,0,0,9335.35);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('18-SEP-08','DD-MON-RR'),23134.79,0,0,44.12,0,0,8978.87);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('25-SEP-08','DD-MON-RR'),24950.45,0,0,129.98,0,5330.22,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('10-SEP-09','DD-MON-RR'),19266.95,0,0,0,0,0,7657.94);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('03-SEP-09','DD-MON-RR'),17183.25,0,0,0,0,3487.12,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('18-SEP-08','DD-MON-RR'),21372.82,0,0,0,0,4546.15,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('10-SEP-09','DD-MON-RR'),17688.41,0,0,113.12,0,3424.17,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('31-DEC-08','DD-MON-RR'),611016.24,0,0,1276.62,724.96,122236.02,0);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('31-DEC-08','DD-MON-RR'),667612.63,0,0,1018.81,0,0,269777.87);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (141,to_date('02-JAN-08','DD-MON-RR'),1676737.13,0,0,5652.47,3850.68,345971.1,500.5);
    Insert into MY_CSH_MAIN (FK_STR_MAIN_ID,BUSI_DATE,CONF_NUMB2,CONF_NUMB49,CONF_NUMB44,CONF_NUMB3,CONF_NUMB4,CONF_NUMB38,CONF_NUMB56) values (221,to_date('02-JAN-08','DD-MON-RR'),1786451.11,0,0,3167.61,175.38,0,788438.73);
     
     
     
      CREATE TABLE  LANDAU_REPORT_STORES 
       (	"COMPANYNAME" CHAR(40 BYTE), 
    	"DISTRICTNAME" VARCHAR2(50 BYTE), 
    	"STOREID" NUMBER(4,0) NOT NULL ENABLE, 
    	"STORENAME" VARCHAR2(70 BYTE), 
    	"STORENBR" CHAR(4 BYTE) NOT NULL ENABLE
       )  
     
     
    REM INSERTING into LANDAU_REPORT_STORES
    Insert into LANDAU_REPORT_STORES (COMPANYNAME,DISTRICTNAME,STOREID,STORENAME,STORENBR) values ('Landau                                  ','DIST 10',64,'N_Main','0004');
    Insert into LANDAU_REPORT_STORES(COMPANYNAME,DISTRICTNAME,STOREID,STORENAME,STORENBR) values ('Landau                                  ','DIST 10',65,'Belvidere','0005');
    Insert into LANDAU_REPORT_STORES(COMPANYNAME,DISTRICTNAME,STOREID,STORENAME,STORENBR) values ('Landau                                  ','DIST 10',104,'Mulford','0032');
    Insert into LANDAU_REPORT_STORES(COMPANYNAME,DISTRICTNAME,STOREID,STORENAME,STORENBR) values ('Landau                                  ','DIST 50',141,'Charleston','0043');
    Insert into LANDAU_REPORT_STORES(COMPANYNAME,DISTRICTNAME,STOREID,STORENAME,STORENBR) values ('Landau                                  ','DIST 10',61,'Kilburn','0002');
    Insert into LANDAU_REPORT_STORES(COMPANYNAME,DISTRICTNAME,STOREID,STORENAME,STORENBR) values ('Landau                                  ','DIST 10',62,'11th_St','0003');

  2. #2
    Join Date
    Apr 2009
    Posts
    6

    Continued

    Code:
    with WeeklyTotals as 
       ( select                           StoreId
                                         , week_date
                                         , net_sales
                                         , ljs_food_sales
                                         , total_drink_sales
                                         , net_food
                                         , CASE
                                              WHEN net_food is null  then 0
                                              WHEN net_food  = 0  then 0
                                              ELSE Ljs_food_sales / net_food
                                           END as LJS_Percent
                                         , CASE
                                              WHEN net_food is null  then 0
                                              WHEN net_food = 0  then 0
                                              else total_drink_sales * (ljs_food_sales / net_food)
                                           END as LJS_Drinks
                                         , aw_sales
                                         , tb_sales
    		FROM                            (Select fk_str_main_id as StoreId, 
    		                                                                 trunc(csh.busi_date, 'IW') as week_date, 
                                                                         sum(csh.conf_numb2) As net_sales,
                                                                         sum(conf_numb49) as ljs_food_sales,
                                                                         sum(conf_numb44)  As total_drink_sales,
                                                                         sum(csh.conf_numb2) - sum(CONF_NUMB44) - sum(conf_numb3) - sum(conf_numb4) AS net_food,
                                                                         sum(conf_numb38) As aw_sales,
                                                                         sum(conf_numb56)  as tb_sales
                                                     from my_csh_main csh
                                                     WHERE BUSI_DATE BETWEEN  TO_DATE( '28-Dec-08' ,'DD-MON-YY') AND  TO_DATE( '27-SEP-09' ,'DD-MON-YY') 
                                                    and fk_str_main_id in (141, 221)
                                                    GROUP BY CSH.FK_STR_MAIN_ID,    trunc(csh.busi_date, 'IW')
                                               ) 
       )
     ,  WeeklyFoodSalesLY  as
     (         SELECT  fk_str_main_id AS storeid
                 ,   TRUNC(busi_date, 'iw') week_date
                 ,   sum(conf_numb2)   net_sales 
               FROM  my_csh_main csh     
               WHERE busi_date BETWEEN   to_date('31-dec-07', 'dd-Mon-yy')  and  to_date('22-Sep-08', 'dd-Mon-yy') + 7
               --and fk_str_main_id = 141   
               GROUP BY  fk_str_main_id , TRUNC(busi_date, 'iw')       
     )
     ---=====  This almost does it but does not give me all the records back I need 
     /*
     with   WeeklyFoodSalesLY  as
     (         SELECT  fk_str_main_id AS storeid
                 ,   TRUNC(busi_date, 'iw') week_date
                 ,   sum(conf_numb2)   net_sales 
               FROM  my_csh_main csh     
               WHERE busi_date BETWEEN   to_date('31-dec-07', 'dd-Mon-yy')  and  to_date('22-Sep-08', 'dd-Mon-yy') + 7
               --and fk_str_main_id = 141   
               GROUP BY  fk_str_main_id , TRUNC(busi_date, 'iw')       
     )
     ,  sales_ly as 
     (    select storeid,
                 week_date,
                 net_sales,
                 sum(net_sales) OVER (partition by storeid order by week_date) net_sales_ytd
          from   WeeklyFoodSalesLY
     ) 
    ,  pivoted_sales_ly as 
    (    SELECT          storeid, 
                         MAX(DECODE(week_date, to_date('25-Aug-08', 'dd-Mon-yy'),          net_sales))        as lastyear
                    ,    MAX(DECODE(week_date, to_date('25-Aug-08', 'dd-Mon-yy') + 7,      net_sales))        as lastyearnextweek
                    ,    MAX(DECODE(week_date, to_date('25-Aug-08', 'dd-Mon-yy'),          net_sales_ytd))        as lastyeartotal
          from sales_ly
          GROUP BY  storeid
     ) 
     */
      , StoreDetails  AS
     (
             select * from LANDAU_REPORT_STORES where STORENAME NOT like  '%CLOSED%'  order by DistrictNAme, StoreNbr
     )
       Select  
                 foods.storeid
              ,   CASE
                   WHEN   to_date(Foods.week_date, 'dd-Mon-yy')  =  to_date('24-AUG-09', 'dd-Mon-yy')  then 1
                   WHEN   to_date(Foods.week_date, 'dd-Mon-yy')  =  to_date('24-AUG-09', 'dd-Mon-yy') + 7 then 2
                   WHEN   to_date(Foods.week_date, 'dd-Mon-yy')  =  to_date('24-AUG-09', 'dd-Mon-yy') + 14 then 3
                   WHEN   to_date(Foods.week_date, 'dd-Mon-yy')  =  to_date('24-AUG-09','dd-Mon-yy') + 21 then 4
                   WHEN   to_date(Foods.week_date, 'dd-Mon-yy')  =  to_date('24-AUG-09', 'dd-Mon-yy') + 28 then 5
                   ELSE  0
             end as WeekNBr 
        ,   foods.week_date  as CurrWeekDate
        ,   foodsLY.week_date as LastYearWEekDate    
        ,   ROUND(NVL(foods.net_sales - (aw_sales + tb_sales + ljs_drinks + ljs_food_sales ), 0), 2) as Landau_Net_Sales
        ,   ROUND(NVL(aw_sales, 0), 2) as aw_sales
        ,   ROUND(NVL(tb_sales, 0), 2) as tb_sales
        ,   ROUND(NVL(ljs_drinks + ljs_food_sales, 0),2)    as ljs_net_sales
        ,   ROUND(NVL(foods.Net_Sales, 0), 2) as net_sales
        --==
        -- Last Year Sales and Last Year Next Year Sales
       --==
     ,    ROUND(NVL(foodsLY.Net_Sales, 0),2)    as                     last_year_sales
     ,    ROUND(NVL(Foods.Net_Sales -  FoodsLY.Net_Sales, 0),2)  as    WTDSalesIncrease
     ,    '???????'                                              as    last_year_sales_next_week   
     ,   stores.*
     from WEeklyTotals Foods  
     LEFT OUTER JOIN Weeklyfoodsalesly foodsly ON foodsly.storeid = foods.storeid
                                              AND foodsly.week_date = DECODE(foods.week_date,
                                                                             to_date('24-AUG-09', 'dd-Mon-yy') ,       to_date('25-AUG-08', 'dd-Mon-yy') ,
                                                                             to_date('24-AUG-09', 'dd-Mon-yy')  + 7,   to_date('25-AUG-08', 'dd-Mon-yy') + 7,
                                                                             to_date('24-AUG-09', 'dd-Mon-yy')  + 14,  to_date('25-AUG-08', 'dd-Mon-yy') + 14,
                                                                             to_date('24-AUG-09', 'dd-Mon-yy')  + 21,  to_date('25-AUG-08', 'dd-Mon-yy') + 21,
                                                                             to_date('24-AUG-09', 'dd-Mon-yy')  + 28,  to_date('25-AUG-08', 'dd-Mon-yy') + 28)
    LEFT OUTER  JOIN  StoreDetails             stores  ON     stores.storeid   = Foods.storeid;
    THIS IS THE OUTPUT I AM LOOKING FOR WITH THE WITH STATEMENT

    Code:
       STOREID WEEK_DATE  LAST_YEAR_SALES   LAST_YEAR_SALES_NEXTWEEK     
    ---------- --------- ----------        -------------------------
           141 31-DEC-07     ?                ?  
           141 25-AUG-08   22103.69         22,627.74  
           141 01-SEP-08   22627.74         22,839.30  
           141 08-SEP-08    22839.3          21372.82
           141 15-SEP-08   21372.82         24,950.45
           141 22-SEP-08   24950.45         22,584.21

    The 5 weeks starting with Aug 25 2008 represent the period info.
    There is also a YTD column on the report. where the amount of the 5 weeks(PTD) is added to the sum from the beginning of year to the end of the last
    period. I can not seem to make those work correctly. Here is an example of the report I am building the query for. I will be taking the data and massaging into a report like this just to give you a feel for the YTD amounts I am looking for. (The program will calculate the PTD so I am not concerned with it) (hopefully).
    Code:
    	        WEEK1       WEEK2           WEEK3         WEEK4             WEEK5       Period-to-date          Year-to-date
    Net - Landau	11,485.79   11,416.60 	11,609.01     11,049.76 	12,867.10 	58,428.00 	  	454,231.37 	 
    Net - AW	0.00 	         0.00	     0.00	   0.00	    	     0.00	     0.00 	        0.00
    Net - LJS	0.00 	       	 0.00 	     0.00 	   0.00 	     0.00 	    0.00	  	0.00
    Net - TB	7,118.17     7,228.13    7,657.94      7,699.53           7,958.53 	37,662.00 	 	306,115.59 	 
    Total Net	18,603.96    18,644.73 	19,266.95     18,749.29         20,825.63 	96,091.00 	 	760,346.96 	 
     
    Last Year Sales	23,515.95    24,244.37 	 23,962.74    23,134.79 	24,440.87 	119,299.00 		856,363.36 	
    Increase	(4,911.99)  (5,599.64)	 (4,695.79)  (4,385.50)	 	(3,615.24)	(23,208.00)	 	(96,016.40)	 
    Last year 
      Next Week	24,244.37    23,962.74 	 23,134.79    24,440.87 	23,055.87 	118,839.00 		879,419.23
    Any help is appreciated. Thanks.
    Last edited by row118; 10-28-09 at 20:53.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •