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
Medium Priority
48 Views
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
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;
``````
0
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
• 2

LVL 66

Expert Comment

ID: 41867194
0

Author Comment

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

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;

--
``````
0

Author Comment

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 ,
``````
0

## Featured Post

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
###### Suggested Courses
Course of the Month8 days, 5 hours left to enroll