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

Question

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 `

According to: http://stackoverflow.com/questions/2072721/how-can-i-select-multiple-columns-within-a-case-when-on-sql-server, and: http://stackoverflow.com/questions/8358642/case-statement-in-sql-how-to-return-multiple-variables, it looks like you're out of luck — mcalex, Feb 08 '13 at 05:15
Yes, that looks like the same type of thing. I wanted to be sure I am not missing something as I am no expert. Thank you for pointing out this other question. I was afraid of this! Jim :) — James Randall, Feb 08 '13 at 05:22
One thing I could possibly do is add another column to my Category table in a manner that I can query a single field here rather than five. I will consider that if need be. Again thankyou for the other references. — James Randall, Feb 08 '13 at 05:28
Are you able to do a separate update statement? Then you could just set all the fields where the condition exists. — Chris, Feb 08 '13 at 05:28
I do actually have an update statement later on, but I am using these derived fields to sort the results prior to the update. It is looking like what I have is fine at least for now. Thanks — James Randall, Feb 08 '13 at 05:45

Geert Immerzeel · Accepted Answer · 2013-02-08T13:30:44.023

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;

Geert, This is very interesting and looks like what I had been looking for. I had not seen CTE's before. I will work with this and accept this as the solution. Thank you, Jim — James Randall, Feb 08 '13 at 12:40

score 0 · Answer 2 · answered Feb 08 '13 at 05:35

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)

Yes, actually I already have a UNION going on (not shown), so I would actually need a union of four selects in my case. Ouch! Thank you for pointing this out. I am thinking adding a higher level field in my Category table is the way to go for better efficiency here. I guess I am not missing something obvious which was really the question. Thanks — James Randall, Feb 08 '13 at 05:42

score 0 · Answer 3 · answered Feb 08 '13 at 05:45

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.

score 0 · Answer 4 · answered Feb 08 '13 at 12:50

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;

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

4 Answers4