Calculate percentages based on if condition

Question

Calculate percentages based on if condition

acmedeepak

Old Hand

Points: 385
More actions
May 20, 2017 at 12:23 pm

#321609

Hi,
I have a query and I need to implement logic to calculate percentages based on if condition in stored procedure itself. Please help me in this...
My existing query:
: 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, sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.RetailPrice as total_retail_price, PJ_Item_M.RetailPrice 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))
I need to calculate percentages for each column and show them on screen. The conditions and logic(in C#) are:
If((DateTime.Parse(WeekEnd_Date) >= 2002-06-12) & (DateTime.Parse(WeekEnd_Date) <= 2002-06-16)); thenCY_CW_RtlVal = (CY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_CY_CW_RtlVal = (T_CY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); EndIf((DateTime.Parse(WeekEnd_Date) >= LY_CW_FrmDt) & (DateTime.Parse(WeekEnd_Date) <= 2001-06-16)); thenLY_CW_RtlVal = (LY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_LY_CW_RtlVal = (T_LY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); EndIf((DateTime.Parse(Week) >= CY_FromDate) & (DateTime.Parse(WeekEnd_Date) <= 2002-06-16)); thenCY_YTD_RtlVal = (CY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_CY_YTD_RtlVal = (T_CY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); EndIf((DateTime.Parse(WeekEnd_Date) >= LY_FromDate) & (DateTime.Parse(WeekEnd_Date) <= 2001-06-16)); thenLY_YTD_RtlVal = (LY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_LY_YTD_RtlVal = (T_LY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); 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);
I am unable to implement it as I am not so good at stored procedures. Please help me in this

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

acmedeepak,

In order to help you we need more information along with some sample data and expected output.
We also don't know how you have translated the query output to the C# names. W/O that we can't determine what code would be able to replace the C# code.

Regards,
Matt

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Let's start by formatting your code.
SELECT s.Store_Number, l.Location, s.WkEnd_Date, s.ItemNumber, SUM(s.Qty) as Qty, i.SellingPrice, SUM(s.Qty)*i.SellingPrice AS total_price, SUM(s.Qty)*i.RetailPrice AS total_retail_price, i.RetailPrice FROM PJ_RetStore_Sales_T s JOIN PJ_Location_M l ON s.Store_Number = l.Store_Number JOIN PJ_Item_M i ON s.ItemNumber = i.ItemNumber WHERE i.variation = 'Std' AND s.WkEnd_Date >= '2002-06-12' AND s.WkEnd_Date <= '2016-06-16' AND s.ItemNumber = 'C123' GROUP BY s.Store_Number, l.Location, s.WkEnd_Date, s.ItemNumber, i.SellingPrice, i.RetailPrice ORDER BY s.Store_Number, s.WkEnd_Date;
If((DateTime.Parse(WeekEnd_Date) >= 2002-06-12) & (DateTime.Parse(WeekEnd_Date) <= 2002-06-16)); then CY_CW_RtlVal = (CY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_CY_CW_RtlVal = (T_CY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); End If((DateTime.Parse(WeekEnd_Date) >= LY_CW_FrmDt) & (DateTime.Parse(WeekEnd_Date) <= 2001-06-16)); then LY_CW_RtlVal = (LY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_LY_CW_RtlVal = (T_LY_CW_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); End If((DateTime.Parse(Week) >= CY_FromDate) & (DateTime.Parse(WeekEnd_Date) <= 2002-06-16)); then CY_YTD_RtlVal = (CY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_CY_YTD_RtlVal = (T_CY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); End If((DateTime.Parse(WeekEnd_Date) >= LY_FromDate) & (DateTime.Parse(WeekEnd_Date) <= 2001-06-16)); then LY_YTD_RtlVal = (LY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); T_LY_YTD_RtlVal = (T_LY_YTD_RtlVal + round((float.Parse(Qty) * float.Parse(RetailPrice)), 2)); 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);
Now, we need to know what do all those columns represent and if we need to add more or just add calculations to the ones that are already in the query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

acmedeepak Old Hand Points: 385 More actions · Answer 3

Hi,
Thank you Matt and Luis,

Sorry for not being clear. I need to create new columns in order to show the calculated output. Below image is my output format.

My query from old code is:
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), PJ_Item_M.SellingPrice, PJ_Item_M.RetailPrice 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 >= '" & LY_FromDate & "' and WkEnd_Date <= '" & CY_ToDate & "' and PJ_RetStore_Sales_T.ItemNumber = '" & bItemID & "' 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

And the calculation part is an extra logic from the C# code which is given above. Please help me in completing this Query with output. Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I'm not helping if you're not even willing to format your code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

acmedeepak Old Hand Points: 385 More actions · Answer 5

Sorry Louis C,
My requirement changed a bit. Please help me if you can for the below query:
select RST.Store_Number, PLM.Location, RST.WkEnd_Date, RST.ItemNumber, SUM(RST.Qty) as Qty, PIM.SellingPrice, sum(RST.Qty)*PIM.SellingPrice as total_price, sum(RST.Qty)*PIM.RetailPrice as total_retail_price, Max(case when WkEnd_Date>WkEnd_Date then WkEnd_Date else null end) YTD_Date, PIM.RetailPrice from PJ_RetStore_Sales_T RST, PJ_Location_M PLM, PJ_Item_M PIM where RST.Store_Number = PLM.Store_Number and RST.ItemNumber = PIM.ItemNumber and PIM.variation = 'Std' and WkEnd_Date >= '2006-06-06' and WkEnd_Date <= '2007-06-06' and RST.ItemNumber = 'C123' group by RST.Store_Number, PLM.Location, RST.WkEnd_Date, RST.ItemNumber, PIM.SellingPrice, PIM.RetailPrice order by RST.Store_Number, RST.WkEnd_Date
This query returns two rows for each item number based on the Current Week and YTD results( 2 dates in 1 column). I need to separate the Qty values based on the date. one for Current Week and other for YTD. Please check my Query, Sample result set and help me alter my query so that I can split the column values into two columns based on the date column.

Now, The result set is:

206 | NJ | 2007-02-11 | C123 | 150 | 14.80 | 2250 | 2400 | Null | 15.99

206 | NJ | 2007-02-18 | C123 | 1064 | 14.80 | 15960 | 17024 | Null | 15.99

221 | New Brit | 2007-02-11 | c123 |150 | 14.80 | 2250 | 2400 | NULL | 15.99
221 | New Brit | 2007-02-18 | c123 | 876 | 14.80 | 13140 | 14016 | NULL | 15.99

Expected Result set is:

206 | NJ | 2007-02-11 | C123 | 150 | 1064 | 14.80 | 2250 | 2400 | Null | 15.99

221 | New Brit | 2007-02-11 | c123 |150 | 876 | 14.80 | 2250 | 2400 | NULL | 15.99

Please help me in this...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Luis Cazares - Tuesday, May 23, 2017 6:50 AM
I'm not helping if you're not even willing to format your code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2