Get unique value from lines else take default value in SQL-CodePudding

I have a list of sales lines each having a currency field. A sale line is represented by the table SALE_LINE which has a field, Currency, to store the currency of the sales line.

I want to do an SQL request which will take a list of sales line and if the currency of these sales lines is unique to them all, then take that currency or else set the value to a default one like the dollar.

As in if I have 3 lines with currencies: EUR, GBP, ZAR, the request would return me the DOL. if I have 3 lines with currencies: EUR, EUR, EUR, the request would return me the EUR.

My SQL request is as below:

DECLARE
    @salesLineIds VARCHAR(MAX),
    @defaultCurrency VARCHAR(10),
    @uniqueCurrency VARCHAR(10)

SET @salesLineIds = '1,2,3';
SET defaultCurrency = 'DOL';

DECLARE
    @ID_LIST table (Id BIGINT)
    
INSERT INTO @ID_LIST SELECT TRY_CAST(value AS BIGINT)
FROM STRING_SPLIT(@salesLineIds, ',')

SELECT @uniqueCurrency =(CASE WHEN 1 = (SELECT COUNT(DISTINCT SL.Currency))
THEN 
    (SELECT DISTINCT SL_CUR.Currency
    from SALE_LINE SL_CUR
    where SL_CUR.Id in (SELECT * FROM @ID_LIST)
    )
ELSE 
    @defaultCurrency
END)
FROM SALE_LINE SL
WHERE SL.Id IN (SELECT * FROM @ID_LIST)

The above works but it might not be good performance. Is there any way where I can improve the performance of the above query?

The below query does not give the good currency:

SELECT @uniqueCurrency =(CASE WHEN 1 = (SELECT COUNT(DISTINCT SL.Currency))
THEN 
    (SELECT DISTINCT SL.Currency
    )
ELSE 
    @defaultCurrency
END)
FROM SALE_LINE SL
WHERE SL.Id IN (SELECT * FROM @ID_LIST)
group by SL.Currency

Any help of how I can do better in the working SQL will be appreciated. Thanks

CodePudding user response：

You need to group over the whole set, then simply check for distinct Currency values. If there is only one then you can just use any aggregation, such as MIN, to get it.

DECLARE
    @salesLineIds VARCHAR(MAX),
    @defaultCurrency VARCHAR(10),
    @uniqueCurrency VARCHAR(10);

SET @salesLineIds = '1,2,3';
SET defaultCurrency = 'DOL';

DECLARE
    @ID_LIST table (Id BIGINT);
    
INSERT INTO @ID_LIST SELECT TRY_CAST(value AS BIGINT)
FROM STRING_SPLIT(@salesLineIds, ',');

SELECT @uniqueCurrency =
  CASE WHEN COUNT(DISTINCT SL.Currency) = 1
    THEN MIN(SL.Currency)
    ELSE @defaultCurrency
  END
FROM SALE_LINE SL
WHERE SL.Id IN (SELECT * FROM @ID_LIST);

I suggest CHAR(3) as a suitable data type for currency

CodePudding user response：

Assuming no nulls.

SELECT
    CASE min(SL.Currency) = max(SL.Currency)
        THEN min(SL.Currency)
        ELSE @defaultCurrency
    END
FROM SALE_LINE SL
WHERE SL.Id IN (SELECT * FROM @ID_LIST);