-1

I'm currently struggling with a seemingly simple task.

The goal is to get the corresponding prices according to the customer number - OR - the customer price group if the customer number does not exist in the table. I think my problem here is that those values are saved in the same table. The table looks like this (shortened):

Sales Code  | Item No | Unit price
----------------------------------
SC1         | 711111  | 345.12
SC2         | 711111  | 501.24
SC3         | 711111  | 242.70
CustomerNo1 | 711111  | 242.70
CustomerNo2 | 711111  | 250.00
SC1         | 711112  | 345.12
SC2         | 711112  | 501.24
SC3         | 711112  | 242.70
CustomerNo2 | 711112  | 250.00

I am using a parameter for the customer number and I already got the customer price group of the customer, for the case if the customer number is not available in the table for the sales prices.

A short snipped of my code so far:

-- Get the customer price group
SET @query = 
    'SELECT @CustomerPriceGroup = (CASE [Customer Price Group] WHEN '''' THEN ''SC1'' ELSE [Customer Price Group] END)
    FROM [customers] 
    WHERE [No] = ''' + @CustomerNo + ''''
EXEC sp_executesql @query, N'@CustomerPriceGroup NVARCHAR(3) OUTPUT', @CustomerPriceGroup = @CustomerPriceGroup OUTPUT

-- Get the item data
    SET @query = 'SELECT sp.[Sales Code] AS [SalesCode],
            i.[No] AS [ItemNo],
            i.[Name] AS [ItemName],
            sp.[Unit Price] AS [UnitPrice]
    FROM [items] i
    INNER JOIN [salesprices] sp ON i.[No] = sp.[ItemNo]
    WHERE (sp.[Sales Code] = ''' + @CustomerNo + ''' OR sp.[Sales Code] = ''' + @CustomerPriceGroup + ''')'

    EXEC (@query)

I already tried to use EXIST or a window function (example from here https://stackoverflow.com/questions/15723326/if-exists-in-where-clause), but I was not successful. I got the prices for the customer itself without the customer price group, but not the customer price group if no customer number is available (always nothing, both or only the customer price).

I need to get the following fields:

  • Sales Code
  • Item number
  • Item name
  • Unit price (according to the sales code, either CustomerNo or sales code)
  • Grouped by the item number

I am using Microsoft SQL Server 2016.

Any idea how I can solve that?

BR Raphi

0

I'm glad that you found your own answer, but may I suggest this as a slightly cleaner alternative? As a bonus, it should perform faster as well. Assuming that there is a unique constraint on SalesCode and ItemNo, this should work just fine by joining the table twice and using coalesce.

DECLARE @SalesPrice TABLE
    (
    SalesCode NVARCHAR(20) NOT NULL 
    , ItemNo NVARCHAR(10) NOT NULL
    , UnitPrice DECIMAL(10,2) NOT NULL
    )

INSERT INTO @SalesPrice
(SalesCode, ItemNo, UnitPrice)
VALUES ('SC1','711111',345.12)
    , ('SC2','711111',501.24)
    , ('SC3','711111',242.70)
    , ('CustomerNo1','711111',242.70)
    , ('CustomerNo2','711111',250.00)
    , ('SC1','711112',345.12)
    , ('SC2','711112',501.24)
    , ('SC3','711112',242.70)
    , ('CustomerNo2','711112',250.00)

DECLARE @Items TABLE
    (
    ItemNo NVARCHAR(10) NOT NULL
    , ItemName NVARCHAR(50) NOT NULL
    )

INSERT INTO @Items 
    (ItemNo, ItemName)
VALUES ('711111', 'Widget 1')
    , ('711112', 'Reverse Googah')

DECLARE @CustomerNo NVARCHAR(20) = 'CustomerNo1'
DECLARE @CustomerPriceGroup NVARCHAR(10) = 'SC1'

SELECT COALESCE(SP.SalesCode, SP2.SalesCode) AS SalesCode
    , SP.SalesCode 
    , SP2.SalesCode
    , I.ItemNo
    , I.ItemName 
    , COALESCE(SP.UnitPrice, SP2.UnitPrice) AS UnitPrice
FROM @Items AS I
    LEFT OUTER JOIN @SalesPrice AS SP ON SP.ItemNo = I.ItemNo AND (SP.SalesCode = @CustomerNo)
    LEFT OUTER JOIN @SalesPrice AS SP2 ON SP2.ItemNo = I.ItemNo AND (SP2.SalesCode = @CustomerPriceGroup)
| improve this answer | |
  • Thank you for your answer! It seems to work too, but I just measured the code and it seems that my approach is two times faster (once for 3 rows, once for 639 rows), so I think I will stick to my own solution. Do you have an explanation why do you think your solution should be faster? – raph Jul 3 at 6:24
  • Alright, unfortunately the requirements changed and so I need to use the starting and ending date of the prices too. It seems that my solution does not work, but yours is working like a charm. I will use yours, thank you very much. – raph Jul 3 at 7:16
  • @raph, probably engine differences? I was using SQL 2019 for my testing, but I also had no indexes on these tables. My thought was that mine avoided an expensive peek into the salesprice table, but does suffer from always joining the table twice. Depending on your actual query, table size, statistics, etc, etc. Glad it works for you. – Jonathan Fite Jul 6 at 1:13
0

I could solve my problem by myself.

I just used CASE WHEN EXISTS in my WHERE expression:

SET @query = 'SELECT sp.[Sales Code] AS [SalesCode],
            i.[No] AS [ItemNo],
            i.[Name] AS [ItemName],
            sp.[Unit Price] AS [UnitPrice]
    FROM [items] i
    INNER JOIN [salesprices] sp ON i.[No] = sp.[ItemNo]
    WHERE (sp.[Sales Code] = (CASE 
                                    WHEN EXISTS (SELECT 1 FROM [salesprices] sp2 WHERE sp2.[ItemNo] = i.[No] AND sp2.[Sales Code] = ''' + @CustomerNo + ''')
                                    THEN ''' + @CustomerNo + '''
                                    ELSE ''' + @CustomerPriceGroup + ''' 
                                   END)'

Works like a charm.

| improve this answer | |
0

The operator you are looking for is XOR. I don't know if it exists in your DBMS, but:

A XOR B  <=> (A OR B) AND NOT (A AND B)

so in your case, that would be:

WHERE (sp.[Sales Code] = @CustomerNo OR sp.[Sales Code] = @CustomerPriceGroup)
  AND NOT (sp.[Sales Code] = @CustomerNo AND sp.[Sales Code] = @CustomerPriceGroup)

You can simplify OR a bit with IN:

WHERE (sp.[Sales Code] IN (@CustomerNo, CustomerPriceGroup))
  AND NOT (sp.[Sales Code] = @CustomerNo AND sp.[Sales Code] = @CustomerPriceGroup)
| improve this answer | |
  • thank you for your answer! I think that could work too, yes. – raph Jul 3 at 6:07

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.