multiple case statements result in rows being duplicated

Question

I have the following query, the output creates 1 row per ConsolID. I have inserted a number of Case Statements which check when the field XV_Name contains numerous possible answers like Master Date, Planning Note etc. When the answers match the field name, then the Case Statement will return the data in the XV_Data field. XV_Name and XV_Data are fields contained in a table that contains custom fields in the XV_Name field, and the contents related to the field are stored in XV_Data. Above is an example of the custom fields in the consol. Each field appears in its own column, but the trouble is that each field that has data in it results in a new row in the output, such that a consol can be repeated 19 times. I tried using Select Distinct, but that resulted in an error because of the ``` CASE WHEN ISNULL(HeaderCount, 0) = 0 THEN 'N' ELSE 'Y' END AS HeaderExists, ``` statement in the Select . Sorry if the question is a bit jumbled - I hope someone can supply a solution as to how I can get one row of data for each console. ``` CREATE FUNCTION Report_ConsolProfileReport_With_Custom_fields_cl ( @JH_GC uniqueidentifier, @CurrentCountry char(2), @JK_RL_NKLoad char(5), @JK_RL_NKDischarge char(5), @JW_RL_NKLoad char(5), @JW_RL_NKDischarge char(5), @JW_FromDT datetime, @JW_ToDT datetime, @JW_FromAT datetime, @JW_ToAT datetime, @TransportMode char(3), @OrderedStorageClassCodesList varchar(1000), @OnlyFirstLeg char(1), @OnlyLastLeg char(1), @Gateway varchar(3) ) RETURNS TABLE AS RETURN SELECT CASE WHEN ISNULL(HeaderCount, 0) = 0 THEN 'N' ELSE 'Y' END AS HeaderExists, --CONSOL DETAILS JK_UniqueConsignRef AS ConsolID, JK_AgentType AS Type, JK_TransportMode AS Trans, JK_ConsolMode AS Mode, JK_IsNeutralMaster AS Neutral, JK_MasterBillNum AS Master, JK_RL_NKLoadPort AS FirstLoadPort, JK_RL_NKDischargePort AS LastDischargePort, MainConTrans.JW_Vessel AS Vessel, MainConTrans.JW_VoyageFlight AS Voyage, SendAgent.OH_PK AS SendAgentPK, RecvAgent.OH_PK AS RecvAgentPK, SendAgent.OH_Code AS SendAgentCode, SendAgent.OH_FullName AS SendAgentName, RecvAgent.OH_Code AS RecvAgentCode, RecvAgent.OH_FullName AS RecvAgentName, CoLoad.OH_PK AS CoLoadPK, CoLoad.OH_Code AS CoLoadCode, CoLoad.OH_FullName AS CoLoadName, MainConTrans.JW_ETD AS ETD, MainConTrans.JW_ATD AS ATD, MainConTrans.JW_ETA AS ETA, MainConTrans.JW_ATA AS ATA, FirstLastConsolTransport.FirstLoad_ETD AS ETDFirstLegLoad, FirstLastConsolTransport.FirstLoad_ATD AS ATDFirstLegLoad, FirstLastConsolTransport.LastDischarge_ETA AS ETALastLegDischarge, FirstLastConsolTransport.LastDischarge_ATA AS ATALastLegDischarge, FirstLastConsolTransport.FirstLoad_Port AS FirstLegLoadPort, FirstLastConsolTransport.LastDischarge_Port AS LastLegDischargePort, JK_SystemCreateTimeUtc AS RegisteredDate, JK_PrepaidCollect AS Terms, Carrier.OH_PK AS CarrierPK, Carrier.OH_Code AS CarrierCode, Carrier.OH_FullName AS CarrierName, JK_BookingReference AS BookingRef, JK_AgentsReference AS AgentRef, MainConTrans.JA_DocumentaryCutoff AS DocsCutOff, JK_CustomsReference AS EntryNumber, case JK_IsForwarding WHEN 1 then 'Y' else 'N' END AS IsForward, CASE JK_IsCancelled WHEN 0 THEN 'Y' ELSE 'N' END AS IsActiveConsol, CASE When XV_Name = 'Master Booking' Then XV_DATA END AS MasterBooking, CASE When XV_Name = 'Planning Note' Then XV_DATA END AS PlanningNote, CASE When XV_Name = 'Ad Hoc Booking' Then XV_DATA END AS AdHocBooking, CASE When XV_Name = 'Status' Then XV_DATA END AS Status, CASE When XV_Name = 'Day Of the Week' Then XV_DATA END AS DayoftheWeek, CASE When XV_Name = 'Booking Month' Then XV_DATA END AS BookingMonth, CASE When XV_Name = 'Commodity Code' Then XV_DATA END AS CommodityCode, CASE When XV_Name = 'Booked Skids' Then XV_DATA END AS BookedSkids, CASE When XV_Name = 'Booked Weight (Kg)' Then XV_DATA END AS BookedWeightKg, CASE When XV_Name = 'Booked Boxes' Then XV_DATA END AS BookedBoxes, CASE When XV_Name = 'Last Flight' Then XV_DATA END AS LastFlight, CASE When XV_Name = 'Pre-Booking Note' Then XV_DATA END AS PreBookingNote, CASE When XV_Name = 'Airline Reference' Then XV_DATA END AS AirlineReference, CASE When XV_Name = 'Ground Handler' Then XV_DATA END AS GroundHandler, CASE When XV_Name = 'Comment' Then XV_DATA END AS Comment, CASE When XV_Name = 'Multi Shipper' Then XV_DATA END AS MultiShipper, CASE When XV_Name = 'Pick Up Points' Then XV_DATA END AS Pickuppoints, CASE When XV_Name = 'Booking Confirmation Sent' Then XV_DATA END AS BookingConfirmationSent, CASE When XV_Name = 'Final Master' Then XV_DATA END AS FinalMaster, CASE When XV_Name = 'FWB Message Sent' Then XV_DATA END AS FWBMessageSent, CASE When XV_Name = 'Invoice Notes' Then XV_DATA END AS InvoiceNotes, JK_CarrierContractNumber AS CarrierContractNumber, TotalCO2e = JCO_CO2eWTW, StatusCO2e = JCO_Status, --SHIPMENT ATTACH TO CONSOL --Excludes the Weight, Volume & Chargeable for CoLoad House Weight = Totals.ConsolWeight, WeightUnit = Totals.ConsolWeightUnit, Volume = Totals.ConsolVolume, VolumeUnit = Totals.ConsolVolumeUnit, Chargeable = Totals.ConsolChargeable, ChargeableUnit = Totals.ConsolChargeableUnit, Totals.LoadingMeters AS LoadingMeters, --JOB INVOICE SUMMARY FOR SHIPMENT ATTACH TO CONSOL RTRIM(CASE WHEN JobConsolBranchCount.JN_JKBranchCount = 1 OR JobConsolBranchCount.JN_JK is NULL THEN JobConsolBranchCount.GB_Code ELSE 'Multiple Branches' END) AS Branch, RTRIM(CASE WHEN JobConsolDepartmentCount.JN_JKDepartmentCount = 1 OR JobConsolDepartmentCount.JN_JK is NULL THEN JobConsolDepartmentCount.GE_Code ELSE 'Multiple Depts' END) AS Dept, SummedJobProfit.Amount AS Amount, SummedJobProfit.REVAmount, SummedJobProfit.WIPAmount, SummedJobProfit.ACRAmount, SummedJobProfit.CSTAmount, --CONSOL RATINGS AND EXCHANGE JK_ConsolChargeable AS ConsolChargeable, JK_ConsolChargeableRate AS ChargeableRate, Containers.TotalTEU AS TEU, Containers.ContainerCount AS ContainerCount, --SHIPMENTS ISNULL(Shipments.Shipments, 0) AS Shipments, ISNULL(Shipments.Masters, 0) AS Masters, ISNULL(Shipments.House, 0) AS House, CountOther, Count1, Count2, Count3, Count4, Count5, Count6, Count7, Count8, Count9, Count10, Count11, Count12, Count13, Count14, Count15 FROM dbo.JobConsol LEFT JOIN ( SELECT JN_JK, MIN(GB_Code) AS GB_Code, COUNT(DISTINCT JH_GB) AS JN_JKBranchCount FROM dbo.JobConShipLink JOIN dbo.JobHeader ON JH_ParentID = JN_JS AND JH_GC = @JH_GC JOIN dbo.GLBBranch ON JH_GB = GB_PK GROUP BY JN_JK ) AS JobConsolBranchCount ON JobConsolBranchCount.JN_JK = JK_PK LEFT JOIN ( SELECT JN_JK, MIN(GE_Code) AS GE_Code, COUNT(DISTINCT JH_GE) AS JN_JKDepartmentCount FROM dbo.JobConShipLink JOIN dbo.JobHeader ON JH_ParentID = JN_JS AND JH_GC = @JH_GC JOIN dbo.GlbDepartment ON GE_PK = JH_GE GROUP BY JN_JK ) AS JobConsolDepartmentCount ON JobConsolDepartmentCount.JN_JK = JK_PK JOIN dbo.csfn_ConsolsForTransportCriteriaWithLocations(NULL, NULL, @JW_RL_NKLoad, @JW_RL_NKDischarge, @JW_FromDT, @JW_ToDT, @JW_FromAT, @JW_ToAT, NULL, NULL) AS csfn_Criteria ON csfn_Criteria.JK_PK = JobConsol.JK_PK LEFT JOIN dbo.ViewFirstLastConsolTransport AS FirstLastConsolTransport ON FirstLastConsolTransport.ParentType = 'CON' AND FirstLastConsolTransport.JK = JobConsol.JK_PK LEFT JOIN ( SELECT LocoCode FROM dbo.csfn_LocoReportingZones(NULL) WHERE ZoneCode = @JW_RL_NKLoad GROUP BY LocoCode ) AS LegLoadZone ON LegLoadZone.LocoCode = FirstLastConsolTransport.FirstLoad_Port AND LEN(@JW_RL_NKLoad) = 4 LEFT JOIN ( SELECT LocoCode FROM dbo.csfn_LocoReportingZones(NULL) WHERE ZoneCode = @JW_RL_NKDischarge GROUP BY LocoCode ) AS LegDischargeZone ON LegDischargeZone.LocoCode = FirstLastConsolTransport.LastDischarge_Port AND LEN(@JW_RL_NKDischarge) = 4 LEFT JOIN ( SELECT LocoCode FROM dbo.csfn_LocoReportingZones(NULL) WHERE ZoneCode = @JK_RL_NKLoad GROUP BY LocoCode ) AS ConsolLoadZone ON ConsolLoadZone.LocoCode = JobConsol.JK_RL_NKLoadPort AND LEN(@JK_RL_NKLoad) = 4 LEFT JOIN ( SELECT LocoCode FROM dbo.csfn_LocoReportingZones(NULL) WHERE ZoneCode = @JK_RL_NKDischarge GROUP BY LocoCode ) AS ConsolDischargeZone ON ConsolDischargeZone.LocoCode = JobConsol.JK_RL_NKDischargePort AND LEN(@JK_RL_NKDischarge) = 4 LEFT JOIN dbo.csfn_MainConsolTransport(@CurrentCountry) AS MainConTrans ON MainConTrans.JW_JK = JobConsol.JK_PK LEFT JOIN dbo.GenCustomAddonValue on GenCustomAddonValue.XV_ParentID = JobConsol.JK_PK LEFT JOIN dbo.OrgAddress AS SendingAgentAddress ON SendingAgentAddress.OA_PK = JobConsol.JK_OA_SendingForwarderAddress LEFT JOIN dbo.OrgAddress AS ReceivingAgentAddress ON ReceivingAgentAddress.OA_PK = JobConsol.JK_OA_ReceivingForwarderAddress LEFT JOIN dbo.OrgAddress AS CarrierAddress ON CarrierAddress.OA_PK = JobConsol.JK_OA_ShippingLineAddress LEFT JOIN dbo.OrgAddress AS CreditorAddress ON CreditorAddress.OA_PK = JobConsol.JK_OA_CreditorAddress LEFT JOIN dbo.OrgHeader AS SendAgent ON SendAgent.OH_PK = SendingAgentAddress.OA_OH LEFT JOIN dbo.OrgHeader AS RecvAgent ON RecvAgent.OH_PK = ReceivingAgentAddress.OA_OH LEFT JOIN dbo.OrgHeader AS CoLoad ON CoLoad.OH_PK = CreditorAddress.OA_OH LEFT JOIN dbo.OrgHeader AS Carrier ON Carrier.OH_PK = CarrierAddress.OA_OH LEFT JOIN dbo.vw_ConsolTotals AS Totals ON Totals.JK = JobConsol.JK_PK LEFT JOIN ( SELECT JN_JK AS JH_JS, SUM(AL_LineAmount) AS Amount, NULLIF(SUM(REVAmount), .000) AS REVAmount, NULLIF(SUM(WIPAmount), .000) AS WIPAmount, NULLIF(SUM(ACRAmount), .000) AS ACRAmount, NULLIF(SUM(CSTAmount), .000) AS CSTAmount FROM dbo.JobHeader JOIN dbo.vw_NonReversedClassifiedTransactionLineAmounts ON JH_PK = AL_JH JOIN dbo.JobConShipLink ON JH_ParentID = JN_JS AND JH_GC = @JH_GC GROUP BY JN_JK ) AS SummedJobProfit ON JobConsol.JK_PK = SummedJobProfit.JH_JS LEFT JOIN ( SELECT JN_JK AS JH_JK, COUNT(JN_JS) AS HeaderCount FROM dbo.JobHeader JOIN dbo.JobConShipLink ON JH_ParentID = JN_JS AND JH_GC = @JH_GC GROUP BY JN_JK ) AS JobHeader ON JobConsol.JK_PK = JH_JK LEFT JOIN ( SELECT JobContainer.JC_JK AS ConsolPK, COUNT(JobContainer.JC_ContainerJobID) AS ContainerCount, SUM(RefContainer.RC_TEU * JobContainer.JC_ContainerCount) AS TotalTEU, SUM(CountOther) AS CountOther, SUM(Count1) AS Count1, SUM(Count2) AS Count2, SUM(Count3) AS Count3, SUM(Count4) AS Count4, SUM(Count5) AS Count5, SUM(Count6) AS Count6, SUM(Count7) AS Count7, SUM(Count8) AS Count8, SUM(Count9) AS Count9, SUM(Count10) AS Count10, SUM(Count11) AS Count11, SUM(Count12) AS Count12, SUM(Count13) AS Count13, SUM(Count14) AS Count14, SUM(Count15) AS Count15 FROM dbo.JobContainer LEFT JOIN dbo.RefContainer ON JobContainer.JC_RC = RefContainer.RC_PK LEFT JOIN dbo.ContainerSummaryFunction(@OrderedStorageClassCodesList) ON JobContainer.JC_PK = ContainerSummaryFunction.JC_PK GROUP BY JobContainer.JC_JK ) AS Containers ON Containers.ConsolPK = JobConsol.JK_PK LEFT JOIN ( SELECT JK = shpLink.JN_JK, Shipments = SUM(CASE WHEN shp.JS_ShipmentType IN ('STD', 'HLS') AND masLink.JN_JK is NULL THEN 1 ELSE 0 END), -- STD OR HLS with NO master on this Consol Masters = SUM(CASE WHEN shp.JS_ShipmentType IN ('ASM', 'BCN', 'CLD', 'CLB') THEN 1 ELSE 0 END), -- ASM, BCN, CLD, CLB of any level House = SUM(CASE WHEN shp.JS_ShipmentType IN ('STD', 'HLS') AND masLink.JN_JK is NOT NULL THEN 1 ELSE 0 END) -- STD OR HLS with master on this Consol FROM dbo.JobConShipLink AS shpLink JOIN dbo.JobShipment AS shp ON shp.JS_PK = shpLink.JN_JS LEFT JOIN dbo.JobConShipLink AS masLink ON masLink.JN_JK = shpLink.JN_JK AND masLink.JN_JS = shp.JS_JS_ColoadMasterShipment WHERE shp.JS_IsCancelled = 0 GROUP BY shpLink.JN_JK ) AS Shipments ON Shipments.JK = JobConsol.JK_PK LEFT JOIN dbo.JobCO2e ON JCO_ParentID = JobConsol.JK_PK AND JCO_ParentTableCode = 'JK' AND JCO_Type = '' WHERE JobConsol.JK_AgentType 'CLM' AND ( JobConsol.JK_TransportMode = @TransportMode OR @TransportMode = '' OR @TransportMode IS NULL ) AND ( ISNULL(@OnlyFirstLeg, '') = '' OR ISNULL(@JW_RL_NKLoad, '') = '' OR (LEN(@JW_RL_NKLoad) = 5 AND @JW_RL_NKLoad = FirstLastConsolTransport.FirstLoad_Port) OR (LEN(@JW_RL_NKLoad) = 2 AND @JW_RL_NKLoad = LEFT(FirstLastConsolTransport.FirstLoad_Port, 2)) OR (LEN(@JW_RL_NKLoad) = 4 AND LegLoadZone.LocoCode is NOT NULL) ) AND ( ISNULL(@OnlyFirstLeg, '') = '' OR (FirstLastConsolTransport.FirstLoad_TD >= @JW_FromDT AND FirstLastConsolTransport.FirstLoad_TD < @JW_ToDT) OR ( (@JW_FromDT = '2079-06-06') AND (FirstLastConsolTransport.FirstLoad_TD = '' OR FirstLastConsolTransport.FirstLoad_TD IS NULL) ) ) AND ( ISNULL(@OnlyLastLeg, '') = '' OR ISNULL(@JW_RL_NKDischarge, '') = '' OR (LEN(@JW_RL_NKDischarge) = 5 AND @JW_RL_NKDischarge = FirstLastConsolTransport.LastDischarge_Port) OR (LEN(@JW_RL_NKDischarge) = 2 AND @JW_RL_NKDischarge = LEFT(FirstLastConsolTransport.LastDischarge_Port, 2)) OR (LEN(@JW_RL_NKDischarge) = 4 AND LegDischargeZone.LocoCode is NOT NULL) ) AND ( ISNULL(@OnlyLastLeg, '') = '' OR (FirstLastConsolTransport.LastDischarge_TA >= @JW_FromAT AND FirstLastConsolTransport.LastDischarge_TA < @JW_ToAT) OR ( (@JW_FromAT = '2079-06-06') AND (FirstLastConsolTransport.LastDischarge_TA = '' OR FirstLastConsolTransport.LastDischarge_TA IS NULL) ) ) AND ( ISNULL(@JK_RL_NKLoad, '') = '' OR (LEN(@JK_RL_NKLoad) = 5 AND @JK_RL_NKLoad = JobConsol.JK_RL_NKLoadPort) OR (LEN(@JK_RL_NKLoad) = 2 AND @JK_RL_NKLoad = LEFT(JobConsol.JK_RL_NKLoadPort, 2)) OR (LEN(@JK_RL_NKLoad) = 4 AND ConsolLoadZone.LocoCode is NOT NULL) ) AND ( ISNULL(@JK_RL_NKDischarge, '') = '' OR (LEN(@JK_RL_NKDischarge) = 5 AND @JK_RL_NKDischarge = JobConsol.JK_RL_NKDischargePort) OR (LEN(@JK_RL_NKDischarge) = 2 AND @JK_RL_NKDischarge = LEFT(JobConsol.JK_RL_NKDischargePort, 2)) OR (LEN(@JK_RL_NKDischarge) = 4 AND ConsolDischargeZone.LocoCode is NOT NULL) ) AND ( ISNULL(@Gateway, '') = '' OR (@Gateway = 'ALL') OR (@Gateway = 'GTW' AND (JK_SendingForwarderHandlingType '' OR JK_ReceivingForwarderHandlingType '')) OR (@Gateway = 'NGW' AND (JK_SendingForwarderHandlingType = '' AND JK_ReceivingForwarderHandlingType = '')) OR (@Gateway = 'GTS' AND JK_SendingForwarderHandlingType '') OR (@Gateway = 'GTR' AND JK_ReceivingForwarderHandlingType '') OR (@Gateway = 'GTB' AND (JK_SendingForwarderHandlingType '' AND JK_ReceivingForwarderHandlingType '')) ) ```

multiple case statements result in rows being duplicated

Related Questions

Same Tag Questions

Popular Questions

0 Answers