Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Query Calculation

Posted on 2016-08-22
7
Medium Priority
?
104 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

606 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