Question on last valid Date in the system

Question

Post reply

Question on last valid Date in the system

protocoder

Old Hand

Points: 374
More actions
April 4, 2022 at 11:55 am
Go to Answer
#4010459

Hi Experts,
I have several more than 60+ Dates and I need to know which is last valid date for a particular record ?
All the timers are extracted in a row for a particular record.

DesNorton

SSC-Insane

Points: 24279

April 4, 2022 at 12:31 pm

Answer

#4010466

Can you please show us a partial query so that we can see what you mean. No need to add all 60 date fields.

If you have something like this

SELECT ID, t1.date1, t1.date2, t2.date3, t2.date4 ...
FROM table1 AS t1
JOIN table2 AS t2 ON t1.ID=t2.ID

Then something like this should do the trick

SELECT ID, calc.MaxDate, t1.date1, t1.date2, t2.date3, t2.date4 ...
FROM table1 AS t1
JOIN table2 AS t2 ON t1.ID=t2.ID
OUTER APPLY (SELECT MaxDate = MAX(src.DateField)
             FROM (VALUES ( t1.date1 )
                        , ( t1.date2 ) /* Be sure to list each date field like this */
                        , ( t2.date3 )
                        , ( t2.date4 )
                   ) AS src(DateField)
            ) AS calc(MaxDate)

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

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

protocoder wrote:

Hi Experts,
I have several more than 60+ Dates and I need to know which is last valid date for a particular record ?
All the timers are extracted in a row for a particular record.

To DesNorton's point, please see the article at the first link in my signature line below to help us give you the best answer possible as quickly as possible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

protocoder Old Hand Points: 374 More actions · Answer 2

Hi @desnorton,

That is very smart stuff.

Thank you very much, I will try this.

I was using datediff (day,date1, date2) for each consecutive dates and prior to it, I was making them NULL if they are 1900 and feel dumb, which I am 🙂

This is so so good thank you very much. Later in the day, I will try this later this day.

Regards