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:
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:
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.
@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?