Link to home
Start Free TrialLog in
Avatar of mburk1968
mburk1968Flag for United States of America

asked on

SQL Query Syntax to add subtotal calculation by Contractor for each row.

I have the following query that displays 40 records. I'm currently calculating a field [Tot_FOB] and [Custom] I need to add a field named [DUTY] which is a Calculation of [Custom]/ SubTotal by Contractor of the [Tot_FOB] * [Tot_FOB]

For example my data set displays 3 rows of data for Contractor 123103 I need to calculate the subtotal of the Tot_FOB for that Contractor which would be 591.88 so that I can use it to populate the [Duty]

so for the first row it would be 11952.77/591.88 * 223.17 = 4506.83 Duty

Contractor   Tot_FOB    Custom          Duty
123103         223.17       11952.77
123103         198.00       11952.77
123103         170.71       11952.77
584357         199.21        6988.33
584357         223.78        6988.33




SELECT  CASE WHEN H.CONTRACTOR = '' THEN ''
             ELSE RTRIM(CAST(H.CONTRACTOR AS CHAR(6)))
        END AS Contr ,
        ( (LOCTOP.LOC_NAME) ) AS Contr_Name ,
        CASE WHEN SHPMH.shpm_ref = '' THEN ''
             ELSE RTRIM(CAST(SHPMH.shpm_ref AS CHAR(30)))
        END AS Shpm_Ref ,
        ( (SHPMH.SHPM_BOL) ) AS Master_BOL ,
        CASE WHEN SHPMH.shpm_ref = '' THEN ''
             ELSE RTRIM(CAST(D.CNTR_NUM AS CHAR(6)))
        END AS Cntr_ID ,
        CASE WHEN D.SHPDATE = {TS '1900-01-01 00:00:00' } THEN ''
             ELSE D.SHPDATE
        END AS Ship_Dt ,
        ( (D.SHIPTO) ) AS Ship_To ,
        CASE WHEN D.PROD_NUM = 0 THEN ''
             ELSE RTRIM(CAST(D.PROD_NUM AS CHAR(24)))
        END AS Prod ,
        ( (DBO.BCFN_LINKEDSOPOINFOINT(H.PROD_NUM, D.OPEN_SEQ, 'O')) ) AS Linked_SO_Num ,
        CASE WHEN dbo.bcfn_LinkedSoPoInfoChar(H.prod_num, D.open_seq, 'C') = ''
             THEN ''
             ELSE RTRIM(CAST(dbo.bcfn_LinkedSoPoInfoChar(H.prod_num,
                                                         D.open_seq, 'C') AS CHAR(7)))
        END AS Customer ,
        CASE WHEN D.STYLE = '' THEN ''
             ELSE RTRIM(CAST(D.STYLE AS CHAR(12)))
        END AS Style ,
        ( (D.COLOR_CODE) ) AS Color ,
        CASE WHEN D.LBL_CODE = '' THEN ''
             ELSE RTRIM(CAST(D.LBL_CODE AS CHAR(7)))
        END AS Label ,
        ( (D.DIMENSION) ) AS DIMENSION ,
        ( ( D.COST * D.VNDR_CONVERSION ) * H.CO_RATE ) AS Cost ,
        ( (( CASE WHEN D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
                  ELSE D.WIP_TOTAL
             END ) * ( D.COST * D.VNDR_CONVERSION )) ) AS Tot_FOB ,
        ( (LOCSHIP.STATE) ) AS Ship_To_State ,
        ( (D.TOTAL_QTY) ) AS Ship_QTY ,
        ( (D.WIP_TOTAL) ) AS WIP ,
        ( (CASE WHEN D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
                ELSE D.WIP_TOTAL
           END ) ) AS Ctns ,
        CASE WHEN SHPMH.eta_date = {TS '1900-01-01 00:00:00' } THEN ''
             ELSE SHPMH.eta_date
        END AS [Shipment Arrival Date] ,
        CASE WHEN NULLIF(D.DUE_DATE, '1900-01-01 00:00:00') = {TS '1900-01-01 00:00:00' }
             THEN ''
             ELSE NULLIF(D.DUE_DATE, '1900-01-01 00:00:00')
        END AS [Delivery Date] ,
        ( (CASE WHEN D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
                ELSE 0
           END ) ) AS Bal_To_Ship ,
        ( (CASE WHEN D.STAGE = 'ISSUE' THEN D.WIP_TOTAL
                ELSE 0
           END ) ) AS Recd ,
        ( (D.LAST_STAGE) ) AS Last_Rcv ,
        ( (SUBSTRING(H.REF_NUM, 1, 2)) ) AS Coord ,
        ( (CASE WHEN D.SHP_OK = 'Y'
                     OR D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
                ELSE 0
           END ) ) AS UDF_ZZCPDRPB_038 ,
        CASE WHEN dbo.bcfn_LinkedSoPoInfoDate(H.prod_num, D.open_seq, 'S') = {TS '1900-01-01 00:00:00' }
             THEN ''
             ELSE RTRIM(CAST(dbo.bcfn_LinkedSoPoInfoDate(H.prod_num,
                                                         D.open_seq, 'S') AS VARCHAR(26)))
        END AS [Linked SO Start Date] ,
        CASE WHEN dbo.bcfn_LinkedSoPoInfoDate(H.prod_num, D.open_seq, 'E') = {TS '1900-01-01 00:00:00' }
             THEN ''
             ELSE RTRIM(CAST(dbo.bcfn_LinkedSoPoInfoDate(H.prod_num,
                                                         D.open_seq, 'E') AS VARCHAR(26)))
        END AS [Linked SO End Date] ,
        CASE WHEN LOCSHIP.city = '' THEN ''
             ELSE RTRIM(CAST(LOCSHIP.city AS CHAR(25)))
        END AS [Ship to - City] ,
        ( (H.CONTRACTOR) ) AS CONTRACTOR ,
        ( (H.CO_RATE) ) AS CO_RATE ,
        ( (H.DIVISION) ) AS DIVISION ,
        ( (NULLIF(D.DUE_DATE, '1900-01-01 00:00:00')) ) AS DUE_DATE ,
        ( (D.LBL_CODE) ) AS LBL_CODE ,
        ( (D.OPEN_SEQ) ) AS OPEN_SEQ ,
        ( (D.PROD_LINE) ) AS PROD_LINE ,
        ( (D.PROD_NUM) ) AS PROD_NUM ,
        ( (H.REF_NUM) ) AS REF_NUM ,
        ( (SHPMH.ETA_DATE) ) AS SFDARRIVAL_DATE ,
        ( (DBO.BCFN_LINKEDSOPOINFOCHAR(H.PROD_NUM, D.OPEN_SEQ, 'C')) ) AS SFSLINKCUSTOMER ,
        ( (DBO.BCFN_LINKEDSOPOINFODATE(H.PROD_NUM, D.OPEN_SEQ, 'E')) ) AS SFSLINKEND_DATE ,
        ( (DBO.BCFN_LINKEDSOPOINFODATE(H.PROD_NUM, D.OPEN_SEQ, 'S')) ) AS SFSLINKSTART_DATE ,
        ( (LOCSHIP.CITY) ) AS SFSSHIPTO_CITY ,
        ( (LOCSHIP.STATE) ) AS SFSSHIPTO_STATE ,
        ( (SHPMH.SHPM_REF) ) AS SFSSHPM_REF ,
        ( (D.TOTAL_QTY) ) AS SFS_TOTAL_QTY ,
        ( (D.SHP_OK) ) AS SHP_OK ,
        ( (D.SHP_SEQ) ) AS SHP_SEQ ,
        ( (D.STAGE) ) AS STAGE ,
        ( (D.STAGE_NUM) ) AS STAGE_NUM ,
        ( (D.STYLE) ) AS STYLE ,
        ( (D.TOTAL_QTY) ) AS TOTAL_QTY ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(D.TRANS_DATE AS VARCHAR), 1, 12))) ) AS TRANS_DATE ,
        ( (CASE WHEN D.STAGE_NUM <> 1 THEN D.WIP_TOTAL
                ELSE 0
           END ) ) AS UDF_ZZCPDRPB_010 ,
        ( (CASE WHEN D.LAST_STAGE = 'Y'
                THEN ( CONVERT(DATETIME, SUBSTRING(CAST(D.TRANS_DATE AS VARCHAR),
                                                   1, 12)) )
                ELSE ''
           END ) ) AS UDF_ZZCPDRPB_034 ,
        ( (CASE WHEN D.LBL_CODE <> '99'
                     AND D.LBL_CODE <> '100'
                     AND D.LBL_CODE <> '101'
                THEN ( DBO.BCFN_LINKEDSOPOINFOCHAR(H.PROD_NUM, D.OPEN_SEQ, 'C') )
                ELSE 'SHWROOM'
           END ) ) AS UDF_ZZCPDRPB_042 ,
        ( (SUBSTRING(H.REF_NUM, 1, 2)) ) AS UDF_ZZCPDRPB_045 ,
        ( (C.MIN_MULTIPLE) ) AS UDF_ZZCPDRPB_053 ,
        ( (D.WIP_TOTAL / ( C.MIN_MULTIPLE )) ) AS UDF_ZZCPDRPB_054 ,
        H.CO_CURR AS SFC_CURR_CODE ,
        H.CO_RATE AS SFC_RPTRATE ,
        SHPMH.PKEY ,
        EXT.Custom
FROM    ZZCORDRH H
        JOIN ZZCORDRD D ON H.PKEY = D.FKEY
        JOIN ZZXSCOLR C ON H.DIVISION = C.DIVISION
                           AND D.STYLE = C.STYLE
                           AND D.COLOR_CODE = C.COLOR_CODE
                           AND D.LBL_CODE = C.LBL_CODE
                           AND D.DIMENSION = C.DIMENSION
        JOIN ZZXSTYLR S ON S.PKEY = C.FKEY
        LEFT JOIN ZZMSHPMH R ON D.SHP_SEQ = R.SHIPMENT_NUM
        LEFT OUTER JOIN ZZCVNDRH VH ON VH.DIVISION = C.DIVISION
                                       AND VH.STYLE = C.STYLE
                                       AND VH.COLOR_CODE = C.COLOR_CODE
                                       AND VH.LBL_CODE = C.LBL_CODE
                                       AND VH.DIMENSION = C.DIMENSION
                                       AND VH.CONTRACTOR = H.CONTRACTOR
        LEFT OUTER JOIN ZZTOPCONTRNAME LOCTOP ON H.CONTRACTOR = LOCTOP.LOCATION
        LEFT OUTER JOIN ZZMSHPMH SHPMH ON D.SHP_SEQ = SHPMH.SHIPMENT_NUM
        LEFT OUTER JOIN ZZXLOCAR LOCSHIP ON D.SHIPTO = LOCSHIP.LOCATION
        LEFT OUTER JOIN ( SELECT    SUM([UDF_Numeric_Data]) AS Custom ,
                                    JoinFromHdrPKey
                          FROM      [dataKLL].[dbo].[KLL Extender UDF Values with Data]
                          WHERE     GROUP_NAME = 'NOTES FOR TRAFFIC'
                                    AND UDF_FIELD IN ( 'DUTY1AMT', 'DUTY2AMT' )
                          GROUP BY  JoinFromHdrPKey
                        ) AS EXT ON EXT.JoinFromHdrPKey = SHPMH.pkey
WHERE   ( D.STAGE = 'TRANSIT' )
        AND ( D.WIP_TOTAL > 0 )
        AND ( D.DUE_DATE BETWEEN @FromDate AND @ToDate )
        OR ( D.LAST_STAGE = 'Y' )
        AND ( D.DUE_DATE BETWEEN @FromDate AND @ToDate )
ORDER BY Contr ,
        Ship_Dt;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mburk1968

ASKER

Thank You
just as a note. It is not necessary to use a cte to achieve the same result, a more traditional "derived table" would be:
Select
     a.*
   , round( a.custom / sum(a.Tot_FOB) over (partition by a.Contractor) * a.Tot_FOB, 2) Duty
from (
	SELECT  CASE WHEN H.CONTRACTOR = '' THEN ''
				 ELSE RTRIM(CAST(H.CONTRACTOR AS CHAR(6)))
			END AS Contr ,
			( (LOCTOP.LOC_NAME) ) AS Contr_Name ,
			CASE WHEN SHPMH.shpm_ref = '' THEN ''
				 ELSE RTRIM(CAST(SHPMH.shpm_ref AS CHAR(30)))
			END AS Shpm_Ref ,
			( (SHPMH.SHPM_BOL) ) AS Master_BOL ,
			CASE WHEN SHPMH.shpm_ref = '' THEN ''
				 ELSE RTRIM(CAST(D.CNTR_NUM AS CHAR(6)))
			END AS Cntr_ID ,
			CASE WHEN D.SHPDATE = {TS '1900-01-01 00:00:00' } THEN ''
				 ELSE D.SHPDATE
			END AS Ship_Dt ,
			( (D.SHIPTO) ) AS Ship_To ,
			CASE WHEN D.PROD_NUM = 0 THEN ''
				 ELSE RTRIM(CAST(D.PROD_NUM AS CHAR(24)))
			END AS Prod ,
			( (DBO.BCFN_LINKEDSOPOINFOINT(H.PROD_NUM, D.OPEN_SEQ, 'O')) ) AS Linked_SO_Num ,
			CASE WHEN dbo.bcfn_LinkedSoPoInfoChar(H.prod_num, D.open_seq, 'C') = ''
				 THEN ''
				 ELSE RTRIM(CAST(dbo.bcfn_LinkedSoPoInfoChar(H.prod_num,
															 D.open_seq, 'C') AS CHAR(7)))
			END AS Customer ,
			CASE WHEN D.STYLE = '' THEN ''
				 ELSE RTRIM(CAST(D.STYLE AS CHAR(12)))
			END AS Style ,
			( (D.COLOR_CODE) ) AS Color ,
			CASE WHEN D.LBL_CODE = '' THEN ''
				 ELSE RTRIM(CAST(D.LBL_CODE AS CHAR(7)))
			END AS Label ,
			( (D.DIMENSION) ) AS DIMENSION ,
			( ( D.COST * D.VNDR_CONVERSION ) * H.CO_RATE ) AS Cost ,
			( (( CASE WHEN D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
					  ELSE D.WIP_TOTAL
				 END ) * ( D.COST * D.VNDR_CONVERSION )) ) AS Tot_FOB ,
			( (LOCSHIP.STATE) ) AS Ship_To_State ,
			( (D.TOTAL_QTY) ) AS Ship_QTY ,
			( (D.WIP_TOTAL) ) AS WIP ,
			( (CASE WHEN D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
					ELSE D.WIP_TOTAL
			   END ) ) AS Ctns ,
			CASE WHEN SHPMH.eta_date = {TS '1900-01-01 00:00:00' } THEN ''
				 ELSE SHPMH.eta_date
			END AS [Shipment Arrival Date] ,
			CASE WHEN NULLIF(D.DUE_DATE, '1900-01-01 00:00:00') = {TS '1900-01-01 00:00:00' }
				 THEN ''
				 ELSE NULLIF(D.DUE_DATE, '1900-01-01 00:00:00')
			END AS [Delivery Date] ,
			( (CASE WHEN D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
					ELSE 0
			   END ) ) AS Bal_To_Ship ,
			( (CASE WHEN D.STAGE = 'ISSUE' THEN D.WIP_TOTAL
					ELSE 0
			   END ) ) AS Recd ,
			( (D.LAST_STAGE) ) AS Last_Rcv ,
			( (SUBSTRING(H.REF_NUM, 1, 2)) ) AS Coord ,
			( (CASE WHEN D.SHP_OK = 'Y'
						 OR D.LAST_STAGE = 'Y' THEN D.TOTAL_QTY
					ELSE 0
			   END ) ) AS UDF_ZZCPDRPB_038 ,
			CASE WHEN dbo.bcfn_LinkedSoPoInfoDate(H.prod_num, D.open_seq, 'S') = {TS '1900-01-01 00:00:00' }
				 THEN ''
				 ELSE RTRIM(CAST(dbo.bcfn_LinkedSoPoInfoDate(H.prod_num,
															 D.open_seq, 'S') AS VARCHAR(26)))
			END AS [Linked SO Start Date] ,
			CASE WHEN dbo.bcfn_LinkedSoPoInfoDate(H.prod_num, D.open_seq, 'E') = {TS '1900-01-01 00:00:00' }
				 THEN ''
				 ELSE RTRIM(CAST(dbo.bcfn_LinkedSoPoInfoDate(H.prod_num,
															 D.open_seq, 'E') AS VARCHAR(26)))
			END AS [Linked SO End Date] ,
			CASE WHEN LOCSHIP.city = '' THEN ''
				 ELSE RTRIM(CAST(LOCSHIP.city AS CHAR(25)))
			END AS [Ship to - City] ,
			( (H.CONTRACTOR) ) AS CONTRACTOR ,
			( (H.CO_RATE) ) AS CO_RATE ,
			( (H.DIVISION) ) AS DIVISION ,
			( (NULLIF(D.DUE_DATE, '1900-01-01 00:00:00')) ) AS DUE_DATE ,
			( (D.LBL_CODE) ) AS LBL_CODE ,
			( (D.OPEN_SEQ) ) AS OPEN_SEQ ,
			( (D.PROD_LINE) ) AS PROD_LINE ,
			( (D.PROD_NUM) ) AS PROD_NUM ,
			( (H.REF_NUM) ) AS REF_NUM ,
			( (SHPMH.ETA_DATE) ) AS SFDARRIVAL_DATE ,
			( (DBO.BCFN_LINKEDSOPOINFOCHAR(H.PROD_NUM, D.OPEN_SEQ, 'C')) ) AS SFSLINKCUSTOMER ,
			( (DBO.BCFN_LINKEDSOPOINFODATE(H.PROD_NUM, D.OPEN_SEQ, 'E')) ) AS SFSLINKEND_DATE ,
			( (DBO.BCFN_LINKEDSOPOINFODATE(H.PROD_NUM, D.OPEN_SEQ, 'S')) ) AS SFSLINKSTART_DATE ,
			( (LOCSHIP.CITY) ) AS SFSSHIPTO_CITY ,
			( (LOCSHIP.STATE) ) AS SFSSHIPTO_STATE ,
			( (SHPMH.SHPM_REF) ) AS SFSSHPM_REF ,
			( (D.TOTAL_QTY) ) AS SFS_TOTAL_QTY ,
			( (D.SHP_OK) ) AS SHP_OK ,
			( (D.SHP_SEQ) ) AS SHP_SEQ ,
			( (D.STAGE) ) AS STAGE ,
			( (D.STAGE_NUM) ) AS STAGE_NUM ,
			( (D.STYLE) ) AS STYLE ,
			( (D.TOTAL_QTY) ) AS TOTAL_QTY ,
			( (CONVERT(DATETIME, SUBSTRING(CAST(D.TRANS_DATE AS VARCHAR), 1, 12))) ) AS TRANS_DATE ,
			( (CASE WHEN D.STAGE_NUM <> 1 THEN D.WIP_TOTAL
					ELSE 0
			   END ) ) AS UDF_ZZCPDRPB_010 ,
			( (CASE WHEN D.LAST_STAGE = 'Y'
					THEN ( CONVERT(DATETIME, SUBSTRING(CAST(D.TRANS_DATE AS VARCHAR),
													   1, 12)) )
					ELSE ''
			   END ) ) AS UDF_ZZCPDRPB_034 ,
			( (CASE WHEN D.LBL_CODE <> '99'
						 AND D.LBL_CODE <> '100'
						 AND D.LBL_CODE <> '101'
					THEN ( DBO.BCFN_LINKEDSOPOINFOCHAR(H.PROD_NUM, D.OPEN_SEQ, 'C') )
					ELSE 'SHWROOM'
			   END ) ) AS UDF_ZZCPDRPB_042 ,
			( (SUBSTRING(H.REF_NUM, 1, 2)) ) AS UDF_ZZCPDRPB_045 ,
			( (C.MIN_MULTIPLE) ) AS UDF_ZZCPDRPB_053 ,
			( (D.WIP_TOTAL / ( C.MIN_MULTIPLE )) ) AS UDF_ZZCPDRPB_054 ,
			H.CO_CURR AS SFC_CURR_CODE ,
			H.CO_RATE AS SFC_RPTRATE ,
			SHPMH.PKEY ,
			EXT.Custom
	FROM    ZZCORDRH H
			JOIN ZZCORDRD D ON H.PKEY = D.FKEY
			JOIN ZZXSCOLR C ON H.DIVISION = C.DIVISION
							   AND D.STYLE = C.STYLE
							   AND D.COLOR_CODE = C.COLOR_CODE
							   AND D.LBL_CODE = C.LBL_CODE
							   AND D.DIMENSION = C.DIMENSION
			JOIN ZZXSTYLR S ON S.PKEY = C.FKEY
			LEFT JOIN ZZMSHPMH R ON D.SHP_SEQ = R.SHIPMENT_NUM
			LEFT OUTER JOIN ZZCVNDRH VH ON VH.DIVISION = C.DIVISION
										   AND VH.STYLE = C.STYLE
										   AND VH.COLOR_CODE = C.COLOR_CODE
										   AND VH.LBL_CODE = C.LBL_CODE
										   AND VH.DIMENSION = C.DIMENSION
										   AND VH.CONTRACTOR = H.CONTRACTOR
			LEFT OUTER JOIN ZZTOPCONTRNAME LOCTOP ON H.CONTRACTOR = LOCTOP.LOCATION
			LEFT OUTER JOIN ZZMSHPMH SHPMH ON D.SHP_SEQ = SHPMH.SHIPMENT_NUM
			LEFT OUTER JOIN ZZXLOCAR LOCSHIP ON D.SHIPTO = LOCSHIP.LOCATION
			LEFT OUTER JOIN ( SELECT    SUM([UDF_Numeric_Data]) AS Custom ,
										JoinFromHdrPKey
							  FROM      [dataKLL].[dbo].[KLL Extender UDF Values with Data]
							  WHERE     GROUP_NAME = 'NOTES FOR TRAFFIC'
										AND UDF_FIELD IN ( 'DUTY1AMT', 'DUTY2AMT' )
							  GROUP BY  JoinFromHdrPKey
							) AS EXT ON EXT.JoinFromHdrPKey = SHPMH.pkey
	WHERE   ( D.STAGE = 'TRANSIT' )
			AND ( D.WIP_TOTAL > 0 )
			AND ( D.DUE_DATE BETWEEN @FromDate AND @ToDate )
			OR ( D.LAST_STAGE = 'Y' )
			AND ( D.DUE_DATE BETWEEN @FromDate AND @ToDate )
)  AS a
order by a.Contr , a.Ship_Dt

Open in new window