I have posted on SQL forum also, but was wondering if any Wisys Agility Design Studio programmers can help
I am running a pivot grid to determine sales and am needing to get a percentage difference from two Aliases I created. I have the CASE statement below to determine the Difference, but cannot figure out how to get a % Difference.
I am a novice SQL writer
CASE
WHEN oehdrhst_wv.inv_dt BETWEEN '1/1/2015' AND Dateadd(Year, -1, Getdate()) THEN -( ( oelinhst_wv.qty_to_ship - oelinhst_wv.qty_return_to_stk ) * oelinhst_wv.unit_price * ( 1 - oelinhst_wv.discount_pct / 100 ) )
WHEN oehdrhst_wv.inv_dt LIKE '%2016%' THEN ( ( oelinhst_wv.qty_to_ship - oelinhst_wv.qty_return_to_stk ) * oelinhst_wv.unit_price * ( 1 - oelinhst_wv.discount_pct / 100 ) )
END AS 'YTD Diff',
I know I may have to create a subquery, but I do not know how to run a subquery like that.
The Aliases I need to determine the % is [YTD Diff]/[YTD 2015 Sales]
Thank You
I am running a pivot grid to determine sales and am needing to get a percentage difference from two Aliases I created. I have the CASE statement below to determine the Difference, but cannot figure out how to get a % Difference.
I am a novice SQL writer
CASE
WHEN oehdrhst_wv.inv_dt BETWEEN '1/1/2015' AND Dateadd(Year, -1, Getdate()) THEN -( ( oelinhst_wv.qty_to_ship - oelinhst_wv.qty_return_to_stk ) * oelinhst_wv.unit_price * ( 1 - oelinhst_wv.discount_pct / 100 ) )
WHEN oehdrhst_wv.inv_dt LIKE '%2016%' THEN ( ( oelinhst_wv.qty_to_ship - oelinhst_wv.qty_return_to_stk ) * oelinhst_wv.unit_price * ( 1 - oelinhst_wv.discount_pct / 100 ) )
END AS 'YTD Diff',
I know I may have to create a subquery, but I do not know how to run a subquery like that.
The Aliases I need to determine the % is [YTD Diff]/[YTD 2015 Sales]
Thank You