Need to create four columns to existing query output based on if condition

• acmedeepak

  Old Hand

  Points: 385

  More actions

  May 19, 2017 at 12:43 am

  #337934

  Hi all,

  I have a stored procedure and I need to include new columns in it based on the if conditions. Please check my stored procedure in SQL server:

  select PJ_RetStore_Sales_T.Store_Number, PJ_Location_M.Location, PJ_RetStore_Sales_T.WkEnd_Date, 
  PJ_RetStore_Sales_T.ItemNumber, SUM(PJ_RetStore_Sales_T.Qty) as Qty, PJ_Item_M.SellingPrice, 
  sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.SellingPrice as total_price,  PJ_Item_M.RetailPrice, 
  sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.RetailPrice as total_retail_price  from PJ_RetStore_Sales_T, 
  PJ_Location_M, PJ_Item_M  where PJ_RetStore_Sales_T.Store_Number = PJ_Location_M.Store_Number 
  and PJ_RetStore_Sales_T.ItemNumber = PJ_Item_M.ItemNumber  and PJ_Item_M.variation = 'Std' 
  and WkEnd_Date >= '2002-06-12' and WkEnd_Date <= '2016-06-16'  
  and PJ_RetStore_Sales_T.ItemNumber = 'C123' 
  group by PJ_RetStore_Sales_T.Store_Number, PJ_Location_M.Location, PJ_RetStore_Sales_T.WkEnd_Date, 
  PJ_RetStore_Sales_T.ItemNumber, PJ_Item_M.SellingPrice, PJ_Item_M.RetailPrice 
  order by PJ_RetStore_Sales_T.Store_Number, PJ_RetStore_Sales_T.WkEnd_Date

  Now, I need the following logic to be implemented in the sQL query:

  EndIf((DateTime.Parse(WeekEnd_Date) >= CY_CW_FrmDt) & (DateTime.Parse(WeekEnd_Date) <= CY_ToDate));   
  then CY_CW_RtlVal = (CY_CW_RtlVal + round((float.Parse(Sum(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));   
  T_CY_CW_RtlVal = (T_CY_CW_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));
  EndIf((DateTime.Parse(WeekEnd_Date) >= LY_CW_FrmDt) & (DateTime.Parse(WeekEnd_Date) <= LY_ToDate));
     then LY_CW_RtlVal = (LY_CW_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));
     T_LY_CW_RtlVal = (T_LY_CW_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));
  EndIf((DateTime.Parse(WeekEnd_Date) >= CY_FromDate) & (DateTime.Parse(WeekEnd_Date) <= CY_ToDate));   
  then CY_YTD_RtlVal = (CY_YTD_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));   
  T_CY_YTD_RtlVal = (T_CY_YTD_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));
  EndIf((DateTime.Parse(WeekEnd_Date) >= LY_FromDate) & (DateTime.Parse(WeekEnd_Date) <= LY_ToDate));   
  then LY_YTD_RtlVal = (LY_YTD_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2));   
  T_LY_YTD_RtlVal = (T_LY_YTD_RtlVal + round((float.Parse(PJ_RetStore_Sales__T.Qty) * float.Parse(PJ_Item_M.RetailPrice)), 2)); 
  End         bT_Week_Perc = (((T_CY_CW_RtlVal / T_LY_CW_RtlVal)- 1) * 100);   
  bT_YTD_Perc = (((T_CY_YTD_RtlVal / T_LY_YTD_RtlVal)- 1)* 100);

  Please consider all the variables as new columns in the table. The code is in VB and I want to implement it in SQL server query.

  CY_CW_RtlVal, T_CY_CW_RtlVal,LY_CW_RtlVal,T_LY_CW_RtlVal,CY_YTD_RtlVal,T_CY_YTD_RtlVal,LY_YTD_RtlVal,T_LY_YTD_RtlVal

   bT_Week_Perc,bT_YTD_Perc
  

  are column names.
  Please help me.

  Regards,

  Deepak