Solved

SQL Query Calculation

Posted on 2016-08-22
76 Views
I have the need to perform a calculation that takes Dollar Sales/Units Sold.

Here is how I calculate Dollar Sales in my query
``````SUM(CONVERT(MONEY, CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate )
THEN ( DATA.total_qty * DATA.price
* DATA.co_rate )
ELSE ( 0 )
END)) AS [Wk_TY_Sales] ,
``````

Here is how I calculate Unit Sales
``````SUM(CONVERT(MONEY, ( CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(zzxdiscr.disc_perc, 0)
/ 100 ) )
ELSE ( 0 )
END ) * DATA.co_rate)) AS [Wk TY Unit Sales]
``````

I'm just not certain of the syntax to combine these two so that I get the [Wk TY AUR] more importantly I need to account for the divide by zero error since some weeks have a 0.00 for dollar sales/unit sales
0
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 25

Accepted Solution

jrb1 earned 250 total points
ID: 41765894
Does this work for you?

, CASE WHEN SUM(CASE WHEN ( ord_date BETWEEN '8/21/2016' AND '8/27/2016'
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(0, 0)
/ 100 ) )
ELSE ( 0 )
END * DATA.co_rate) = 0 THEN 0
ELSE SUM(CASE WHEN ( ord_date BETWEEN '8/21/2016' AND '8/27/2016'
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(0, 0)
/ 100 ) )
ELSE ( 0 )
END * DATA.co_rate) / SUM(CASE WHEN ( ord_date BETWEEN '8/21/2016' AND '8/27/2016'
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(0, 0)
/ 100 ) )
ELSE ( 0 )
END * DATA.co_rate) END AS [Wk \$ / Units]

If the Unit Sales are 0, this returns 0.  Otherwise it divides the two values.
0

LVL 43

Assisted Solution

zephyr_hex (Megan) earned 125 total points
ID: 41765960
I prefer this pattern when dealing with operations on aggregations:

``````SELECT *, CASE WHEN u.Units = 0 THEN 0 ELSE u.Sales / u.Units END
FROM (SELECT 99 AS Sales, 7 AS Units) u
``````

To apply this to your specific query:

``````SELECT *, CASE WHEN u.[Wk TY Unit Sales] = 0 THEN 0 ELSE u.k_TY_Sales / u.[Wk TY Unit Sales] END
FROM (/*  Your original query goes here  */) u
``````
0

LVL 43

Assisted Solution

zephyr_hex (Megan) earned 125 total points
ID: 41765964
You could also use a Common Table Expression:

``````;WITH cte (Units, Sales) AS (
SELECT 7 AS Units, 99 AS Sales

) SELECT *, CASE WHEN Units = 0 THEN 0 ELSE Sales/Units END
FROM cte
``````
0

LVL 69

Assisted Solution

Scott Pletcher earned 125 total points
ID: 41765980
I prefer to push the calcs down into CROSS APPLYs in order to assign an alias name to them.  That keeps the main code less "cluttered" to me.

SELECT /*...,*/
SUM(Wk_TY_Sales) AS Wk_TY_Sales,
SUM([Wk TY Unit Sales]) AS [Wk TY Unit Sales],
CASE WHEN SUM([Wk TY Unit Sales]) = 0.0 THEN 0.0
ELSE SUM([Wk_TY_Sales]) / SUM([Wk TY Unit Sales]) END AS [Wk TY AUR]
FROM dbo.tablename
CROSS APPLY (
SELECT CAST(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate )
THEN ( DATA.total_qty * DATA.price * DATA.co_rate )
ELSE ( 0 )
END AS money) AS [Wk_TY_Sales]
) AS assign_alias_names1
CROSS APPLY (
SELECT CAST(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate AND DATA.line_status = 'I' )
THEN ( DATA.total_qty * DATA.price - ( DATA.total_qty * DATA.price ) * ( ISNULL(zzxdiscr.disc_perc, 0) / 100.0 ) )
ELSE ( 0 )
END * DATA.co_rate AS money) AS [Wk TY Unit Sales]
) AS assign_alias_names2
0

Author Comment

ID: 41766899
Thank you for all of your help.
0

LVL 43

Expert Comment

ID: 41767213
@mburk1968 - if one or more of these responses answered your question, you can close the question and select the answers.
0

Author Comment

ID: 41774636
Thank you
0

Featured Post

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month3 days, 20 hours left to enroll