2

Sorry for the Title but I haven't found a better explanation.

I need to generate a TrackingID number for shipments using data from 3 different tables:

  • Sales
  • ShippingAddress
  • Carriers

I built a query that increments the appropriate trackingNumber and returns it together with some extra columns. (In the real query data are stored in a temp table and other queries follow, but now the issue is here.)

Here it is

DECLARE @OrderID NVARCHAR(10) = 'B3'

UPDATE cc  
SET NextSeed= IIF(nextSeed IS NULL, NewStartSeed + 1,
                  IIF(nextSeed = LastSeed, NewStartSeed, NextSeed + 1)),
    LastSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NewStopSeed, LastSeed),
    NewStartSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NULL, NewStartSeed),
    NewStopSeed = IIF(NextSeed=LastSeed OR NextSeed IS NULL, NULL, NewStopSeed)
OUTPUT
    COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed, 
    inserted.Prefix, inserted.Suffix,
    inserted.ShippingService, inserted.CarrierCode
FROM
    Sales s
JOIN
    ShippingAddress ss ON s.shippingAddressID = ss.ShippingAddressID
JOIN
    Carriers cc ON cc.ShippingService = s.ShippingService 
                AND cc.carriercode = s.carriercode
LEFT JOIN
    SurchargeAreas ssa ON ss.PostalCode = ssa.PostalCode
WHERE
    s.OrderID = @OrderID

Now I have to add an extra table that contains, for the different carriers, the PostalCodes with Surcharge:

SurchargeAreas Table

What I need is:

  • if Customer's PostalCode is in SurchargeArea for the selected Carrier I need to change carrier/shippingService to default Carrier/shippingService to avoid extra shipping costs.

I added to the above query the following extra condition:

and ssa.postalcode is null

That detects if the customer's postalcode is in a surcharge area, but returns zero result, and therefore does not select the default shipping service, does not increment the Seed and does not return a result.

Here is the sqlfiddle with all tables and some sample data to better understand.

In the example, the PostalCode of OrderID "B4" is in a surcharge area, while B1, B2 and B3 are not.

Therefore setting

@OrderID = 'B1' or 'B2' or 'B3' 

returns data, while

@OrderID = 'B4'

does not return anything, while I would expect something like

31001     RS    NL   postnlrm    postnl

since postnlrm has 'default' column set to 1

Can you suggest how to achieve what I'm looking for?

Thanks.

  • You can't return that record when the CarrierCode for that Sales order is associated with dhl and not postnl. Are you asking to (at times) ignore the CarrierCode relationship between the Carriers and Sales tables? – codedawg82 Aug 7 '17 at 11:56
  • Yes, if the postalcode is in SurchargeArea, it has to override the selected carrier/service and use the default one. – Joe Aug 7 '17 at 12:02
  • Are you constrained to one UPDATE? It sounds like you coudld easily do this in two UPDATE statements: one for sales records that are in SurchargeAreas and one for the remainder. You could store your OUTPUT's results by using the frame OUTPUT COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed, inserted.Prefix, inserted.Suffix, inserted.ShippingService, inserted.CarrierCode INTO @Table... – codedawg82 Aug 7 '17 at 12:16
  • Sorry, not well understood your question: That above is only part of the query and actually the output goes to a temporary table and then other queries follows. You mean to execute this query and if result is null to make another query with default parameters? I already thought about this, and once understood how to check if result is null, probably will work, but was looking for a better solution, if exists (but always exists!) – Joe Aug 7 '17 at 12:29
2

Because you're essentially asking for the condition of joins to be changed through a lookup ("if my postalcode is in a surcharge area, change my carrier code joins to this, otherwise this"), I would recommend that you split this update into two sets. Here's some pseudocode to help get you started:

DECLARE @OrderID nvarchar(10)='B4'
DECLARE @mytable TABLE (CurrentSeed, Prefix, Suffix, ShippingService, CarrierCode)

-- Update Surchargeable Areas with Defaults; this assumes only ONE default for a postal code
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN SurchargeAreas     ssa on  ss.PostalCode=ssa.PostalCode
JOIN dbo.Carriers           cc  on cc.carriercode=ssa.carriercode
      and cc.[Default] = 1  -- terrible practice using a SQL keyword as a column name, by the way!!
WHERE s.OrderID=@OrderID;

-- Update non-Surchargeable Areas
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN Carriers cc ON cc.ShippingService = s.ShippingService 
       AND cc.carriercode = s.carriercode
LEFT JOIN SurchargeAreas        ssa on  ss.PostalCode=ssa.PostalCode
AND cc.[Default] = 0
WHERE s.OrderID=@OrderID
          AND ssa.ID IS NULL;
|improve this answer|||||
  • This procedure is for each single shipment: I think is better to first check if the given postal code is in surcharge area and then according to the result use the 'standard' parameters or override the shipping service: something like if not exists (select 1 from Sales s JOIN ShippingAddress ss ON s.shippingAddressID = ss.ShippingAddressID JOIN Carriers cc ON cc.ShippingService = s.ShippingService AND cc.carriercode = s.carriercode LEFT JOIN SurchargeAreas ssa ON ss.PostalCode = ssa.PostalCode WHERE s.OrderID = @OrderID and ssa.postalcode is null) THEN BEGIN....END ELSE BEGIN... END – Joe Aug 8 '17 at 6:39
  • but I was looking for a more elegant solution – Joe Aug 8 '17 at 6:40
  • this is my 'unelegant' solution: sqlfiddle – Joe Aug 8 '17 at 7:14
  • I've seen more inelegant solutions :) You're certainly welcome to do the check first and and apply control logic to decide which UPDATE you want to pursue. I tend to try to use relational sets as best I can instead of specifying control logic, but there could be many factors in one's decision. – codedawg82 Aug 8 '17 at 15:49
  • Yes, that's what I was looking for. Mine is procedural logic, that I do not like when working with SQL, but haven't find a better solution, and I've not understood how to apply your. Anyway, thanks for your time! – Joe Aug 8 '17 at 16:25
-3

Cant you COALESCE and return default courier for the area, if courier is NULL or if a surcharge area from the surchargarea table before u join it?

I.e Left join (select top 1 coaleacse blah from surchargeare) x On x.postalcode=ss.postalcode.

Sorry if it is not clear, i am not at a desk

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.