Solved

if statement to run query

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

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

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


please help
CREATE procedure LTS_STYLE_PERFORMAnCE_REPORT_PAUL      
      
@Season  varchar(50) = '',      
@FROM_WEEK int,      
@TO_Week int,      
@LY_START_WEEK int,      
@LY_END_WEEK int      
      
      
as begin      
      
if @season = ''      
SELECT         
--pa.anal_desc as SEASON,    
          al.level_desc as DEPT_CATEGORY,      
          pm.alt_code1 as STYLE,      
          c.colour_desc as COLOUR,       
          pm.description as DESCRIPTION,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      
         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,      
      
'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code) else 0 end),      
      
/************START OF RETAIL FIELDS************/      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      
              
'VALUE' = SUM(CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')
THEN (ph.sales_spvalue + ph.refnd_disc_value) - (ph.REFND_spvalue + ph.sales_disc_value) ELSE 0 END),      
      
'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           --AND pms.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+ Convert(Varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                    AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),0)),      
      
      
--   'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
-- else       
-- SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','20      
-- 8','209','210','211','212','213','214','215') THEN ph.Sales_units     
      
      
      
'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
else       
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      
      
SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      
      
'STORE_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'TOTAL_RETAIL_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
      
      
/************START OF MAIL_ORDER FIELDS************/      
      
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       
      
'VALUE'=CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           --AND pmsw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+CONVERT(VARCHAR,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           --AND pmssw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      
      
              
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      
      
'MO_COVER' =    convert(decimal(18,1),    
case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
/************START OF TOTAL FIELDS************/      
      
      
'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      
      
'VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      
      
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           --AND pmsw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
       AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp) +       
      
(SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           --AND pms.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           --AND pmssw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp) +      
      
(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                        branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week       
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      
      
      
      
      
'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','098','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),
      
      
'TOTAL_COVER' =convert(decimal(18,1), case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector = @to_week  - 1      
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',
  
    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),      
      
      
'OVERALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      
                     FROM  PRODUCT_HISTORY PHSS,        
                           PRODUCT_MASTER PMSS,        
                           PRODUCT_DETAIL PDSS,        
                           SIZES SSS,        
                           COLOUR CSS,        
                           BRANCH BSS,        
                           ANAL_LEVEL ALSS      
                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      
                           AND PHSS.SKU_ID = PDSS.SKU_ID       
                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        
                           AND ALSS.J_CODE = PMSS.J_CODE_LINK        
                           AND ALSS.LEVEL_NO = 4        
                           AND SSS.SIZES_ID = PDSS.SIZES_ID        
                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        
                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033' 
 
,    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        
                           --AND PMSS.ALT_CODE1 LIKE '%Q%'      
                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      
                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      
                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      
                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      
                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      
                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      
                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      
      
      
       
        
FROM      product_history ph,        
          product_master pm,        
          product_detail pd,        
          SIZES s,        
          colour c,        
          branch b,        
          anal_level al  
--product_anal pa,    
--product_anal_types pat      
        
WHERE     ph.week_selector = @to_week      
          AND ph.sku_id = pd.sku_id        
          AND pd.product_id = pm.prod_id        
          AND al.j_code = pm.j_code_link        
          AND al.level_no = 4        
          AND s.sizes_id = pd.sizes_id        
          AND c.colour_id = pd.colour_id     
    --    AND pa.anal_type = pat.anal_type    
  --      AND pa.anal_id = pm.anal_id_3       
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   
          AND b.branch_id = ph.branch_id        
         --AND pm.alt_code1 like '%Q%'      
               
          
GROUP BY        
          pm.alt_code1,      
          c.colour_desc,       
          al.level_desc,      
          pd.original_sp,      
          pd.current_sp,      
          pm.description,      
          Pd.current_cp  
  --        pa.anal_desc       
order by        
          pm.alt_code1      
      
      
      
ELSE      
      
SELECT        
-- pa.anal_desc as SEASON,     
          al.level_desc as DEPT_CATEGORY,      
          pm.alt_code1 as STYLE,      
          c.colour_desc as COLOUR,       
          pm.description as DESCRIPTION,      
           CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      
         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,     
    
/************START OF RETAIL FIELDS************/      
      
'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 else 0 end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      
              
'VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),       
      
'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           AND als.level1_desc = @season      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                  AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp)*1,0)),      
      
      
      
      
      
      
'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
else       
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      
      
SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      
      
'STORE_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'TOTAL_RETAIL_COVER' =  convert(decimal(18,1),      
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
/************START OF MAIL_ORDER FIELDS************/      
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       
      
'VALUE'=CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           AND alsw.level1_desc = @season      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           AND alssw.level1_desc = @season      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      
      
              
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      
      
'MO_COVER' =    convert(decimal(18,1),    
case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
      
/************START OF TOTAL FIELDS************/      
      
'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','
  
    
20      
8','209','210','211','212','213','214','215') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      
      
'VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','03
  
1','032','033','034','035','207','    
20      
8','209','210','211','212','213','214','215') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      
      
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           AND alsw.level1_desc = @season      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
          AND Pd.current_cp=Pdsw.current_cp) +       
      
(SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           AND als.level1_desc = @season      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           AND alssw.level1_desc = @season      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp) +      
      
(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week     
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      
      
      
      
      
'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210','098')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),      
      
'TOTAL_COVER' = convert(decimal(18,1),case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector = @to_week  - 1      
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                   AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),      
      
      
'OVER_ALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      
                     FROM  PRODUCT_HISTORY PHSS,        
                           PRODUCT_MASTER PMSS,        
                           PRODUCT_DETAIL PDSS,        
                           SIZES SSS,        
                           COLOUR CSS,        
                           BRANCH BSS,        
                           ANAL_LEVEL ALSS      
                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      
                           AND PHSS.SKU_ID = PDSS.SKU_ID       
                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        
                         AND ALSS.J_CODE = PMSS.J_CODE_LINK        
                           AND ALSS.LEVEL_NO = 4        
                           AND SSS.SIZES_ID = PDSS.SIZES_ID        
                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        
                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',
  
    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        
                           AND alss.level1_desc = @season      
                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      
                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      
                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      
                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      
                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      
                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      
                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      
      
      
       
        
FROM      product_history ph,        
          product_master pm,        
          product_detail pd,        
          SIZES s,        
          colour c,        
          branch b,        
          anal_level al
-- product_anal pa,    
-- product_anal_types pat          
        
WHERE     ph.week_selector = @to_week      
          AND ph.sku_id = pd.sku_id        
          AND pd.product_id = pm.prod_id        
          AND al.j_code = pm.j_code_link        
          AND al.level_no = 4        
          AND s.sizes_id = pd.sizes_id        
          AND c.colour_id = pd.colour_id      
-- AND pa.anal_type = pat.anal_type    
 --AND pa.anal_id = pm.anal_id_3       
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','
     
208','209','210','211','212','213','214','215')        
          AND b.branch_id = ph.branch_id        
          AND al.level1_desc = @season      
               
          
GROUP BY        
          pm.alt_code1,      
          c.colour_desc,       
          al.level_desc,      
          pd.original_sp,      
          pd.current_sp,      
          pm.description,      
          Pd.current_cp  
 --pa.anal_desc      
order by        
          pm.alt_code1      
      
end
GO

Open in new window

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

Expert Comment

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

Expert Comment

by:matty1stop
ID: 21802496
Your question is a little confusing but I'm gonna take a guess and say that you probably want to do a left join to the "pa" table instead of joining the tables in your where clauses
Try changing your from and where clauses to this.
 
One important note:  You need a join on the branch table
 
FROM      product_history ph
join 	product_detail pd
on 	ph.sku_id = pd.sku_id   
join 	roduct_master pm
on 	pd.product_id = pm.prod_id  
join 	SIZES S
on	s.sizes_id = pd.sizes_id
join 	colour c,
on 	c.colour_id = pd.colour_id
--join	branch b
join 	anal_level al 
on 	al.j_code = pm.j_code_link 
left ouer join product_anal_types pat   
on 	pa.anal_id = pm.anal_id_3
left ouer join 	product_anal pa
on	pa.anal_type = pat.anal_type 
WHERE     ph.week_selector = @to_week      
          AND al.level_no = 4        
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   
          AND pm.alt_code1 like '%Q%'   

Open in new window

0
 

Author Comment

by:ammartahir1978
ID: 21814175
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:ammartahir1978
ID: 21814210
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
 
LVL 13

Expert Comment

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

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

Accepted Solution

by:
matty1stop earned 500 total points
ID: 21814329
Try running this.

Corrected the typo, thanks andycrofts
CREATE procedure LTS_STYLE_PERFORMAnCE_REPORT_PAUL      
      
@Season  varchar(50) = '',      
@FROM_WEEK int,      
@TO_Week int,      
@LY_START_WEEK int,      
@LY_END_WEEK int      
      
      
as begin      
      
if @season = ''      
SELECT         
--pa.anal_desc as SEASON,    
          al.level_desc as DEPT_CATEGORY,      
          pm.alt_code1 as STYLE,      
          c.colour_desc as COLOUR,       
          pm.description as DESCRIPTION,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      
         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,      
      
'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code) else 0 end),      
      
/************START OF RETAIL FIELDS************/      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      
              
'VALUE' = SUM(CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')
THEN (ph.sales_spvalue + ph.refnd_disc_value) - (ph.REFND_spvalue + ph.sales_disc_value) ELSE 0 END),      
      
'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           --AND pms.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+ Convert(Varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                    AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),0)),      
      
      
--   'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
-- else       
-- SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','20      
-- 8','209','210','211','212','213','214','215') THEN ph.Sales_units     
      
      
      
'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
else       
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      
      
SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      
      
'STORE_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'TOTAL_RETAIL_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
      
      
/************START OF MAIL_ORDER FIELDS************/      
      
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       
      
'VALUE'=CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           --AND pmsw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+CONVERT(VARCHAR,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           --AND pmssw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      
      
              
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      
      
'MO_COVER' =    convert(decimal(18,1),    
case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
/************START OF TOTAL FIELDS************/      
      
      
'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      
      
'VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      
      
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           --AND pmsw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
       AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp) +       
      
(SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           --AND pms.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           --AND pmssw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp) +      
      
(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                        branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week       
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      
      
      
      
      
'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','098','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),
      
      
'TOTAL_COVER' =convert(decimal(18,1), case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector = @to_week  - 1      
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',
  
    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),      
      
      
'OVERALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      
                     FROM  PRODUCT_HISTORY PHSS,        
                           PRODUCT_MASTER PMSS,        
                           PRODUCT_DETAIL PDSS,        
                           SIZES SSS,        
                           COLOUR CSS,        
                           BRANCH BSS,        
                           ANAL_LEVEL ALSS      
                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      
                           AND PHSS.SKU_ID = PDSS.SKU_ID       
                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        
                           AND ALSS.J_CODE = PMSS.J_CODE_LINK        
                           AND ALSS.LEVEL_NO = 4        
                           AND SSS.SIZES_ID = PDSS.SIZES_ID        
                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        
                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033' 
 
,    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        
                           --AND PMSS.ALT_CODE1 LIKE '%Q%'      
                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      
                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      
                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      
                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      
                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      
                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      
                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      
      
      
       
        
FROM      product_history ph,        
          product_master pm,        
          product_detail pd,        
          SIZES s,        
          colour c,        
          branch b,        
          anal_level al  
--product_anal pa,    
--product_anal_types pat      
        
WHERE     ph.week_selector = @to_week      
          AND ph.sku_id = pd.sku_id        
          AND pd.product_id = pm.prod_id        
          AND al.j_code = pm.j_code_link        
          AND al.level_no = 4        
          AND s.sizes_id = pd.sizes_id        
          AND c.colour_id = pd.colour_id     
    --    AND pa.anal_type = pat.anal_type    
  --      AND pa.anal_id = pm.anal_id_3       
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   
          AND b.branch_id = ph.branch_id        
         --AND pm.alt_code1 like '%Q%'      
               
          
GROUP BY        
          pm.alt_code1,      
          c.colour_desc,       
          al.level_desc,      
          pd.original_sp,      
          pd.current_sp,      
          pm.description,      
          Pd.current_cp  
  --        pa.anal_desc       
order by        
          pm.alt_code1      
      
      
      
ELSE      
      
SELECT        
-- pa.anal_desc as SEASON,     
          al.level_desc as DEPT_CATEGORY,      
          pm.alt_code1 as STYLE,      
          c.colour_desc as COLOUR,       
          pm.description as DESCRIPTION,      
           CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      
         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,     
    
/************START OF RETAIL FIELDS************/      
      
'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 else 0 end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      
              
'VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),       
      
'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           AND als.level1_desc = @season      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                  AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp)*1,0)),      
      
      
      
      
      
      
'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
else       
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      
      
SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      
      
'STORE_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'TOTAL_RETAIL_COVER' =  convert(decimal(18,1),      
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
/************START OF MAIL_ORDER FIELDS************/      
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       
      
'VALUE'=CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           AND alsw.level1_desc = @season      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           AND alssw.level1_desc = @season      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      
      
              
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      
      
'MO_COVER' =    convert(decimal(18,1),    
case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
      
/************START OF TOTAL FIELDS************/      
      
'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','
  
    
20      
8','209','210','211','212','213','214','215') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      
      
'VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','03
  
1','032','033','034','035','207','    
20      
8','209','210','211','212','213','214','215') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      
      
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           AND alsw.level1_desc = @season      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
          AND Pd.current_cp=Pdsw.current_cp) +       
      
(SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           AND als.level1_desc = @season      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           AND alssw.level1_desc = @season      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp) +      
      
(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week     
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      
      
      
      
      
'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210','098')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),      
      
'TOTAL_COVER' = convert(decimal(18,1),case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector = @to_week  - 1      
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                   AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),      
      
      
'OVER_ALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      
                     FROM  PRODUCT_HISTORY PHSS,        
                           PRODUCT_MASTER PMSS,        
                           PRODUCT_DETAIL PDSS,        
                           SIZES SSS,        
                           COLOUR CSS,        
                           BRANCH BSS,        
                           ANAL_LEVEL ALSS      
                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      
                           AND PHSS.SKU_ID = PDSS.SKU_ID       
                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        
                         AND ALSS.J_CODE = PMSS.J_CODE_LINK        
                           AND ALSS.LEVEL_NO = 4        
                           AND SSS.SIZES_ID = PDSS.SIZES_ID        
                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        
                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',
  
    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        
                           AND alss.level1_desc = @season      
                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      
                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      
                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      
                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      
                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      
                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      
                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      
      
      
       
        
FROM      product_history ph
join 	product_detail pd
on 	ph.sku_id = pd.sku_id   
join 	roduct_master pm
on 	pd.product_id = pm.prod_id  
join 	SIZES S
on	s.sizes_id = pd.sizes_id
join 	colour c,
on 	c.colour_id = pd.colour_id
join	branch b
on	b.branch_id = ph.branch_id 
join 	anal_level al 
on 	al.j_code = pm.j_code_link 
left outer join product_anal_types pat   
on 	pa.anal_id = pm.anal_id_3
left outer join 	product_anal pa
on	pa.anal_type = pat.anal_type 
WHERE     ph.week_selector = @to_week      
          AND al.level_no = 4        
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   
          AND pm.alt_code1 like '%Q%'   
 
          
GROUP BY        
          pm.alt_code1,      
          c.colour_desc,       
          al.level_desc,      
          pd.original_sp,      
          pd.current_sp,      
          pm.description,      
          Pd.current_cp  
 --pa.anal_desc      
order by        
          pm.alt_code1      
      
end
GO
 
Open in New Window Tags: 
microsoft, sql server, 2000 Zones: 
SQL Server 2005, MySQL, SQLBase

Open in new window

0
 

Author Comment

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

say the prefix pa doesnt match with the table alias
CREATE procedure LTS_STYLE_PERFORMAnCE_REPORT_PAUL      
      
@Season  varchar(50) = '',      
@FROM_WEEK int,      
@TO_Week int,      
@LY_START_WEEK int,      
@LY_END_WEEK int      
      
      
as begin      
      
if @season = ''      
SELECT         
--pa.anal_desc as SEASON,    
          al.level_desc as DEPT_CATEGORY,      
          pm.alt_code1 as STYLE,      
          c.colour_desc as COLOUR,       
          pm.description as DESCRIPTION,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      
         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,      
      
'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code) else 0 end),      
      
/************START OF RETAIL FIELDS************/      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      
              
'VALUE' = SUM(CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')
THEN (ph.sales_spvalue + ph.refnd_disc_value) - (ph.REFND_spvalue + ph.sales_disc_value) ELSE 0 END),      
      
'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           --AND pms.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+ Convert(Varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                    AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),0)),      
      
      
--   'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
-- else       
-- SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','20      
-- 8','209','210','211','212','213','214','215') THEN ph.Sales_units     
      
      
      
'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
else       
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      
      
SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      
      
'STORE_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'TOTAL_RETAIL_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
      
      
/************START OF MAIL_ORDER FIELDS************/      
      
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       
      
'VALUE'=CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           --AND pmsw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+CONVERT(VARCHAR,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           --AND pmssw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      
      
              
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      
      
'MO_COVER' =    convert(decimal(18,1),    
case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
/************START OF TOTAL FIELDS************/      
      
      
'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      
      
'VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      
      
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           --AND pmsw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
       AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp) +       
      
(SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           --AND pms.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+CONVERT(VARCHAR,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           --AND pmssw.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp) +      
      
(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                        branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week       
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      
      
      
      
      
'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','098','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),
      
      
'TOTAL_COVER' =convert(decimal(18,1), case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector = @to_week  - 1      
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',
  
    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND bss.branch_id = phss.branch_id        
                           --AND pmss.alt_code1 like '%Q%'      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),      
      
      
'OVERALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      
                     FROM  PRODUCT_HISTORY PHSS,        
                           PRODUCT_MASTER PMSS,        
                           PRODUCT_DETAIL PDSS,        
                           SIZES SSS,        
                           COLOUR CSS,        
                           BRANCH BSS,        
                           ANAL_LEVEL ALSS      
                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      
                           AND PHSS.SKU_ID = PDSS.SKU_ID       
                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        
                           AND ALSS.J_CODE = PMSS.J_CODE_LINK        
                           AND ALSS.LEVEL_NO = 4        
                           AND SSS.SIZES_ID = PDSS.SIZES_ID        
                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        
                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033' 
 
,    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        
                           --AND PMSS.ALT_CODE1 LIKE '%Q%'      
                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      
                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      
                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      
                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      
                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      
                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      
                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      
      
      
       
        
FROM    product_history ph
join 	product_detail pd
on 	ph.sku_id = pd.sku_id   
join 	product_master pm
on 	pd.product_id = pm.prod_id  
join 	SIZES S
on	s.sizes_id = pd.sizes_id
join 	colour c
on 	c.colour_id = pd.colour_id
join	branch b
on	b.branch_id = ph.branch_id 
join 	anal_level al 
on 	al.j_code = pm.j_code_link 
left outer join product_anal_types pat   
on 	pa.anal_id = pm.anal_id_3
left outer join 	product_anal pa
on	pa.anal_type = pat.anal_type 
WHERE     ph.week_selector = @to_week      
          AND al.level_no = 4        
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   
          
GROUP BY        
          pm.alt_code1,      
          c.colour_desc,       
          al.level_desc,      
          pd.original_sp,      
          pd.current_sp,      
          pm.description,      
          Pd.current_cp , 
         pa.anal_desc       
order by        
          pm.alt_code1      
      
      
      
ELSE      
      
SELECT        
pa.anal_desc as SEASON,     
          al.level_desc as DEPT_CATEGORY,      
          pm.alt_code1 as STYLE,      
          c.colour_desc as COLOUR,       
          pm.description as DESCRIPTION,      
           CHAR(0163)+CONVERT(VARCHAR,ROUND(Pd.current_cp*1,2)) as CP,      
          CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.original_sp*1,2)) as ORIGINAL_SP,      
         CHAR(0163)+CONVERT(VARCHAR,ROUND(pd.current_sp*1,2)) as CURRENT_SP,     
    
/************START OF RETAIL FIELDS************/      
      
'STORE'=  (case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 else 0 end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,      
              
'VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),       
      
'CUM_UNITS' = (SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           AND als.level1_desc = @season      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' =CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week        
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                  AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp)*1,0)),      
      
      
      
      
      
      
'ROS' = convert(decimal(18,1),CASE WHEN ( count (distinct b.branch_code)-2) = 0 then 0      
else       
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)*1.00/(case when (Sum(ph.stock_units_free )) > 0 then count (distinct b.branch_code)-2 end) end),      
      
      
SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as STORE_STK ,      
      
SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END) as TOTAL_RETAIL_STOCK,      
      
'STORE_COVER' = convert(decimal(18,1),       
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'TOTAL_RETAIL_COVER' =  convert(decimal(18,1),      
case when (SUM (CASE WHEN B.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
/************START OF MAIL_ORDER FIELDS************/      
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) as UNITS,       
      
'VALUE'=CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)) ,       
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           AND alsw.level1_desc = @season      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
                           AND Pd.current_cp=Pdsw.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           AND alssw.level1_desc = @season      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp)*1,0)),      
      
              
      
SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END) as MO_STOCK,      
      
'MO_COVER' =    convert(decimal(18,1),    
case when (SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code = '999'      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + Stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
      
/************START OF TOTAL FIELDS************/      
      
'UNITS'=  (SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','
  
    
20      
8','209','210','211','212','213','214','215') THEN ph.Sales_units - ph.REFND_units ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_units - ph.REFND_units ELSE 0 END)),      
      
'VALUE' = CHAR(0163)+ Convert(varchar,ROUND((SUM (CASE WHEN B.BRANCH_CODE IN ('001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','03
  
1','032','033','034','035','207','    
20      
8','209','210','211','212','213','214','215') THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END)) + (SUM (CASE WHEN b.branch_code = '999' THEN ph.Sales_spvalue - ph.REFND_spvalue ELSE 0 END))*1,0)),      
      
      
'CUM_UNITS' = (SELECT SUM(phsw.Sales_units - phsw.REFND_units)      
                     FROM  product_history phsw,        
                           product_master pmsw,        
                           product_detail pdsw,        
                           SIZES ssw,        
                           colour csw,        
                           branch bsw,        
                           anal_level alsw      
                     WHERE phsw.week_selector BETWEEN @from_week AND @to_week       
                           AND phsw.sku_id = pdsw.sku_id        
                           AND pdsw.product_id = pmsw.prod_id        
                           AND alsw.j_code = pmsw.j_code_link        
                           AND alsw.level_no = 4        
                           AND ssw.sizes_id = pdsw.sizes_id        
                           AND csw.colour_id = pdsw.colour_id        
                           AND bsw.BRANCH_CODE IN ('999')      
                           AND bsw.branch_id = phsw.branch_id        
                           AND alsw.level1_desc = @season      
                           AND pm.alt_code1=pmsw.alt_code1      
                           AND c.colour_desc=csw.colour_desc      
                           AND al.level_desc=alsw.level_desc      
                           AND pd.original_sp=pdsw.original_sp      
                           AND pd.current_sp=pdsw.current_sp      
                           AND pm.description=pmsw.description      
          AND Pd.current_cp=Pdsw.current_cp) +       
      
(SELECT SUM(phs.Sales_units - phs.REFND_units)      
                     FROM  product_history phs,        
                           product_master pms,        
                           product_detail pds,        
                           SIZES ss,        
                           colour cs,        
                           branch bs,        
                           anal_level als      
                     WHERE phs.week_selector BETWEEN @from_week AND @to_week       
                           AND phs.sku_id = pds.sku_id        
                           AND pds.product_id = pms.prod_id        
                           AND als.j_code = pms.j_code_link        
                           AND als.level_no = 4        
                           AND ss.sizes_id = pds.sizes_id        
                           AND cs.colour_id = pds.colour_id        
                           AND bs.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bs.branch_id = phs.branch_id        
                           AND als.level1_desc = @season      
                           AND pm.alt_code1=pms.alt_code1      
                           AND c.colour_desc=cs.colour_desc      
                           AND al.level_desc=als.level_desc      
                           AND pd.original_sp=pds.original_sp      
                           AND pd.current_sp=pds.current_sp      
                           AND pm.description=pms.description      
                           AND Pd.current_cp=Pds.current_cp),      
      
      
'CUM_VALUE' = CHAR(0163)+ Convert(varchar,ROUND(((SELECT SUM(phssw.sales_spvalue - phssw.REFND_spvalue)      
                     FROM  product_history phssw,        
                           product_master pmssw,        
                           product_detail pdssw,        
                           SIZES sssw,        
                           colour cssw,        
                           branch bssw,        
                           anal_level alssw      
                     WHERE phssw.week_selector BETWEEN @from_week AND @to_week       
                           AND phssw.sku_id = pdssw.sku_id        
                           AND pdssw.product_id = pmssw.prod_id        
                           AND alssw.j_code = pmssw.j_code_link        
                           AND alssw.level_no = 4        
                           AND sssw.sizes_id = pdssw.sizes_id        
                           AND cssw.colour_id = pdssw.colour_id        
                           AND bssw.BRANCH_CODE IN ('999')      
                           AND bssw.branch_id = phssw.branch_id        
                           AND alssw.level1_desc = @season      
                           AND pm.alt_code1=pmssw.alt_code1      
                           AND c.colour_desc=cssw.colour_desc      
                           AND al.level_desc=alssw.level_desc      
                           AND pd.original_sp=pdssw.original_sp      
                           AND pd.current_sp=pdssw.current_sp      
                           AND pm.description=pmssw.description      
                           AND Pd.current_cp=Pdssw.current_cp) +      
      
(SELECT SUM(phss.sales_spvalue - phss.REFND_spvalue)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector BETWEEN @from_week AND @to_week     
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                           AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp))*1,0)),      
      
      
      
      
'TOTAL_STOCK' = (SUM (CASE WHEN b.branch_code = '999' THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END))+(SUM (CASE WHEN b.branch_code IN ('000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210','098')       
THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc ELSE 0 END)),      
      
'TOTAL_COVER' = convert(decimal(18,1),case when (SUM (CASE WHEN B.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END) = 0) then 0 else(SUM (CASE WHEN b.branch_code IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
 THEN ph.stock_units_free + ph.stock_units_it + ph.stock_units_qc + ph.stock_units_dmg ELSE 0 END)) *1.00/(SUM (CASE WHEN B.BRANCH_CODE IN ('999','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210') THEN ph.Sales_units - ph.REFND_units ELSE 0 END))end),      
      
'LW_SALES' = (SELECT SUM(phss.Sales_units - phss.REFND_units)      
                     FROM  product_history phss,        
                           product_master pmss,        
                           product_detail pdss,        
                           SIZES sss,        
                           colour css,        
                           branch bss,        
                           anal_level alss      
                     WHERE phss.week_selector = @to_week  - 1      
                           AND phss.sku_id = pdss.sku_id        
                           AND pdss.product_id = pmss.prod_id        
                           AND alss.j_code = pmss.j_code_link        
                           AND alss.level_no = 4        
                           AND sss.sizes_id = pdss.sizes_id        
                           AND css.colour_id = pdss.colour_id        
                           AND bss.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','010','011','012','015','016','018','020','021','022','023','025','026','027','029','031','032','035','207','208','209','210')      
                           AND bss.branch_id = phss.branch_id        
                           AND alss.level1_desc = @season      
                           AND pm.alt_code1=pmss.alt_code1      
                           AND c.colour_desc=css.colour_desc      
                   AND al.level_desc=alss.level_desc      
                           AND pd.original_sp=pdss.original_sp      
                           AND pd.current_sp=pdss.current_sp      
                           AND pm.description=pmss.description      
                           AND Pd.current_cp=Pdss.current_cp),      
      
      
'OVER_ALL_SALES' = (SELECT SUM(PHSS.SALES_UNITS - PHSS.REFND_UNITS)      
                     FROM  PRODUCT_HISTORY PHSS,        
                           PRODUCT_MASTER PMSS,        
                           PRODUCT_DETAIL PDSS,        
                           SIZES SSS,        
                           COLOUR CSS,        
                           BRANCH BSS,        
                           ANAL_LEVEL ALSS      
                     WHERE   PHSS.WEEK_SELECTOR between @LY_START_WEEK and  @LY_END_WEEK      
                           AND PHSS.SKU_ID = PDSS.SKU_ID       
                           AND PDSS.PRODUCT_ID = PMSS.PROD_ID        
                         AND ALSS.J_CODE = PMSS.J_CODE_LINK        
                           AND ALSS.LEVEL_NO = 4        
                           AND SSS.SIZES_ID = PDSS.SIZES_ID        
                           AND CSS.COLOUR_ID = PDSS.COLOUR_ID        
                           AND BSS.BRANCH_CODE IN ('999','000','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033',
  
    
'034','035','207','208','209','210','211','212','213','214','215')      
                           AND BSS.BRANCH_ID = PHSS.BRANCH_ID        
                           AND alss.level1_desc = @season      
                           AND PM.ALT_CODE1=PMSS.ALT_CODE1      
                           AND C.COLOUR_DESC=CSS.COLOUR_DESC      
                           AND AL.LEVEL_DESC=ALSS.LEVEL_DESC      
                           AND PD.ORIGINAL_SP=PDSS.ORIGINAL_SP      
                           AND PD.CURRENT_SP=PDSS.CURRENT_SP      
                           AND PM.DESCRIPTION=PMSS.DESCRIPTION      
                           AND PD.CURRENT_CP=PDSS.CURRENT_CP)      
      
      
       
        
FROM      product_history ph
join 	product_detail pd
on 	ph.sku_id = pd.sku_id   
join 	product_master pm
on 	pd.product_id = pm.prod_id  
join 	SIZES S
on	s.sizes_id = pd.sizes_id
join 	colour c
on 	c.colour_id = pd.colour_id
join	branch b
on	b.branch_id = ph.branch_id 
join 	anal_level al 
on 	al.j_code = pm.j_code_link 
left outer join product_anal_types pat   
on 	pa.anal_id = pm.anal_id_3
left outer join 	product_anal pa
on	pa.anal_type = pat.anal_type 
WHERE     ph.week_selector = @to_week      
          AND al.level_no = 4        
          AND b.branch_code IN ('000','999','001','002','003','004','005','006','007','008','009','010','011','012','014','015','016','017','018','019','020','021','022','023','024','025','026','027','028','029','030','031','032','033','034','035','207','208','209','210','211','212','213','214','215')   
         AND al.level1_desc = @season
 
          
GROUP BY        
          pm.alt_code1,      
          c.colour_desc,       
          al.level_desc,      
          pd.original_sp,      
          pd.current_sp,      
          pm.description,      
          Pd.current_cp,  
 pa.anal_desc      
order by        
          pm.alt_code1      
      
end
GO
 

Open in new window

0
 
LVL 2

Expert Comment

by:matty1stop
ID: 21878358
Does the "product_anal" table have a field called "anal_desc"?
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

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

Join & Ask a Question