Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query Calculation

Posted on 2016-08-22
7
Medium Priority
?
96 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 1000 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 44

Assisted Solution

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

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 500 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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 44

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

916 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