Solved

SQL Query Assistance

Posted on 2016-10-31
4
36 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 24

Accepted Solution

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 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

12 Experts available now in Live!

Get 1:1 Help Now