Tag: 2016

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