Solved

if statement to run query

Posted on 2008-06-17
8
218 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 49
MySQL Grouping 2 41
MySQL Memory Keeps Increasing 4 35
why sql server only update some statistics in the database ? 3 20
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

776 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