if statement to run query

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

ammartahir1978Asked:
Who is Participating?
 
matty1stopConnect With a Mentor Commented:
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
 
Philip PinnellCommented:
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
 
matty1stopCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ammartahir1978Author Commented:
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
 
ammartahir1978Author Commented:
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
 
Philip PinnellCommented:
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
 
ammartahir1978Author Commented:
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
 
matty1stopCommented:
Does the "product_anal" table have a field called "anal_desc"?
0
All Courses

From novice to tech pro — start learning today.