Solved

SQL Query Calculation

Posted on 2016-08-22
7
62 Views
Last Modified: 2016-08-29
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] ,

Open in new window



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] 

Open in new window


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
Comment
Question by:mburk1968
7 Comments
 
LVL 25

Accepted Solution

by:
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 42

Assisted Solution

by:zephyr_hex (Megan)
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

Open in new window


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

Open in new window

0
 
LVL 42

Assisted Solution

by:zephyr_hex (Megan)
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

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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

by:mburk1968
ID: 41766899
Thank you for all of your help.
0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
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

by:mburk1968
ID: 41774636
Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now