mburk1968
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
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER