Highest date in multiple columns. 1

[ibwebn65]

I've been asked to create a report from an existing database. The user wants to know when was the last time someone worked on a project. Normally, this would not be a problem, because there would be a table for the project and a separate table logging each time someone did work on the project. This database was done differently.

The project table has a date column for each possible project task. So, it is possible for more than one task date column to have a date. Plus, there is no particular order for entering dates in the task date fields. So, it is also possible for any task field to have a date when none of the other task fields have a date.

The project table, called PROJECT, has the following fields:

PROJECT_ID
TASK_1_DATE
TASK_2_DATE
TASK_3_DATE
TASK_4_DATE
TASK_5_DATE

Does anyone have any ideas on how I can write a SELECT statement to look through all the task date fields, and only return the highest or most recent date?

 

[vongrunt]

Until M$ gets to brilliant idea to implement LEAST()/GREATEST(), you can:

- use ugly bunch of CASE/WHEN statements
- denormalize data, on-the-fly with subqueries/UNION/MAX or as a part of database design.
- use temp table

Choose one

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[druer]

Assuming that all of your dates have an actual value in them (NO NULLS which screw it up)
you can use the following type command (ugly bunch of case when statements per vongrunt)

select FieldFromYourTable, MostRecentMod = case when field2 > field3 and field2 > field4 then field2 when field3 > field4 then field3 else field4 end
from YourTable

where field2, field3, field4 etc are your date fields

 

[ibwebn65]

druer,

Your code looks interesting, but, unfortunately empty date fields are NULL.

 

[rac2]

vongrunt-
I will be the very last one to defend M$, but I must quibble with the assertion that LEAST()/GREATEST() are brilliant ideas.

Granted functions like these can be quite useful, but I think they are outside the relational database concept. Because they are applied to attributes of the same entity. Suppose a table described Pastas with attributes length, thickness, twists, and folds. You know what Im talking about here, regular spaghetti, angel hair, penne, farfalle, noodles. What sense does it make to calculate LEAST(length, thickness, twists, folds)? Nonsense.

ibwebn65 has given us an example where GREATEST(TASK_1_DATE,TASK_2_DATE,TASK_3_DATE,TASK_4_DATE,TASK_5_DATE) is a sensible calculation. The attributes are all dates. More than that they are the last date worked on various Tasks. I think these are a repeating group. And I submit that whenever LEAST() and GREATEST() are useful, there will be found a repeating group.

A repeating group of course violates 1stNF.

The advice then should be to normalize the table, not de-normalize it.

But alas, that is often not possible. And we are left with

SELECT project_id, MAX(task_date)
FROM (
       SELECT project_id, task_1_date AS task_date
       FROM Projects
       UNION ALL
       SELECT project_id, task_2_date
       FROM Projects
       UNION ALL
       SELECT project_id, task_3_date
       FROM Projects
       UNION ALL
       SELECT project_id, task_4_date
       FROM Projects
       UNION ALL
       SELECT project_id, task_5_date
       FROM Projects
)
GROUP BY project_id

I think when need UNION ALL in the event that two tasks are worked simultaneously.

 

[druer]

No problem. The NULL's can be handled I just didn't go to that extent, by using the ISNULL function:
'
isnull(field2,'01/01/1900')

The ISNULL will use the value 01/01/1900 if the date field was null, but will use the date value otherwise.

 

[vongrunt]

Granted functions like these can be quite useful, but I think they are outside the relational database concept.

Theoretically - I agree with everything you said. These functions either produce GIGO or indicate denormalized (no typo this time ) table design. Plus... if any of listed value is NULL, GREATEST()/LEAST() should also return NULL - which is not acceptable in this particular case.

Practically - I guess this comes down to personal preferences. Fewer things to abuse vs freedom of choice and blah. . The fact some other (R)DBMS vendors implemented these functions must mean something.

No problem. The NULL's can be handled I just didn't go to that extent, by using the ISNULL function
isnull(field2,'01/01/1900')

Um... wanna do something like that, better do it at the end of calculations. Something like:

SELECT project_id, NULLIF(MAX(ISNULL(task_date, 0)), 0)
FROM (
       SELECT project_id, task_1_date AS task_date FROM Projects UNION ALL
       SELECT project_id, task_2_date FROM Projects UNION ALL
       SELECT project_id, task_3_date FROM Projects UNION ALL
       SELECT project_id, task_4_date FROM Projects UNION ALL
       SELECT project_id, task_5_date FROM Projects
) blah
GROUP BY project_id

This also eliminates ANSI warnings...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ibwebn65]

Thanks for your help!!! The UNION worked perfectly.