Solved

SQL Query Assistance

Posted on 2016-10-31
4
30 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
  • 2
4 Comments
 
LVL 65

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 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now