Last value in a row containing..

Question

Post reply

Last value in a row containing..

boehnc

Ten Centuries

Points: 1003
More actions
December 21, 2023 at 7:09 am
Go to Answer
#4350013

I've inherited code in which I need to designate each PATID as either Rel_Exempt or Med_Exempt, depending on whether it is the last column in the row (not counting the column with 'NONE').
Hoping it will turn out as below. Thanks in advance for any help.

create table #values
(
PatID varchar(10)
,Name1 varchar(10)
,Date1 datetime
,Name2 varchar(10)
,Date2 datetime
,Name3 varchar(10)
,Date3 datetime
,Name4 varchar(10)
,Date4 datetime
)
Insert into #values
values
('1','Blue' ,'2021-01-01 07:01:00.000','Green' ,'2021-01-01 19:00:00.000', 'Green', '2021-01-01 23:00:00.000', 'Orange','2021-01-02 05:00:00.000' )
,('2','Green','2021-01-01 19:01:00.000', 'Rel_Exempt' ,'2021-01-02 07:00:00.000', 'NONE', '2023-12-20 00:00:00.000', 'NONE', '2023-12-20 00:00:00.000')
,('3','Blue' ,'2021-01-01 07:01:00.000','Blue' ,'2021-01-11 19:00:00.000', 'Med_Exempt', '2021-01-21 23:00:00.000', 'Green','2021-01-22 05:00:00.000' )
,('4','Green','2021-01-01 07:01:00.000','Green' ,'2021-01-04 19:00:00.000', 'Blue', '2021-06-01 23:00:00.000', 'Rel_Exempt','2021-08-02 05:00:00.000' )
,('5','Med_Exempt' ,'2021-01-01 07:01:00.000','NONE' ,'2023-12-20 00:00:00.000', 'NONE', '2023-12-20 00:00:00.000', 'NONE','2023-12-20 00:00:00.000' )

Select
p.PatID
,p.Name1
,cast(nullif(p.Date1, '') as Date) 'Date1'
,p.Name2
,cast(nullif(p.Date2, '') as Date) 'Date2'
,p.Name3
,cast(nullif(p.Date3, '') as Date) 'Date3'
,p.Name4
,cast(nullif(p.Date4, '') as Date) 'Date4'

from #values p

drew.allen

SSC Guru

Points: 76985

December 21, 2023 at 3:40 pm

Answer

#4350267

Nested CASE expressions are very hard to read, because it quickly becomes difficult to tell exactly where you are in the nesting. Here is an example that uses a single CASE expression.

SELECT v.PatID
     , v.Name1
     , CAST(v.Date1 AS DATE) AS Date1
     , v.Name2
     , CAST(v.Date2 AS DATE) AS Date2
     , v.Name3
     , CAST(v.Date3 AS DATE) AS Date3
     , v.Name4
     , CAST(v.Date4 AS DATE) AS Date4
     , CASE WHEN v.Name4 IN ('Rel_Exempt', 'Med_Exempt') THEN v.Name4
            WHEN v.Name4 <> 'NONE' THEN ''
            WHEN v.Name3 IN ('Rel_Exempt', 'Med_Exempt') THEN v.Name3
            WHEN v.Name3 <> 'NONE' THEN ''
            WHEN v.Name2 IN ('Rel_Exempt', 'Med_Exempt') THEN v.Name2
            WHEN v.Name2 <> 'NONE' THEN ''
            WHEN v.Name1 IN ('Rel_Exempt', 'Med_Exempt') THEN v.Name1
            ELSE ''
        END AS Final_Disp
FROM #values AS v;

I don't have access to SQL 2022 (and GREATEST()), but it performs much better than the CROSS APPLY. Here are the read stats.

/*  CROSS APPLY  */
Table 'Worktable'. Scan count 1, logical reads 39, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#values___0000000004DF'. Scan count 2, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

/* CASE Expression  */
Table '#values___0000000004DF'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

What have you tested ?

This can be an option:

; with cteHighestDate as (
Select PatID
, case Name4 when 'None' then 
case Name3 when 'None' then 
case Name2 when 'None' then
case Name1 when 'None' then NULL 
else Date1 
end
else GREATEST ( Date1, Date2 ) 
end
else GREATEST ( Date1, Date2, Date3 ) 
end
else GREATEST ( Date1, Date2, Date3, Date4 ) 
end GreatestDate
from #values 
)
, cteLastDisp as (
Select V.PatID, Case V.Date1 when HD.GreatestDate then V.Name1
else case V.Date2 when HD.GreatestDate then V.Name2
else case V.Date3 when HD.GreatestDate then V.Name3
else case V.Date4 when HD.GreatestDate then V.Name4 
else '???'
end
end
end
end as Last_Disp
from #values V
inner join cteHighestDate HD
on HD.PatID = V.PatID
)
Select PatID, case when Last_Disp in ( 'Med_Exempt', 'Rel_Exempt' ) then Last_Disp else '' end Final_Disp
from cteLastDisp 
order by PatID;

Test it, Test IT, TEST it, TEST IT ..... on a large set of data !!!!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

DesNorton SSC-Insane Points: 24386 More actions · Answer 2

Another option

WITH cteBase AS (
  SELECT p.*, x.pos, x.Namex
       , rn = ROW_NUMBER()  OVER (PARTITION BY p.PatID ORDER BY x.pos DESC)
  FROM #values AS p
  CROSS APPLY (VALUES  ( 1, p.Name1 )
                     , ( 2, p.Name2 )
                     , ( 3, p.Name3 )
                     , ( 4, p.Name4 )
              ) AS x(pos, Namex)
  WHERE x.[Namex] <> 'NONE'
)
SELECT cte.PatID
     , cte.Name1
     , Date1 = CAST(NULLIF( cte.Date1, '' ) AS date)
     , cte.Name2
     , Date2 = CAST(NULLIF( cte.Date2, '' ) AS date)
     , cte.Name3
     , Date3 = CAST(NULLIF( cte.Date3, '' ) AS date)
     , cte.Name4
     , Date4 = CAST(NULLIF( cte.Date4, '' ) AS date)
     , Final_Disp = MAX(CASE
                      WHEN rn = 1 AND cte.Namex IN ( 'Med_Exempt', 'Rel_Exempt' ) THEN cte.Namex
                      ELSE ''
                    END)
FROM cteBase AS cte
GROUP BY cte.PatID, cte.Name1, cte.Date1, cte.Name2, cte.Date2, cte.Name3, cte.Date3, cte.Name4, cte.Date4

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24386 More actions · Answer 3

For more info on using the CROSS APPLY method, please read this article

https://www.sqlservercentral.com/articles/an-alternative-better-method-to-unpivot-sql-spackle

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

My previous answer does not cater for scenarios where ALL Name values are "NONE".

Updated code - Please test thoroughly.

WITH cteBase AS (
  SELECT p.PatID, x.pos, x.Namex
       , rn = ROW_NUMBER() OVER (PARTITION BY p.PatID ORDER BY x.pos DESC)
  FROM #values AS p
  CROSS APPLY (VALUES ( 1, p.Name1 ), ( 2, p.Name2 ), ( 3, p.Name3 ), ( 4, p.Name4 )
              ) AS x(pos, Namex)
  WHERE x.[Namex] <> 'NONE'
)
SELECT v.PatID
     , v.Name1
     , Date1 = CAST(NULLIF( v.Date1, '' ) AS DATE)
     , v.Name2
     , Date2 = CAST(NULLIF( v.Date2, '' ) AS DATE)
     , v.Name3
     , Date3 = CAST(NULLIF( v.Date3, '' ) AS DATE)
     , v.Name4
     , Date4 = CAST(NULLIF( v.Date4, '' ) AS DATE)
     , Final_Disp = MAX(CASE
                      WHEN cte.rn = 1 AND cte.Namex IN ( 'Med_Exempt', 'Rel_Exempt' ) THEN cte.Namex
                      ELSE ''
                    END)
FROM #values AS v
LEFT JOIN cteBase AS cte ON cte.PatID = v.PatID
GROUP BY v.PatID, v.Name1, v.Date1, v.Name2, v.Date2, v.Name3, v.Date3, v.Name4, v.Date4
ORDER BY v.PatID;

How to post data/code on a forum to get the best help.

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

Nice one Drew. CASE exits the expression once a true condition is found

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

boehnc Ten Centuries Points: 1003 More actions · Answer 6

boehnc

Ten Centuries

Points: 1003

December 22, 2023 at 12:08 am

#4350491

very nice..appreciate all your responses!