Solved

SQL Query Calculation

Posted on 2016-08-22
7
83 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
[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
  • Learn & ask questions
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 43

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 43

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 69

Assisted Solution

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

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

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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