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

mburk1968
mburk1968 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
kinda difficult to troubleshoot and amend your existing codes, so I would do it using CTE like this:

;with cte
as
(
	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 )
) 
Select a.*, 
round( a.custom / sum(a.Tot_FOB) over (partition by a.Contractor) * a.Tot_FOB, 2) Duty
from cte a
order by a.Contr , a.Ship_Dt

Open in new window

Author

Commented:
Thank You
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial