Solved

if statement to run query

Posted on 2008-06-17
8
217 Views
Last Modified: 2013-11-16
hi everyone,

i have the script to do a management report, i have added a field in that report for season which is the first field in the report, as this is an attribute to a product code, it can be sometime NULL, but when its NULL it doesnt show those styles in the report

what i want is eveen if that attribute is null it should show the style still with all the calculations.


please help
CREATE procedure LTS_STYLE_PERFORMAnCE_REPORT_PAUL      

      

@Season  varchar(50) = '',      

@FROM_WEEK int,      

@TO_Week int,      

@LY_START_WEEK int,      

@LY_END_WEEK int      

      

      

as begin      

      

if @season = ''      

SELECT         

--pa.anal_desc as SEASON,    

          al.level_desc as DEPT_CATEGORY,      

          pm.alt_code1 as STYLE,      

          c.colour_desc as COLOUR,       

          pm.description as DESCRIPTION,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      

         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,      

      

'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code) else 0 end),      

      

/************START OF RETAIL FIELDS************/      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      

              

'VALUE' = SUM(CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')

THEN (ph.sales_spvalue + ph.refnd_disc_value) - (ph.REFND_spvalue + ph.sales_disc_value) ELSE 0 END),      

      

'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           --AND pms.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+ Convert(Varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                    AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),0)),      

      

      

--   'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

-- else       

-- SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','20      

-- 8','209','210','211','212','213','214','215') THEN ph.Sales_units     

      

      

      

'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

else       

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      

      

SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      

      

'STORE_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'TOTAL_RETAIL_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

      

      

/************START OF MAIL_ORDER FIELDS************/      

      

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       

      

'VALUE'=CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           --AND pmsw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+CONVERT(VARCHAR,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           --AND pmssw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      

      

              

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      

      

'MO_COVER' =    convert(decimal(18,1),    

case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

/************START OF TOTAL FIELDS************/      

      

      

'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      

      

'VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      

      

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           --AND pmsw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

       AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp) +       

      

(SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           --AND pms.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           --AND pmssw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp) +      

      

(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                        branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week       

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      

      

      

      

      

'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','098','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),

      

      

'TOTAL_COVER' =convert(decimal(18,1), case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector = @to_week  - 1      

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',

  

    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),      

      

      

'OVERALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      

                     FROM  PRODUCT_HISTORY PHSS,        

                           PRODUCT_MASTER PMSS,        

                           PRODUCT_DETAIL PDSS,        

                           SIZES SSS,        

                           COLOUR CSS,        

                           BRANCH BSS,        

                           ANAL_LEVEL ALSS      

                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      

                           AND PHSS.SKU_ID = PDSS.SKU_ID       

                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        

                           AND ALSS.J_CODE = PMSS.J_CODE_LINK        

                           AND ALSS.LEVEL_NO = 4        

                           AND SSS.SIZES_ID = PDSS.SIZES_ID        

                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        

                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033' 

 

,    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        

                           --AND PMSS.ALT_CODE1 LIKE '%Q%'      

                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      

                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      

                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      

                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      

                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      

                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      

                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      

      

      

       

        

FROM      product_history ph,        

          product_master pm,        

          product_detail pd,        

          SIZES s,        

          colour c,        

          branch b,        

          anal_level al  

--product_anal pa,    

--product_anal_types pat      

        

WHERE     ph.week_selector = @to_week      

          AND ph.sku_id = pd.sku_id        

          AND pd.product_id = pm.prod_id        

          AND al.j_code = pm.j_code_link        

          AND al.level_no = 4        

          AND s.sizes_id = pd.sizes_id        

          AND c.colour_id = pd.colour_id     

    --    AND pa.anal_type = pat.anal_type    

  --      AND pa.anal_id = pm.anal_id_3       

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   

          AND b.branch_id = ph.branch_id        

         --AND pm.alt_code1 like '%Q%'      

               

          

GROUP BY        

          pm.alt_code1,      

          c.colour_desc,       

          al.level_desc,      

          pd.original_sp,      

          pd.current_sp,      

          pm.description,      

          Pd.current_cp  

  --        pa.anal_desc       

order by        

          pm.alt_code1      

      

      

      

ELSE      

      

SELECT        

-- pa.anal_desc as SEASON,     

          al.level_desc as DEPT_CATEGORY,      

          pm.alt_code1 as STYLE,      

          c.colour_desc as COLOUR,       

          pm.description as DESCRIPTION,      

           CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      

         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,     

    

/************START OF RETAIL FIELDS************/      

      

'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 else 0 end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      

              

'VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),       

      

'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           AND als.level1_desc = @season      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                  AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp)*1,0)),      

      

      

      

      

      

      

'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

else       

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      

      

SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      

      

'STORE_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'TOTAL_RETAIL_COVER' =  convert(decimal(18,1),      

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

/************START OF MAIL_ORDER FIELDS************/      

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       

      

'VALUE'=CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           AND alsw.level1_desc = @season      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           AND alssw.level1_desc = @season      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      

      

              

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      

      

'MO_COVER' =    convert(decimal(18,1),    

case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

      

/************START OF TOTAL FIELDS************/      

      

'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','

  

    

20      

8','209','210','211','212','213','214','215') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      

      

'VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','03

  

1','032','033','034','035','207','    

20      

8','209','210','211','212','213','214','215') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      

      

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           AND alsw.level1_desc = @season      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

          AND Pd.current_cp=Pdsw.current_cp) +       

      

(SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           AND als.level1_desc = @season      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           AND alssw.level1_desc = @season      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp) +      

      

(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week     

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      

      

      

      

      

'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210','098')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),      

      

'TOTAL_COVER' = convert(decimal(18,1),case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector = @to_week  - 1      

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                   AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),      

      

      

'OVER_ALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      

                     FROM  PRODUCT_HISTORY PHSS,        

                           PRODUCT_MASTER PMSS,        

                           PRODUCT_DETAIL PDSS,        

                           SIZES SSS,        

                           COLOUR CSS,        

                           BRANCH BSS,        

                           ANAL_LEVEL ALSS      

                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      

                           AND PHSS.SKU_ID = PDSS.SKU_ID       

                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        

                         AND ALSS.J_CODE = PMSS.J_CODE_LINK        

                           AND ALSS.LEVEL_NO = 4        

                           AND SSS.SIZES_ID = PDSS.SIZES_ID        

                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        

                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',

  

    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        

                           AND alss.level1_desc = @season      

                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      

                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      

                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      

                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      

                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      

                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      

                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      

      

      

       

        

FROM      product_history ph,        

          product_master pm,        

          product_detail pd,        

          SIZES s,        

          colour c,        

          branch b,        

          anal_level al

-- product_anal pa,    

-- product_anal_types pat          

        

WHERE     ph.week_selector = @to_week      

          AND ph.sku_id = pd.sku_id        

          AND pd.product_id = pm.prod_id        

          AND al.j_code = pm.j_code_link        

          AND al.level_no = 4        

          AND s.sizes_id = pd.sizes_id        

          AND c.colour_id = pd.colour_id      

-- AND pa.anal_type = pat.anal_type    

 --AND pa.anal_id = pm.anal_id_3       

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','

     

208','209','210','211','212','213','214','215')        

          AND b.branch_id = ph.branch_id        

          AND al.level1_desc = @season      

               

          

GROUP BY        

          pm.alt_code1,      

          c.colour_desc,       

          al.level_desc,      

          pd.original_sp,      

          pd.current_sp,      

          pm.description,      

          Pd.current_cp  

 --pa.anal_desc      

order by        

          pm.alt_code1      

      

end

GO

Open in new window

0
Comment
Question by:ammartahir1978
  • 3
  • 3
  • 2
8 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
Comment Utility
I am not sure I understand what happens when season (pa.anal_desc) is null and what you want to happen if it is.
0
 
LVL 2

Expert Comment

by:matty1stop
Comment Utility
Your question is a little confusing but I'm gonna take a guess and say that you probably want to do a left join to the "pa" table instead of joining the tables in your where clauses
Try changing your from and where clauses to this.
 

One important note:  You need a join on the branch table
 

FROM      product_history ph

join 	product_detail pd

on 	ph.sku_id = pd.sku_id   

join 	roduct_master pm

on 	pd.product_id = pm.prod_id  

join 	SIZES S

on	s.sizes_id = pd.sizes_id

join 	colour c,

on 	c.colour_id = pd.colour_id

--join	branch b

join 	anal_level al 

on 	al.j_code = pm.j_code_link 

left ouer join product_anal_types pat   

on 	pa.anal_id = pm.anal_id_3

left ouer join 	product_anal pa

on	pa.anal_type = pat.anal_type 

WHERE     ph.week_selector = @to_week      

          AND al.level_no = 4        

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   

          AND pm.alt_code1 like '%Q%'   

Open in new window

0
 

Author Comment

by:ammartahir1978
Comment Utility
HI matty1stop:

thanks for that can you please tell me where to put this code in i have posted the whole script is it possible to correct that?
0
 

Author Comment

by:ammartahir1978
Comment Utility
just to explain my question again:

i have this script which gives me correct values if i disable the PA.ANAL_DESC and also the link of
PA.ANAL_TYPE and PAT.ANAL_TYPE
pa.anal_id = pm.anal_id_3

but as soon as i switch this link back on the values changes as some of the products doesnt have
anal_desc attached.
so what i want is even though there is no anal_desc attached query should still calculate them.

hope this explain
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 13

Expert Comment

by:Philip Pinnell
Comment Utility
I think as matty says you need a left outer join to product_anal and probably product_anal_types

BTW watch out for the typo ouer=outer
0
 
LVL 2

Accepted Solution

by:
matty1stop earned 500 total points
Comment Utility
Try running this.

Corrected the typo, thanks andycrofts
CREATE procedure LTS_STYLE_PERFORMAnCE_REPORT_PAUL      

      

@Season  varchar(50) = '',      

@FROM_WEEK int,      

@TO_Week int,      

@LY_START_WEEK int,      

@LY_END_WEEK int      

      

      

as begin      

      

if @season = ''      

SELECT         

--pa.anal_desc as SEASON,    

          al.level_desc as DEPT_CATEGORY,      

          pm.alt_code1 as STYLE,      

          c.colour_desc as COLOUR,       

          pm.description as DESCRIPTION,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      

         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,      

      

'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code) else 0 end),      

      

/************START OF RETAIL FIELDS************/      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      

              

'VALUE' = SUM(CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')

THEN (ph.sales_spvalue + ph.refnd_disc_value) - (ph.REFND_spvalue + ph.sales_disc_value) ELSE 0 END),      

      

'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           --AND pms.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+ Convert(Varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                    AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),0)),      

      

      

--   'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

-- else       

-- SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','20      

-- 8','209','210','211','212','213','214','215') THEN ph.Sales_units     

      

      

      

'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

else       

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      

      

SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      

      

'STORE_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'TOTAL_RETAIL_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

      

      

/************START OF MAIL_ORDER FIELDS************/      

      

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       

      

'VALUE'=CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           --AND pmsw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+CONVERT(VARCHAR,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           --AND pmssw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      

      

              

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      

      

'MO_COVER' =    convert(decimal(18,1),    

case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

/************START OF TOTAL FIELDS************/      

      

      

'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      

      

'VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      

      

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           --AND pmsw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

       AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp) +       

      

(SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           --AND pms.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           --AND pmssw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp) +      

      

(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                        branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week       

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      

      

      

      

      

'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','098','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),

      

      

'TOTAL_COVER' =convert(decimal(18,1), case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector = @to_week  - 1      

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',

  

    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),      

      

      

'OVERALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      

                     FROM  PRODUCT_HISTORY PHSS,        

                           PRODUCT_MASTER PMSS,        

                           PRODUCT_DETAIL PDSS,        

                           SIZES SSS,        

                           COLOUR CSS,        

                           BRANCH BSS,        

                           ANAL_LEVEL ALSS      

                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      

                           AND PHSS.SKU_ID = PDSS.SKU_ID       

                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        

                           AND ALSS.J_CODE = PMSS.J_CODE_LINK        

                           AND ALSS.LEVEL_NO = 4        

                           AND SSS.SIZES_ID = PDSS.SIZES_ID        

                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        

                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033' 

 

,    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        

                           --AND PMSS.ALT_CODE1 LIKE '%Q%'      

                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      

                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      

                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      

                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      

                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      

                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      

                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      

      

      

       

        

FROM      product_history ph,        

          product_master pm,        

          product_detail pd,        

          SIZES s,        

          colour c,        

          branch b,        

          anal_level al  

--product_anal pa,    

--product_anal_types pat      

        

WHERE     ph.week_selector = @to_week      

          AND ph.sku_id = pd.sku_id        

          AND pd.product_id = pm.prod_id        

          AND al.j_code = pm.j_code_link        

          AND al.level_no = 4        

          AND s.sizes_id = pd.sizes_id        

          AND c.colour_id = pd.colour_id     

    --    AND pa.anal_type = pat.anal_type    

  --      AND pa.anal_id = pm.anal_id_3       

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   

          AND b.branch_id = ph.branch_id        

         --AND pm.alt_code1 like '%Q%'      

               

          

GROUP BY        

          pm.alt_code1,      

          c.colour_desc,       

          al.level_desc,      

          pd.original_sp,      

          pd.current_sp,      

          pm.description,      

          Pd.current_cp  

  --        pa.anal_desc       

order by        

          pm.alt_code1      

      

      

      

ELSE      

      

SELECT        

-- pa.anal_desc as SEASON,     

          al.level_desc as DEPT_CATEGORY,      

          pm.alt_code1 as STYLE,      

          c.colour_desc as COLOUR,       

          pm.description as DESCRIPTION,      

           CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      

         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,     

    

/************START OF RETAIL FIELDS************/      

      

'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 else 0 end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      

              

'VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),       

      

'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           AND als.level1_desc = @season      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                  AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp)*1,0)),      

      

      

      

      

      

      

'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

else       

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      

      

SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      

      

'STORE_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'TOTAL_RETAIL_COVER' =  convert(decimal(18,1),      

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

/************START OF MAIL_ORDER FIELDS************/      

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       

      

'VALUE'=CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           AND alsw.level1_desc = @season      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           AND alssw.level1_desc = @season      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      

      

              

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      

      

'MO_COVER' =    convert(decimal(18,1),    

case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

      

/************START OF TOTAL FIELDS************/      

      

'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','

  

    

20      

8','209','210','211','212','213','214','215') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      

      

'VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','03

  

1','032','033','034','035','207','    

20      

8','209','210','211','212','213','214','215') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      

      

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           AND alsw.level1_desc = @season      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

          AND Pd.current_cp=Pdsw.current_cp) +       

      

(SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           AND als.level1_desc = @season      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           AND alssw.level1_desc = @season      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp) +      

      

(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week     

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      

      

      

      

      

'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210','098')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),      

      

'TOTAL_COVER' = convert(decimal(18,1),case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector = @to_week  - 1      

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                   AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),      

      

      

'OVER_ALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      

                     FROM  PRODUCT_HISTORY PHSS,        

                           PRODUCT_MASTER PMSS,        

                           PRODUCT_DETAIL PDSS,        

                           SIZES SSS,        

                           COLOUR CSS,        

                           BRANCH BSS,        

                           ANAL_LEVEL ALSS      

                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      

                           AND PHSS.SKU_ID = PDSS.SKU_ID       

                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        

                         AND ALSS.J_CODE = PMSS.J_CODE_LINK        

                           AND ALSS.LEVEL_NO = 4        

                           AND SSS.SIZES_ID = PDSS.SIZES_ID        

                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        

                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',

  

    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        

                           AND alss.level1_desc = @season      

                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      

                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      

                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      

                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      

                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      

                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      

                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      

      

      

       

        

FROM      product_history ph

join 	product_detail pd

on 	ph.sku_id = pd.sku_id   

join 	roduct_master pm

on 	pd.product_id = pm.prod_id  

join 	SIZES S

on	s.sizes_id = pd.sizes_id

join 	colour c,

on 	c.colour_id = pd.colour_id

join	branch b

on	b.branch_id = ph.branch_id 

join 	anal_level al 

on 	al.j_code = pm.j_code_link 

left outer join product_anal_types pat   

on 	pa.anal_id = pm.anal_id_3

left outer join 	product_anal pa

on	pa.anal_type = pat.anal_type 

WHERE     ph.week_selector = @to_week      

          AND al.level_no = 4        

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   

          AND pm.alt_code1 like '%Q%'   
 

          

GROUP BY        

          pm.alt_code1,      

          c.colour_desc,       

          al.level_desc,      

          pd.original_sp,      

          pd.current_sp,      

          pm.description,      

          Pd.current_cp  

 --pa.anal_desc      

order by        

          pm.alt_code1      

      

end

GO

 

Open in New Window Tags: 

microsoft, sql server, 2000 Zones: 

SQL Server 2005, MySQL, SQLBase

Open in new window

0
 

Author Comment

by:ammartahir1978
Comment Utility
this is what i did with the script but when i past it in enterprise manager it gives an error

say the prefix pa doesnt match with the table alias
CREATE procedure LTS_STYLE_PERFORMAnCE_REPORT_PAUL      

      

@Season  varchar(50) = '',      

@FROM_WEEK int,      

@TO_Week int,      

@LY_START_WEEK int,      

@LY_END_WEEK int      

      

      

as begin      

      

if @season = ''      

SELECT         

--pa.anal_desc as SEASON,    

          al.level_desc as DEPT_CATEGORY,      

          pm.alt_code1 as STYLE,      

          c.colour_desc as COLOUR,       

          pm.description as DESCRIPTION,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      

         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,      

      

'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code) else 0 end),      

      

/************START OF RETAIL FIELDS************/      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      

              

'VALUE' = SUM(CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')

THEN (ph.sales_spvalue + ph.refnd_disc_value) - (ph.REFND_spvalue + ph.sales_disc_value) ELSE 0 END),      

      

'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           --AND pms.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+ Convert(Varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                    AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),0)),      

      

      

--   'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

-- else       

-- SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','20      

-- 8','209','210','211','212','213','214','215') THEN ph.Sales_units     

      

      

      

'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

else       

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      

      

SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      

      

'STORE_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'TOTAL_RETAIL_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

      

      

/************START OF MAIL_ORDER FIELDS************/      

      

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       

      

'VALUE'=CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           --AND pmsw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+CONVERT(VARCHAR,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           --AND pmssw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      

      

              

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      

      

'MO_COVER' =    convert(decimal(18,1),    

case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

/************START OF TOTAL FIELDS************/      

      

      

'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      

      

'VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      

      

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           --AND pmsw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

       AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp) +       

      

(SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           --AND pms.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           --AND pmssw.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp) +      

      

(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                        branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week       

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      

      

      

      

      

'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','098','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),

      

      

'TOTAL_COVER' =convert(decimal(18,1), case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector = @to_week  - 1      

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',

  

    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND bss.branch_id = phss.branch_id        

                           --AND pmss.alt_code1 like '%Q%'      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),      

      

      

'OVERALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      

                     FROM  PRODUCT_HISTORY PHSS,        

                           PRODUCT_MASTER PMSS,        

                           PRODUCT_DETAIL PDSS,        

                           SIZES SSS,        

                           COLOUR CSS,        

                           BRANCH BSS,        

                           ANAL_LEVEL ALSS      

                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      

                           AND PHSS.SKU_ID = PDSS.SKU_ID       

                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        

                           AND ALSS.J_CODE = PMSS.J_CODE_LINK        

                           AND ALSS.LEVEL_NO = 4        

                           AND SSS.SIZES_ID = PDSS.SIZES_ID        

                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        

                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033' 

 

,    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        

                           --AND PMSS.ALT_CODE1 LIKE '%Q%'      

                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      

                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      

                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      

                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      

                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      

                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      

                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      

      

      

       

        

FROM    product_history ph

join 	product_detail pd

on 	ph.sku_id = pd.sku_id   

join 	product_master pm

on 	pd.product_id = pm.prod_id  

join 	SIZES S

on	s.sizes_id = pd.sizes_id

join 	colour c

on 	c.colour_id = pd.colour_id

join	branch b

on	b.branch_id = ph.branch_id 

join 	anal_level al 

on 	al.j_code = pm.j_code_link 

left outer join product_anal_types pat   

on 	pa.anal_id = pm.anal_id_3

left outer join 	product_anal pa

on	pa.anal_type = pat.anal_type 

WHERE     ph.week_selector = @to_week      

          AND al.level_no = 4        

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   

          

GROUP BY        

          pm.alt_code1,      

          c.colour_desc,       

          al.level_desc,      

          pd.original_sp,      

          pd.current_sp,      

          pm.description,      

          Pd.current_cp , 

         pa.anal_desc       

order by        

          pm.alt_code1      

      

      

      

ELSE      

      

SELECT        

pa.anal_desc as SEASON,     

          al.level_desc as DEPT_CATEGORY,      

          pm.alt_code1 as STYLE,      

          c.colour_desc as COLOUR,       

          pm.description as DESCRIPTION,      

           CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      

          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      

         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,     

    

/************START OF RETAIL FIELDS************/      

      

'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 else 0 end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      

              

'VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),       

      

'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           AND als.level1_desc = @season      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                  AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp)*1,0)),      

      

      

      

      

      

      

'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      

else       

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      

      

      

SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      

      

SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      

      

'STORE_COVER' = convert(decimal(18,1),       

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'TOTAL_RETAIL_COVER' =  convert(decimal(18,1),      

case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

/************START OF MAIL_ORDER FIELDS************/      

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       

      

'VALUE'=CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           AND alsw.level1_desc = @season      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

                           AND Pd.current_cp=Pdsw.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           AND alssw.level1_desc = @season      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      

      

              

      

SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      

      

'MO_COVER' =    convert(decimal(18,1),    

case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

      

/************START OF TOTAL FIELDS************/      

      

'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','

  

    

20      

8','209','210','211','212','213','214','215') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      

      

'VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','03

  

1','032','033','034','035','207','    

20      

8','209','210','211','212','213','214','215') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      

      

      

'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      

                     FROM  product_history phsw,        

                           product_master pmsw,        

                           product_detail pdsw,        

                           SIZES ssw,        

                           colour csw,        

                           branch bsw,        

                           anal_level alsw      

                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       

                           AND phsw.sku_id = pdsw.sku_id        

                           AND pdsw.product_id = pmsw.prod_id        

                           AND alsw.j_code = pmsw.j_code_link        

                           AND alsw.level_no = 4        

                           AND ssw.sizes_id = pdsw.sizes_id        

                           AND csw.colour_id = pdsw.colour_id        

                           AND bsw.BRANCH_CODE IN ('999')      

                           AND bsw.branch_id = phsw.branch_id        

                           AND alsw.level1_desc = @season      

                           AND pm.alt_code1=pmsw.alt_code1      

                           AND c.colour_desc=csw.colour_desc      

                           AND al.level_desc=alsw.level_desc      

                           AND pd.original_sp=pdsw.original_sp      

                           AND pd.current_sp=pdsw.current_sp      

                           AND pm.description=pmsw.description      

          AND Pd.current_cp=Pdsw.current_cp) +       

      

(SELECT SUM(phs.Sales_units - phs.REFND_units)      

                     FROM  product_history phs,        

                           product_master pms,        

                           product_detail pds,        

                           SIZES ss,        

                           colour cs,        

                           branch bs,        

                           anal_level als      

                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       

                           AND phs.sku_id = pds.sku_id        

                           AND pds.product_id = pms.prod_id        

                           AND als.j_code = pms.j_code_link        

                           AND als.level_no = 4        

                           AND ss.sizes_id = pds.sizes_id        

                           AND cs.colour_id = pds.colour_id        

                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bs.branch_id = phs.branch_id        

                           AND als.level1_desc = @season      

                           AND pm.alt_code1=pms.alt_code1      

                           AND c.colour_desc=cs.colour_desc      

                           AND al.level_desc=als.level_desc      

                           AND pd.original_sp=pds.original_sp      

                           AND pd.current_sp=pds.current_sp      

                           AND pm.description=pms.description      

                           AND Pd.current_cp=Pds.current_cp),      

      

      

'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      

                     FROM  product_history phssw,        

                           product_master pmssw,        

                           product_detail pdssw,        

                           SIZES sssw,        

                           colour cssw,        

                           branch bssw,        

                           anal_level alssw      

                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       

                           AND phssw.sku_id = pdssw.sku_id        

                           AND pdssw.product_id = pmssw.prod_id        

                           AND alssw.j_code = pmssw.j_code_link        

                           AND alssw.level_no = 4        

                           AND sssw.sizes_id = pdssw.sizes_id        

                           AND cssw.colour_id = pdssw.colour_id        

                           AND bssw.BRANCH_CODE IN ('999')      

                           AND bssw.branch_id = phssw.branch_id        

                           AND alssw.level1_desc = @season      

                           AND pm.alt_code1=pmssw.alt_code1      

                           AND c.colour_desc=cssw.colour_desc      

                           AND al.level_desc=alssw.level_desc      

                           AND pd.original_sp=pdssw.original_sp      

                           AND pd.current_sp=pdssw.current_sp      

                           AND pm.description=pmssw.description      

                           AND Pd.current_cp=Pdssw.current_cp) +      

      

(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector BETWEEN @from_week AND @to_week     

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                           AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      

      

      

      

      

'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210','098')       

THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),      

      

'TOTAL_COVER' = convert(decimal(18,1),case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      

      

'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      

                     FROM  product_history phss,        

                           product_master pmss,        

                           product_detail pdss,        

                           SIZES sss,        

                           colour css,        

                           branch bss,        

                           anal_level alss      

                     WHERE phss.week_selector = @to_week  - 1      

                           AND phss.sku_id = pdss.sku_id        

                           AND pdss.product_id = pmss.prod_id        

                           AND alss.j_code = pmss.j_code_link        

                           AND alss.level_no = 4        

                           AND sss.sizes_id = pdss.sizes_id        

                           AND css.colour_id = pdss.colour_id        

                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      

                           AND bss.branch_id = phss.branch_id        

                           AND alss.level1_desc = @season      

                           AND pm.alt_code1=pmss.alt_code1      

                           AND c.colour_desc=css.colour_desc      

                   AND al.level_desc=alss.level_desc      

                           AND pd.original_sp=pdss.original_sp      

                           AND pd.current_sp=pdss.current_sp      

                           AND pm.description=pmss.description      

                           AND Pd.current_cp=Pdss.current_cp),      

      

      

'OVER_ALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      

                     FROM  PRODUCT_HISTORY PHSS,        

                           PRODUCT_MASTER PMSS,        

                           PRODUCT_DETAIL PDSS,        

                           SIZES SSS,        

                           COLOUR CSS,        

                           BRANCH BSS,        

                           ANAL_LEVEL ALSS      

                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      

                           AND PHSS.SKU_ID = PDSS.SKU_ID       

                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        

                         AND ALSS.J_CODE = PMSS.J_CODE_LINK        

                           AND ALSS.LEVEL_NO = 4        

                           AND SSS.SIZES_ID = PDSS.SIZES_ID        

                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        

                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',

  

    

'034','035','207','208','209','210','211','212','213','214','215')      

                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        

                           AND alss.level1_desc = @season      

                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      

                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      

                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      

                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      

                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      

                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      

                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      

      

      

       

        

FROM      product_history ph

join 	product_detail pd

on 	ph.sku_id = pd.sku_id   

join 	product_master pm

on 	pd.product_id = pm.prod_id  

join 	SIZES S

on	s.sizes_id = pd.sizes_id

join 	colour c

on 	c.colour_id = pd.colour_id

join	branch b

on	b.branch_id = ph.branch_id 

join 	anal_level al 

on 	al.j_code = pm.j_code_link 

left outer join product_anal_types pat   

on 	pa.anal_id = pm.anal_id_3

left outer join 	product_anal pa

on	pa.anal_type = pat.anal_type 

WHERE     ph.week_selector = @to_week      

          AND al.level_no = 4        

          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   

         AND al.level1_desc = @season

 

          

GROUP BY        

          pm.alt_code1,      

          c.colour_desc,       

          al.level_desc,      

          pd.original_sp,      

          pd.current_sp,      

          pm.description,      

          Pd.current_cp,  

 pa.anal_desc      

order by        

          pm.alt_code1      

      

end

GO

 

Open in new window

0
 
LVL 2

Expert Comment

by:matty1stop
Comment Utility
Does the "product_anal" table have a field called "anal_desc"?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now