Get Earliest Date unless it is 01/01/1900 then get 2nd earliest

Transact-SQL
1

I have data like the following (sorry not sure how to insert a table)

UserID Date
1 01/01/1900
1 15/07/2013
2 01/01/1900
2 06/11/2014
3 05/04/2014
4 22/08/2015
5 17/03/2013
5 13/02/2013

I need to find the lowest date for each UserID but if it is 01/01/1900 then i need the next lowest date

So based on the above i would end up with...

UserID Date
1 15/07/2013
2 06/11/2014
3 05/04/2014
4 22/08/2015
5 13/02/2013

Any ideas how best to achieve this greatly appreciated

2

SELECT UserID, MIN(Date) FROM yourtable WHERE Date <> '19000101' GROUP BY UserID;

3

Perfect - Thank you

4

Glad I could help.

5

For efficiency, this will be better if an index can be used:

WHERE Date > '19000101'

6

Good to know - thank you

7

... assuming no earlier dates in the table :slightly_smiling:

Would be better to avoid having a bogus date of 01-01-1900 (I assume that is what it is?) - my preference would be to set the value to NULL instead - i.e. if the 01-01-1900 means "unknown"

8

Thanks for reply - yes no earlier dates

I have no control over the DB to change anything - i think the dates got there from what i call an IT 'bodge' update

9

Often the way, sadly. Inevitably leads to further junk-piled-on-junk downstream though.

In your code MIN(Date) would have ignored any value which was NULL, so it would have just worked as-intended. As it stands right now every single place in the APP / all reports / etc. where that date is checked / used there has to be a test for 01-01-1900 :frowning:

Also, without a bogus 01-01-1900 date the table could have a check constraint that ensured that values in [Date] was within a "reasonable" range ... whereas, right now, you might have some junk in there (there might be some other safety-check, e.g. in the APP, but that wouldn't stop someone doing a direct SQL update and accidentally updating to a value that was "unreasonable", or a new APP / software change not making a suitable check on the value.

Probably (as in "hopefully"!!!) nothing to find but might be worth doign:

SELECT MIN(Date), MAX(Date)
FROM yourtable 
WHERE Date <> '19000101'

just to check there is nothing goofy. If there IS then that might cause The Powers That Be to think it is worth tightening it up :slightly_smiling:

10

Thanks Kristen - sounds useful and certainly good to know - i'll run a quick check