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

mburk1968Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mburk1968Author Commented:
Thank You
0
PortletPaulEE Topic AdvisorCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.