Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Assistance

Posted on 2016-10-31
4
Medium Priority
?
48 Views
Last Modified: 2016-11-16
I have the following system generated query out of our software program. Its performing the following calculation.

( ( TOTAL_QTY * ( ( PRICE * TOTAL_QTY )
                          - ( ROUND(( COALESCE(zzxdiscr.disc_perc, 0) * 1.0
                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * H.CO_RATE ) AS SFC_CR_EXT_NET_AMT ,

Query 1 System
SELECT  CASE WHEN D.DIVISION = '' THEN SPACE(3)
             ELSE ' Division: ' + RTRIM(CAST(D.DIVISION AS CHAR(3)))
        END + CASE WHEN H.CUSTOMER = '' THEN SPACE(8)
                   ELSE ' Customer: ' + RTRIM(CAST(H.CUSTOMER AS CHAR(8)))
              END
        + CASE WHEN COALESCE(zzxcustr.cust_name, '') = '' THEN SPACE(40)
               ELSE ' Customer Name: '
                    + RTRIM(CAST(COALESCE(zzxcustr.cust_name, '') AS CHAR(40)))
          END AS cSKU ,
        H.DIVISION ,
        D.STYLE ,
        SZ.size_code AS SIZCODE ,
        ( (D.CMEM_NUM) ) AS CMEM_NUM ,
        ( (D.COLOR_CODE) ) AS COLOR_CODE ,
        ( (H.CO_RATE) ) AS CO_RATE ,
        ( (H.CUSTOMER) ) AS CUSTOMER ,
        ( (D.DIMENSION) ) AS DIMENSION ,
        ( ( H.DISC_AMT ) * H.CO_RATE ) AS DISC_AMT ,
        ( (D.DIVISION) ) AS DIVISION ,
        ( ( H.FRGT_AMT ) * H.CO_RATE ) AS FRGT_AMT ,
        ( ( H.INSU_AMT ) * H.CO_RATE ) AS INSU_AMT ,
        ( (D.LBL_CODE) ) AS LBL_CODE ,
        ( ( H.MISC_AMT ) * H.CO_RATE ) AS MISC_AMT ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(H.RETN_DATE AS VARCHAR), 1, 12))) ) AS RETN_DATE ,
        ( (D.ROYALTY) ) AS ROYALTY ,
        ( (D.ROY_CLS) ) AS ROY_CLS ,
        ( ( SCOL.std_cost ) * H.CO_RATE ) AS SFCCOST ,
        ( ( SC.std_cost * D.TOTAL_QTY ) * H.CO_RATE ) AS SFCDETEXTCOST2 ,
------------------------------------------------------------------------------------------------------------------------------------------------------------
 ( ( TOTAL_QTY * ( ( PRICE * TOTAL_QTY )
                          - ( ROUND(( COALESCE(zzxdiscr.disc_perc, 0) * 1.0
                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * H.CO_RATE ) AS SFC_CR_EXT_NET_AMT ,
--------------------------------------------------------------------------------------------------------------------------------------------------------------
         ( ( 1 ) * H.CO_RATE ) AS SFC_CUR_COUNT ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(H.CMEM_DATE AS VARCHAR), 1, 12))) ) AS SFDCRDM_DATE ,
        ( (COALESCE(zzxcustr.cust_name, '')) ) AS SFSCUST_NAME ,
        ( (D.STYLE) ) AS STYLE ,
        ( (D.TOTAL_QTY) ) AS TOTAL_QTY ,
        H.CO_CURR AS SFC_CURR_CODE ,
        H.CO_RATE AS SFC_RPTRATE
FROM    zznretnh H
        JOIN zznretnd D ON D.FKEY = H.PKEY
                           AND H.DOC_TYPE = 'CM'
        JOIN zzxscolr SC ON D.DIVISION = SC.division
                            AND D.STYLE = SC.style
                            AND D.COLOR_CODE = SC.color_code
                            AND D.LBL_CODE = SC.lbl_code
                            AND D.DIMENSION = SC.dimension
        JOIN zzxstylr SZ ON SC.fkey = SZ.pkey
        LEFT OUTER JOIN zzxcustr ON H.CUSTOMER = zzxcustr.customer
        LEFT OUTER JOIN zzxscolr SCOL ON D.DIVISION = SCOL.division
                                         AND D.STYLE = SCOL.style
                                         AND D.COLOR_CODE = SCOL.color_code
                                         AND D.LBL_CODE = SCOL.lbl_code
                                         AND D.DIMENSION = SCOL.dimension
        LEFT OUTER JOIN zzxdiscr ON D.DISCOUNT = zzxdiscr.discount
        LEFT JOIN zzordad2 AD ON AD.INV_NUM = D.INV_NUM
                                 AND AD.CUSTOMER = H.CUSTOMER
                                 AND AD.INV_NUM > 0
WHERE   ( (H.CUSTOMER = 'AMA0010') )
        AND ( (CONVERT(DATETIME, SUBSTRING(CAST(H.CMEM_DATE AS VARCHAR), 1, 12)) BETWEEN {TS '2014-03-01 00:00:00' }
                                                              AND
                                                              {TS '2014-03-27 00:00:00' }) );
[\code]

I need to adjust the formula in my SQL Report to do the same calculation.

ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE) AS ExtPrice
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN @FromDate AND @ToDate )
               ), 0) AS ReturnAmt ,

SQL Query

[code]
Report Query

SELECT  ORD.ship_date ,
        ORD.style ,
        ORD.color_code ,
        ORD.lbl_code ,
        SUM(ORD.total_qty) AS total_qty ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END AS IsFOB ,
        SUM(ORD.total_qty * ORD.price) AS GrossAmt ,
        SUM(ORD.total_qty * ORD.price
            - CASE WHEN ( ISNULL(D.disc_perc, 0) <> 0 )
                   THEN ( ORD.total_qty * ORD.price * ( D.disc_perc / 100 ) )
                   ELSE ( 0 )
              END) AS NetAmt ,
----------------------------------------------------------------------------------------------------------------------------
        ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE) AS ExtPrice
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN @FromDate AND @ToDate )
               ), 0) AS ReturnAmt ,
------------------------------------------------------------------------------------------------------------------------------------------------------
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls AS Sty_RoyClass ,
        ORD.Dtl_royalty AS Sty_Royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota AS Rights_Code ,
        Q.quota_name AS Rights_Code_Desc ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost AS ExtCost
FROM    [KLL All Order Detail With Style Data] AS ORD
        INNER JOIN zzxcustr AS C ON ORD.customer = C.customer
        INNER JOIN zzxroych AS Roy ON ORD.Dtl_roycls = Roy.ROY_CLS
                                      AND ORD.Dtl_royalty = Roy.ROYALTY
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    style ,
                                    dimension ,
                                    division ,
                                    color_code ,
                                    lbl_code ,
                                    template
                          FROM      zzdcosth
                        ) AS COST ON ORD.dimension = COST.dimension
                                     AND ORD.division = COST.division
                                     AND ORD.style = COST.style
                                     AND ORD.color_code = COST.color_code
                                     AND ORD.lbl_code = COST.lbl_code
        LEFT OUTER JOIN zzxdutyr AS Q ON ORD.Hdr_quota = Q.quota
        LEFT OUTER JOIN zzxdiscr AS D ON ORD.discount = D.discount
WHERE   ( ORD.ship_date BETWEEN @FromDate AND @ToDate )
        AND ( ORD.division = @Division )
GROUP BY ORD.style ,
        ORD.ship_date ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls ,
        ORD.Dtl_royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota ,
        Q.quota_name ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost
HAVING  ( ORD.Dtl_roycls IN ( @Roy_Classes ) )
        AND ( ORD.Dtl_royalty IN ( @Royalties ) )
ORDER BY Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.style ,
        C.cust_name;

Open in new window

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
  • 2
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41867194
Ok.  So what's your question?
0
 

Author Comment

by:mburk1968
ID: 41867257
Basically from query they are performing a calculation for the ReturnAmount  as follows.
( ( TOTAL_QTY * ( ( PRICE * TOTAL_QTY )
                           - ( ROUND(( COALESCE(zzxdiscr.disc_perc, 0) * 1.0
                                       / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
             / CASE WHEN TOTAL_QTY = 0 THEN 1
                    ELSE TOTAL_QTY
               END ) * H.CO_RATE ) AS SFC_CR_EXT_NET_AMT ,

I need to do the same in my query factoring in the zzxdiscr.disc_perc as above.

ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE) AS ExtPrice
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN @FromDate AND @ToDate )
               ), 0) AS ReturnAmt ,
0
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41868818
Try...

--

SELECT  ORD.ship_date ,
        ORD.style ,
        ORD.style_name ,
        ORD.color_code ,
        ORD.lbl_code ,
        SUM(ORD.total_qty) AS total_qty ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END AS IsFOB ,
        SUM(ORD.total_qty * ORD.price) AS GrossAmt ,
        SUM(ORD.total_qty * ORD.price
            - CASE WHEN ( ISNULL(D.disc_perc, 0) <> 0 )
                   THEN ( ORD.total_qty * ORD.price * ( D.disc_perc / 100 ) )
                   ELSE ( 0 )
              END) AS NetAmt ,
        ISNULL((
		
				SELECT SUM(RD.TOTAL_QTY * RD.PRICE)
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016' AND '09/30/2016' )

               ), 0) ReturnAmt ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls AS Sty_RoyClass ,
        ORD.Dtl_royalty AS Sty_Royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota AS Rights_Code ,
        Q.quota_name AS Rights_Code_Desc ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost AS ExtCost
FROM    [KLL All Order Detail With Style Data] AS ORD
        INNER JOIN zzxcustr AS C ON ORD.customer = C.customer
        INNER JOIN zzxroych AS Roy ON ORD.Dtl_roycls = Roy.ROY_CLS
                                      AND ORD.Dtl_royalty = Roy.ROYALTY
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    style ,
                                    dimension ,
                                    division ,
                                    color_code ,
                                    lbl_code ,
                                    template
                          FROM      zzdcosth
                        ) AS COST ON ORD.dimension = COST.dimension
                                     AND ORD.division = COST.division
                                     AND ORD.style = COST.style
                                     AND ORD.color_code = COST.color_code
                                     AND ORD.lbl_code = COST.lbl_code
        LEFT OUTER JOIN zzxdutyr AS Q ON ORD.Hdr_quota = Q.quota
        LEFT OUTER JOIN zzxdiscr AS D ON ORD.discount = D.discount
WHERE   ( ORD.ship_date BETWEEN '07/01/2016' AND '09/30/2016' )
        AND ( ORD.division = 'ADC' )
GROUP BY ORD.style ,
        ORD.style_name ,
        ORD.ship_date ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls ,
        ORD.Dtl_royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota ,
        Q.quota_name ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost
HAVING  ( ORD.Dtl_roycls IN ( 'CRA' ) )
ORDER BY Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.style ,
        C.cust_name;


--

Open in new window

0
 

Author Comment

by:mburk1968
ID: 41868979
Pawan,

 You are helping me on this same question just worded differently.

I'm attempting to do the following.

ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE) - ( COALESCE(D.disc_perc, 0) * 1.0
                                      / 100 ) * ) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * RH.CO_RATE ) 
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016'
                                           AND     '09/30/2016' )
               ), 0) ReturnAmtTest ,

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

610 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