Avoid too many case statements in SQL?

Question

I need to drive 13 or 14 Datamatrix barcodes based on apptypecode and state. I am able to do this but I have too many case statements.

What I'm trying to accomplish is when the application pages = 13 then I get 13 DataMatrix populated and when application pages = 14 then I get 14 DataMatrix populated as below:- After page nine Datamatrix MS3000000002421831E9 I start using letters A = 10, B = 11, C = 12 D= 13 AND E = 14 So the Barcode looks like this:

My question: Is their another way to accomplish this without writing all these cases statements?

Here is my code:

SELECT     C.CustomerID
      ,AT.ServiceTypeID
      ,[loadfilename]
      ,f.loadrowstatus
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EI') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EI') ELSE
      CASE WHEN F.primaryState IN('ND','AK') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D1')
      ELSE '' end END END AS BarCodeID
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E2') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E2') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D2')
      ELSE '' end END END AS DataMatrixCode2
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E3') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E3') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D3')
      ELSE '' end END END AS DataMatrixCode3
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E4') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E4') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D4')
      ELSE '' end END END AS DataMatrixCode4
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E5') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E5') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D5')
      ELSE '' end END END AS DataMatrixCode5
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E6') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E6') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D6')
      ELSE '' end END END AS DataMatrixCode6
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E7') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E7') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D7')
      ELSE '' end END END AS DataMatrixCode7
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E8') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E8') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D8')
      ELSE '' end END END AS DataMatrixCode8
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'E9') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'E9') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'D9')
      ELSE '' end END END AS DataMatrixCode9
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EA') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EA') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DA')
      ELSE '' end END END AS DataMatrixCode10
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EB') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EB') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DB')
      ELSE '' end END END AS DataMatrixCode11
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EC') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EC') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DC')
      ELSE '' end END END AS DataMatrixCode12
     ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'ED') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'ED') ELSE
      CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DD')
      ELSE '' end END END AS DataMatrixCode13
      ,CASE WHEN F.primaryState IN ('AL', 'AK','AR','ID','KY','LA','MI','MS','ND','SD','WA','WV') AND F.AppTypeCode IN (300,301,302,303,320,321,350)
      Then STUFF([BarCodeID],19,20,'EE') ELSE 
      CASE WHEN F.primaryState IN('OR') AND f.AppTypeCode IN (310,311,312,313,330,331) Then STUFF([BarCodeID],19,20,'EE') 
      --CASE WHEN F.primaryState IN('ND') AND f.AppTypeCode IN (310,311,312,313,330,331) THEN STUFF([BarCodeID],19,20,'DD')
      ELSE '' end END AS DataMatrixCode14
     ,ISNULL(f.[AppTypeCode],'') AS [AppTypeCode]
      ,ISNULL([LanguageCode],'') AS [LanguageCode]
      ,ISNULL(f.[FirstName],'') AS [FirstName]
      ,ISNULL(f.[LastName],'') AS [LastName]
      ,ISNULL([primaryAddress1],'') AS [ServiceAddress1]
      ,ISNULL([primaryAddress2],'') AS [ServiceAddress2]
      ,ISNULL([primaryApartmentNumber],'') AS [ServiceApartmentNumber]
      ,ISNULL([primaryCity],'') AS [ServiceCity]
      ,ISNULL([primaryState],'') AS [ServiceState]
      ,ISNULL([primaryZipCode5],'') AS [ServiceZipCode5]
      ,ISNULL([primaryZipCode4],'') AS [ServiceZipCode4]
      ,ISNULL(f.[mailingAddress1],'') AS [BillingAddress1]
      ,ISNULL(f.[mailingAddress2],'') AS [BillingAddress2]
      ,ISNULL([mailingApartmentNumber],'') AS [BillingApartmentNumber]
      ,ISNULL([mailingCity],'') AS [BillingCity]
      ,ISNULL([mailingState],'') AS [BillingState]
      ,ISNULL([mailingZipCode5],'') AS [BillingZip5]
      ,ISNULL([mailingZipCode4],'') AS [BillingZip4]
      ,ISNULL([ReasonCodes],'') AS [ReasonCodes]
      ,ISNULL(f.[ContactPhoneNumber],'') AS [PhoneNumber]
      ,ISNULL([ContactEmailAddress],'') AS [EmailAddress]
      ,CASE WHEN at.servicetypeid='M' THEN ISNULL([CustomerAccountNumber],'') ELSE
      ISNULL(InternetAcctNum,'') END AS [CustomerAccountNumber]
     ,ISNULL([CustomerServiceNumber],'') AS [CustomerServiceNumber]
  FROM [outboundprocess].[DailyFulfillmentFile] F
  JOIN dbo.AppTypes AT ON AT.AppTypeID=f.AppTypeCode
  JOIN dbo.Customers c ON c.CustomerID=f.CustomerID
WHERE f.loadrowstatus = 'Loaded' AND AT.servicetypeid='M' AND c.ServiceAddressState<>'NV';

Answer 1

You should create a mapping table that includes the PrimaryState and AppType combinations and the BarcodeID values that they map to. Then you can JOIN your tables through the mapping table.

Doing so should also improve your query performance since SQL Server can perform set operations instead of loops that are required by CASE statements.

CREATE TABLE map
(
    PrimaryState VARCHAR(255),
    AppTypeCode INT,
    BarCodeID VARCHAR(255)
);

(adjust the datatypes as appropriate)