CODEWITHSUNDEEP
sqlsql-servert-sql
James Randall
James Randall

Reputation: 41

How to consolidate SQL Server Query logic into a single case?

I have a T-SQL Query running on SQL Server 2005 where I have defined a table of data to be returned to the application.

I am assigning several values using several case statements which each has the identical logic.

I am wondering if there is a way to make this more efficient instead of performing the same logic multiple times? I have abbreviated the logic here, but hopefully there is enough to make the point. Is there a way that I wouldn't have to define three case statements using identical "pt.PTYP_Category IN (90, 92, 94, 96, 98)" logic? 

`INSERT INTO @MemberAccountingDetail
    (
     MACD_DPST_FEES_RowID,
     MACD_HighOrderSort,
     MACD_CategorySortDate, 
     MACD_MemberName
     )  
 SELECT 
   d.DPST_ID As MACD_DPST_FEES_RowID,
   CASE
         WHEN  pt.PTYP_Category IN (90, 92, 94, 96, 98) 
             THEN 0
         ELSE     1
     END As MACD_HighOrderSort,
   CASE
         WHEN  pt.PTYP_Category IN (90, 92, 94, 96, 98) 
             THEN DATEADD(ss, 1, d.DPST_TransactionDate)
         ELSE     @DefaultDate
     END As MACD_CategorySortDate,
   CASE
         WHEN  pt.PTYP_Category IN (90, 92, 94, 96, 98) 
             THEN pt.PTYP_CategoryDesc
         ELSE     SUBSTRING(ISNULL(d.DPST_OriginUserID, ''), 1, 11)
     END As MACD_MemberName
 FROM
     Deposit d
        INNER JOIN PaymentType pt
           ON pt.PTYP_ID = d.DPST_PTYP_ID
 WHERE 
     d.DPST_MEMB_ID = @MEMB_ID;

Thank you, Jim `

Upvotes: 1

Views: 305

Answers (4)

Geert Immerzeel
Geert Immerzeel

Reputation: 564

You can use a common table expression so you only have to write the 'IN STATEMENT' once:

INSERT INTO @MemberAccountingDetail
(
    MACD_DPST_FEES_RowID,
    MACD_HighOrderSort,
    MACD_CategorySortDate, 
    MACD_MemberName
)
WITH CTE (PTYP_ID, IsInCollection)
AS
(
    SELECT pt.PTYP_ID, CASE WHEN  pt.PTYP_Category IN (90, 92, 94, 96, 98) THEN 0 ELSE 1 END AS IsInCollection
    FROM PaymentType pt
)
SELECT 
    d.DPST_ID As MACD_DPST_FEES_RowID,
    CTE.IsInCollection As MACD_HighOrderSort,
    CASE WHEN CTE.IsInCollection = 1 
        THEN DATEADD(ss, 1, d.DPST_TransactionDate)
        ELSE     @DefaultDate
    END As MACD_CategorySortDate,
    CASE WHEN CTE.IsInCollection = 1
        THEN pt.PTYP_CategoryDesc
        ELSE SUBSTRING(ISNULL(d.DPST_OriginUserID, ''), 1, 11)
    END As MACD_MemberName
FROM Deposit d
INNER JOIN CTE 
  ON CTE.PTYP_ID = d.DPST_PTYP_ID
INNER JOIN PaymentType pt
  ON pt.PTYP_ID = d.DPST_PTYP_ID
WHERE d.DPST_MEMB_ID = @MEMB_ID;

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

One more option with CROSS APPLY

 INSERT INTO @MemberAccountingDetail
    (
     MACD_DPST_FEES_RowID,
     MACD_HighOrderSort,
     MACD_CategorySortDate, 
     MACD_MemberName
     )  
 SELECT 
   d.DPST_ID As MACD_DPST_FEES_RowID,
   CASE WHEN o.IsMatch = 1 THEN 0 
        ELSE 1 END As MACD_HighOrderSort,
   CASE WHEN o.IsMatch = 1 THEN DATEADD(ss, 1, d.DPST_TransactionDate) 
        ELSE @DefaultDate END As MACD_CategorySortDate,
   CASE WHEN o.IsMatch = 1 THEN pt.PTYP_CategoryDesc 
        ELSE SUBSTRING(ISNULL(d.DPST_OriginUserID, ''), 1, 11) END As MACD_MemberName
 FROM Deposit d INNER JOIN PaymentType pt ON pt.PTYP_ID = d.DPST_PTYP_ID
                CROSS APPLY (SELECT CASE WHEN pt.PTYP_Category IN (90, 92, 94, 96, 98) THEN 1 END AS IsMatch) o
 WHERE d.DPST_MEMB_ID = @MEMB_ID;

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19184

You could create a calculated column in your table that rolls up those 5 categories into a single code, then use that.

This means you don't have to hard code magic numbers anymore. But you still need four different case statements. This is one of those things where any attempt to be 'smart' about solving it just makes it more complicated.

Upvotes: 0

valex
valex

Reputation: 24134

You can try to use UNION to make two separated selects but I'm not sure it will be faster:

INSERT INTO @MemberAccountingDetail
    (
     MACD_DPST_FEES_RowID,
     MACD_HighOrderSort,
     MACD_CategorySortDate, 
     MACD_MemberName
     )  

 SELECT 
   d.DPST_ID As MACD_DPST_FEES_RowID,
   0 As MACD_HighOrderSort,
   DATEADD(ss, 1, d.DPST_TransactionDate) As MACD_CategorySortDate,
   pt.PTYP_CategoryDesc As MACD_MemberName
 FROM
     Deposit d
        INNER JOIN PaymentType pt
           ON pt.PTYP_ID = d.DPST_PTYP_ID
 WHERE 
     d.DPST_MEMB_ID = @MEMB_ID and pt.PTYP_Category IN (90, 92, 94, 96, 98)
 UNION ALL
 SELECT 
   d.DPST_ID As MACD_DPST_FEES_RowID,
   1 As MACD_HighOrderSort,
   @DefaultDate As MACD_CategorySortDate,
   SUBSTRING(ISNULL(d.DPST_OriginUserID, ''), 1, 11) as MACD_MemberName
 FROM
     Deposit d
        INNER JOIN PaymentType pt
           ON pt.PTYP_ID = d.DPST_PTYP_ID
 WHERE 
     d.DPST_MEMB_ID = @MEMB_ID and pt.PTYP_Category NOT IN (90, 92, 94, 96, 98)

Upvotes: 0

Related Questions