• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

SQL Query Calculation

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
mburk1968
Asked:
mburk1968
4 Solutions
 
jrb1Commented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
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.

 
Scott PletcherSenior DBACommented:
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
 
mburk1968Author Commented:
Thank you for all of your help.
0
 
zephyr_hex (Megan)DeveloperCommented:
@mburk1968 - if one or more of these responses answered your question, you can close the question and select the answers.
0
 
mburk1968Author Commented:
Thank you
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now