Determine Percent Difference between two CASE Statements

[VictorAst]

I am working with Wisys Agility Design Studio and writing SQL statements to determine sales differences from year to year.

Case where I determine the 2016 sales data

CASE
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 '2016 Sales',

Case where I determine YTD 2015 comparable to 2016 data

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 )
END AS 'YTD 2015 Sales',

I then created the case to determine the difference between the two

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 now what to create the Percentage Difference for the two, and this is where I am stuck. I cannot divide a Case by a Case. Help

 

[johnherman]

A couple basic solutions here. Subquery, temporary table. Not sure how these work with Wisys Agility Design Studio.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[Borvik]

Yeah, like johnherman said - subquery, if you are writing the full sql statement.

Something like this:

select
  [2016 Sales]
, [YTD 2015 Sales]
, [2016 Sales] - [YTD 2015 Sales]  --or whatever your formula
from
  (select
    case...end as [2016 Sales],
    case...end as [YTD 2015 Sales]
   from...) tbl

 

[VictorAst]

Borkiv

So I can use Aliases in a subquery? 2016 Sales, YTD 2015 Sales are Aliases

 

[fredericofonseca]

victor the following would be valid SQL constructs that would work with SQL Server 2008 R2 and higher, maybe earlier versions.

sample table definition based on the sql you posted

if object_id('oehdrhst_wv') is not null
    drop table oehdrhst_wv
create table oehdrhst_wv
(inv_id int
,inv_dt date
)

if object_id('oelinhst_wv') is not null
    drop table oelinhst_wv
create table oelinhst_wv
(inv_id int
,qty_to_ship int
,unit_price int
,qty_return_to_stk int
,discount_pct int
)

select calculations.[2016 Sales] - calculations.[YTD 2015 Sales] as diff
      ,calculations.*
from oehdrhst_wv
inner join oelinhst_wv
on oelinhst_wv.inv_id = oelinhst_wv.inv_id
outer apply (select case
                    when year(oehdrhst_wv.inv_dt) = 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 '2016 Sales'
                   ,case
                    when oehdrhst_wv.inv_dt between convert(date,'20150101',112) 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)
                    end as 'YTD 2015 Sales'
) as calculations

select [2016 Sales]
      ,[YTD 2015 Sales]
      ,[2016 Sales] - [YTD 2015 Sales] as diff
from (select case
             when year(oehdrhst_wv.inv_dt) = 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 '2016 Sales'
            ,case
             when oehdrhst_wv.inv_dt between convert(date,'20150101',112) 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)
             end as 'YTD 2015 Sales'
    from oehdrhst_wv
    inner join oelinhst_wv
        on oelinhst_wv.inv_id = oelinhst_wv.inv_id
) as temp_query

select case
       when year(oehdrhst_wv.inv_dt) = 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 '2016 Sales'
      ,case
       when oehdrhst_wv.inv_dt between convert(date,'20150101',112) 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)
       end as 'YTD 2015 Sales'
      ,case
       when year(oehdrhst_wv.inv_dt) = 2016
           then (oelinhst_wv.qty_to_ship - oelinhst_wv.qty_return_to_stk) * oelinhst_wv.unit_price * (1 - oelinhst_wv.discount_pct / 100)
       end 
       - case
         when oehdrhst_wv.inv_dt between convert(date,'20150101',112) 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)
         end
from oehdrhst_wv
inner join oelinhst_wv
on oelinhst_wv.inv_id = oelinhst_wv.inv_id

Note that I have replaced "oehdrhst_wv.inv_dt LIKE '%2016%'" with "year(oehdrhst_wv.inv_dt) = 2016" - reasoning is that if a field is a date use date functions on it, not implicit conversions to/from string.
Also replaced "'1/1/2015'" with a explicit cast of a format that will not give dubious results depending on the server settings.
Just standards of mine that do avoid issues on the long run

I did place them on the order I would normally do them although 1 and 2 I do interchange depending on the query.
Last one I avoid as it does mean duplicating the case statements which should be avoided from a code maintenance point of view.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Borvik]

Yes you can use aliases in a subquery. Essentially you are given those calculated fields column names when you give them an alias, and the outer query can then use those aliases to filter and do more calculations on.

 

[VictorAst]

So I got a case of the "Staring at the screen too much and cant think"

How would I write the sub query using the Aliases of YTD Diff and YTD 2015 Sales to determine the Percentage difference

YTD Diff/YTD 2015 Sales = xxx.xx%

 

[Borvik]

take a look at fredericofonseca's third example, sure Frederico (and I for that matter) used a subtraction but you can use whatever formula you want