CODEWITHSUNDEEP
sqlsql-server
LCJ
LCJ

Reputation: 22652

Make multiple SQL into one Query

I have a table in SQL Server 2005 as shown below

DECLARE @Promotions TABLE (PromoCode VARCHAR(10),
                          DeptID VARCHAR(10),
                          VerticalID VARCHAR(10),
                          BuildingID VARCHAR(10)
                          primary key (DeptID, VerticalID, BuildingID)
                        )

INSERT INTO @Promotions VALUES ('P1_20%','101','501','1001')
INSERT INTO @Promotions VALUES ('P2_10%','101','501','All')
INSERT INTO @Promotions VALUES ('P3_5%','101','All','All')
INSERT INTO @Promotions VALUES ('P1_50%','111','606','1002')

We need to find out promotion code value based on the matching values in DeptID, VerticalID and BuildingID columns. If there is not match with these 3 columns, look for a match with the input value for DeptID, VerticalID and a default value ("All") for BuildingID. Still if there is no result found, look for a match by considering input DeptID, default value for VerticalID ("All") and default value for BuildingID ("All").

The following query works fine – however it is using a 3 part approach. Can we achieve this in a single SQL query?

EXISTING CODE

DECLARE @PromoCode VARCHAR(10)

--Part 1
SELECT @PromoCode = PromoCode
FROM @Promotions
WHERE DeptID = @inputDeptID
  AND VerticalID = @inputVerticalID
  AND BuildingID = @inputBuildingID

--Part 2
IF @PromoCode IS NULL 
BEGIN
    SELECT @PromoCode = PromoCode
    FROM @Promotions
    WHERE DeptID = @inputDeptID
      AND VerticalID = @inputVerticalID
      AND BuildingID = 'All'
END

--Part 3
IF @PromoCode IS NULL 
BEGIN
    SELECT @PromoCode = PromoCode
    FROM @Promotions
    WHERE DeptID = @inputDeptID
      AND VerticalID = 'All'
      AND BuildingID = 'All'
END

--Result
SELECT @PromoCode

Testing

DECLARE @inputDeptID VARCHAR(10)
DECLARE @inputVerticalID VARCHAR(10)
DECLARE @inputBuildingID VARCHAR(10)

SET @inputDeptID = '101'
SET @inputVerticalID = '501'
SET @inputBuildingID = '3003'

Expected Result

P2_10%

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You can approach this as a "prioritization". That is, look for all possible matches and then choose the one that is best, using order by and top:

set @PromoCode = (SELECT top 1 PromoCode
                  FROM @Promotions
                  WHERE DeptID = @inputDeptID AND
                        (VerticalID = @inputVerticalID OR VerticalId = 'ALL') AND
                        (BuildingID = @inputBuildingID OR BuildingID = 'ALL')
                  ORDER BY (case when Building = 'ALL' then 1 else 0 end),
                           (case when VerticalId = 'ALL' then 1 else 0 end)
                 );

There are other ways to express this. If performance is an issue and you have indexes on the columns, then a union all approach with order by may work better than ors in the where clause.

Upvotes: 3

Related Questions