How to re-use T-SQL sub-queries with CROSS APPLY within a CASE statement avoiding repetition

Question

I have a SELECT query with different sub-queries inside a CASE statement. I need to re-use them to get other columns inside same general SELECT.

Two main tables are:

Event
+------------+----------------------------+-------------+-----------------------------+
| Id         | EventTypeId  | PersonId    |  Name       |DateEvent         
+------------+----------------------------+-------------+-----------------------------+
| 2307       |      4       |      2189   |  Migrated   | 1900-01-01 00:00:00.6780000 |
| 2308       |      15      |      2189   |  Birthday   | 2020-09-18 16:48:32.6870000 |
| 2309       |      15      |      2190   |  Birthday   | 2012-01-01 00:00:00.0000000 |
| 3401       |      6       |      2190   |  Moved      | 2013-03-12 00:00:00.0000000 |
| 3402       |      15      |      3001   |  Birthday   | 2020-08-21 16:48:32.6870000 |
| 3410       |      6       |      3001   |  Moved      | 1900-01-01 00:00:00.0000000 |
| 3440       |      6       |      2190   |  Moved      | 2016-03-12 00:00:00.0000000 |
| 4000       |      3       |      3001   |  Transfer   | 2021-01-10 00:00:00.0000000 |
| 4020       |      3       |      4020   |  Transfer   | 2016-03-12 00:00:00.0000000 |



Person 
+------------+----------------+-------------------+-----------+-------------------------------+
| Id         | UCNumber       |      Name         |LastName   |  Birth                        | 
+------------+----------------+-------------------+-----------+-------------------------------+
| 2189       |      004947    |        John       |   Smith   |   1900-01-01 00:00:00.0000000 |
| 2190       |      006857    |        Alice      |   Timo    |   1982-02-20 00:00:00.0000000 |
| 3001       |      006594    |        Tom        |   Zigo    |   1981-03-21 00:00:00.0000000 |
| 4020       |      007263    |        Alice      |   Bilani  |   1973-04-03 00:00:00.0000000 |

Multiple events can be associated to a Person.

The query is:

Select
[UC_Number] = A.UCNumber,
[Name] = ltrim(rtrim(A.FirstName + ' ' + A.LastName)),
[Stat_Code] = case 
when A.Id in (select P.Id from db.dbo.Person as P 
              cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17)  
                  order by DateEvent desc) le
              where le.EventTypeId  = 5) then 'AR'   
when A.Id in (select P.Id from db.dbo.Person as P 
              cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17) 
                  order by DateEvent desc) le
              where le.EventTypeId  = 4) then 'AW'  
when A.DateB is not null then 'ABC' 
when A.Id in (select P.Id from db.dbo.Person as P 
              cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17)  
                  order by DateEvent desc) as le
              where le.EventTypeId  = 15) then 'AT'    
when A.Id in (select P.Id from db.dbo.Person as P 
               cross apply 
                    (select top 1 id, Name, DateEvent, EventTypeId 
                      from db.dbo.Event
                      where PersonId = P.Id and EventTypeId  not in (8, 14, 17) 
                      order by DateEvent desc) le
               where le.EventTypeId  = 6) then 'ASPEC'               
when A.Id in (select P.Id from db.dbo.Person as P 
             cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17)  
                  order by DateEvent desc) le
             left join db.dbo.Address B on P.AddressResidenceId = B.Id
             where le.EventTypeId  = 3 and B.PostCode is null) then 'AK'   
else 'OK' 
end,
[Status] = case 
when A.Id in (select P.Id from db.dbo.Person as P 
              cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17) 
                  order by DateEvent desc) le
              where le.EventTypeId  = 4) then 'OtherD'
when A.Id in (select P.Id from db.dbo.Person as P 
              cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17)  
                  order by DateEvent desc) as le
              where le.EventTypeId  = 6) then 'OtherE'
when A.Id in (select P.Id from db.dbo.Person as P 
             cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17)  
                  order by DateEvent desc) le
             left join db.dbo.Address B on P.AddressResidenceId = B.Id
             where le.EventTypeId  = 3 and B.PostCode is null) then 'OtherW' 
when A.Id in (select P.Id from db.dbo.Person as P 
              cross apply 
                 (select top 1 id, Name, DateEvent, EventTypeId 
                  from db.dbo.Event
                  where PersonId = P.Id and EventTypeId  not in (8, 14, 17)  
                  order by DateEvent desc) as le
              where le.EventTypeId  = 15) then 'OtherB'
else 'NO'
End
From db.dbo.Person A ...
left join Address B on A.AddressResidenceId = B.Id
...

Expected result:

+-----------+--------------+-----------+--------+
| UC_Number | Name         | Stat_Code | Status |
+-----------+--------------+-----------+--------+
| 004947    | John Smith   | AT        | OtherB |
+-----------+--------------+-----------+--------+
| 006857    | Alice Timo   | ASPEC     | OtherE |
+-----------+--------------+-----------+--------+
| 006594    | Tom Zigo     | AK        | OtherW |
+-----------+--------------+-----------+--------+
| 007263    | Alice Bilani | AK        | OtherW |
+-----------+--------------+-----------+--------+

If you check the Status column I re-used same sub-queries. Is there another way to make the script parts not to be repeated ?

`CASE` **Expression**, SQL Server does not support `Case` (`Switch`) statements. And you can't reference a subquery in the `SELECT` else where in the `SELECT`, you would have to repeat it. In truth, however, it seems like you should be using a `JOIN` or `APPLY` here in your `FROM` here. Possibly you should be looking at conditional aggregation. Without sample data and expected results it's difficult to kmow. — Thom A, Sep 22 '20 at 17:28
I added the `FROM` part. Did not post the other `WHEN` statements which are the repetition of the above sub-queries. — , Sep 22 '20 at 17:39
One place you could improve performance is switching your `IN`s for `EXISTS` so SQL Server doesn't pull back the entire subquery every time. — critical_error, Sep 22 '20 at 18:06
I change the query slightly so the expected result makes sense. — , Sep 22 '20 at 18:39

score 0 · Answer 1 · answered Sep 22 '20 at 18:15

This is an expansion to my comment. You can improve performance by switching out your IN to EXISTS. Using IN returns the entire subquery--every time--whereas EXISTS check for, well, an existing row.

I don't understand your data fully, so you'll want to double-check my logic, however here is an example of how you could accomplish this change:

SELECT 
    [Stat_Code] = CASE 
        WHEN A.Birth IS NOT NULL THEN 'BD'
        WHEN EXISTS (
            
            SELECT * FROM db.dbo.Person AS P 
            CROSS APPLY (
                SELECT TOP 1 id, Name, DateEvent, EventTypeId FROM db.dbo.Event
                WHERE 
                    PersonId = P.Id 
                    AND EventTypeId NOT IN ( 8, 14, 17 )  
                ORDER BY DateEvent DESC
            ) AS le
            WHERE
                P.Id = A.ID -- A.ID comparison happens here instead of looking for IN.
                AND le.EventTypeId  = 5
            
        ) THEN 'AR'
        ...

https://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance#comment1994630_2065403. — GSerg, Sep 22 '20 at 18:17
Wow, thanks for sharing @GSerg. I stand corrected--though the version of SQL Server isn't mentioned here... — critical_error, Sep 22 '20 at 18:24

score 0 · Answer 2 · answered Sep 22 '20 at 18:31

Assuming you are asking about avoiding code duplication rather than improving performance.

SQL Server is very good at unwinding inline table-valued functions and putting their bodies inside the calling query.

It's likely you will be able to do:

create function dbo.HelpfulNameHere(@EventTypeId int)
returns table
as
return (
  select
    P.Id, P.AddressResidenceId, le.EventId, le.EventName, le.DateEvent
  from
    db.dbo.Person as P 
    cross apply (select top (1) id as EventId, Name as EventName, DateEvent, EventTypeId
                 from db.dbo.Event
                 where PersonId = P.Id and EventTypeId not in (8, 14, 17)  
                 order by DateEvent desc) le
  where
    le.EventTypeId = @EventTypeId
);

Select 

[Stat_Code] = case
when A.Id in (select P.Id from dbo.HelpfulNameHere(5) P) then 'AR'   
when A.Id in (select P.Id from dbo.HelpfulNameHere(4) P) then 'AW'  
when A.DateB is not null then 'ABC' 
when A.Id in (select P.Id from dbo.HelpfulNameHere(15) P) then 'AT'    
when A.Id in (select P.Id from dbo.HelpfulNameHere(6) P) then 'ASPEC'               
when A.Id in (select P.Id
              from dbo.HelpfulNameHere(3) P
                   left join db.dbo.Address B on P.AddressResidenceId = B.Id
              where B.PostCode is null) then 'AK'   
else 'OK' 
end,

[Status] = case 
when A.Id in (select P.Id from dbo.HelpfulNameHere(4) P) then 'OtherD'
when A.Id in (select P.Id from dbo.HelpfulNameHere(15) P) then 'OtherB'
...

The performance should be the same.

Thanks. Very useful. But we tend not to use functions and SPs for maintenance purposes. — , Sep 22 '20 at 18:37

How to re-use T-SQL sub-queries with CROSS APPLY within a CASE statement avoiding repetition

2 Answers2