identifying max date from multiple column dates in sql

Hello Experts,

I have 7 column of data and all the columns are data.  How can I identify the minimum and max data from the set of dates.  Here is an example.  I need to the min and max date from the set of dates in the column

User  Date1 Date2 Date3 Date2 Date4 Date5 Date6 Date7
1111 3/3/2015 3/2/2015 7/3/2015 7/3/2015 2/7/2015 1/13/2015 8/16/2015

Thanks in advance for your help
LVL 1
fb1990Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
If you're trying to get the min and max for each user:
select user, min(mydate), max(mydate)
from (
select user, date1 as mydate from original_table
union
select user, date2 as mydate from original_table
. . .
union
select user, date7 as mydate from original_table)
group by user
plusone3055Commented:
this should work
SELECT  MAX(date_column) AS max_date
FROM    ( (SELECT   date1 AS date_column
           FROM     yourtable         )
          UNION
          ( SELECT  date2 AS date_column
            FROM    yourtable  
          )
          UNION
          ( SELECT  date3 AS date_column
            FROM    yourtable  
          )
          UNION
          ( SELECT  date4 AS date_columns
            FROM    yourtable  
          )
          UNION
          ( SELECT  date5 AS date_column
            FROM    yourtable  
          )
           UNION
          ( SELECT  date6 AS date_column
            FROM    yourtable  
          )
           UNION
          ( SELECT  date7 AS date_column
            FROM    yourtable  
          )
        ) AS date_query

Open in new window

plusone3055Commented:
sorry forgot the MIN


SELECT  MAX(date_column) AS max_date
              ,MIN date_column) as min_Date
FROM    ( (SELECT   date1 AS date_column
           FROM     yourtable         )
          UNION
          ( SELECT  date2 AS date_column
            FROM    yourtable  
          )
          UNION
          ( SELECT  date3 AS date_column
            FROM    yourtable  
          )
          UNION
          ( SELECT  date4 AS date_columns
            FROM    yourtable  
          )
          UNION
          ( SELECT  date5 AS date_column
            FROM    yourtable  
          )
           UNION
          ( SELECT  date6 AS date_column
            FROM    yourtable  
          )
           UNION
          ( SELECT  date7 AS date_column
            FROM    yourtable  
          )
        ) AS date_query

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
Oh, I see you have chosen BOTH MS SQL Server & Oracle as topics

Which database is this actually for please? (and which version?)

The syntax for such a solution to this is very different in these databases.

In Oracle, use these:

select
     least(Date1, Date2 ,Date3 ,Date4 ,Date5 ,Date6 ,Date7)
  ,  greatest(Date1 ,Date2 ,Date3 ,Date4 ,Date5 ,Date6 ,Date7)
from table1


in SQL Server the simplest approach id to transpose the data using CROSS APPLY, like this:
select
      t.[user]
    , MIN(ca.DateData) as MinDate
    , MAX(ca.DateData) as MaxDate
from table1 t
cross apply (
  values
         (Date1)
       , (Date2)
       , (Date3)
       , (Date4)
       , (Date5)
       , (Date6)
       , (Date7)
  ) as CA (DateData)
group by
      t.[user];

| user |                   MinDate |                  MaxDate |
|------|---------------------------|--------------------------|
| 1111 | January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |

Open in new window

see http://sqlfiddle.com/#!3/e9f37/3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
plusone3055Commented:
darn forgot a Brac

SELECT  MAX(date_column) AS max_date
              ,MIN (date_column) as min_Date
FROM    ( (SELECT   date1 AS date_column
           FROM     yourtable         )
          UNION
          ( SELECT  date2 AS date_column
            FROM    yourtable  
          )
          UNION
          ( SELECT  date3 AS date_column
            FROM    yourtable  
          )
          UNION
          ( SELECT  date4 AS date_columns
            FROM    yourtable  
          )
          UNION
          ( SELECT  date5 AS date_column
            FROM    yourtable  
          )
           UNION
          ( SELECT  date6 AS date_column
            FROM    yourtable  
          )
           UNION
          ( SELECT  date7 AS date_column
            FROM    yourtable  
          )
        ) AS date_query

Open in new window

fb1990Author Commented:
Hello Paul,

I am using toad to query table from an oracle database.  I apply the oracle database solution that was suggested by you.  the code ran with ease, but it does give me the expected result.

here is my code
select user,date1,
date2,
date3,
date4,
date5,
date6,
date7
,
least(date1,
date2,
date3,
date4,
date5,
date6,
date7
) as mindate

,greatest(date1,
date2,
date3,
date4,
date5,
date6,
date7
) as maxdate
from tbldata

/* the result shows null for the least and greatest date
user	date1	date2	date3	date4	date5	date6	date7	MINDATE	MAXDATE
1004	8/3/2013 0:00								
1009	9/27/2013 0:00			12/26/2013 0:00	12/26/2013 0:00				
1011	1/9/2012 0:00								
1019						3/13/2015 0:00			

Open in new window

slightwv (䄆 Netminder) Commented:
LEAST and GREATEST will return a null when any value is null.

You can use NVL to get around this but what value do you want returned if ALL date columns are null?

Here is a quick example that returns  01/01/9999 for the LEAST and 01/01/0001 for the GREATEST if everything is null.

drop table tab1 purge;
create table tab1( date1 date, date2 date, date3 date);
insert into tab1 values(sysdate,sysdate,sysdate);
insert into tab1 values(null,sysdate,null);
insert into tab1 values(null,null,null);
commit;

select least( nvl(date1,to_date('01/01/9999','MM/DD/YYYY')), nvl(date2,to_date('01/01/9999','MM/DD/YYYY')), nvl(date3,to_date('01/01/9999','MM/DD/YYYY')) )
, greatest( nvl(date1,to_date('01/01/0001','MM/DD/YYYY')), nvl(date2,to_date('01/01/0001','MM/DD/YYYY')), nvl(date3,to_date('01/01/0001','MM/DD/YYYY')) )
from tab1;

Open in new window

PortletPaulEE Topic AdvisorCommented:
As slightwv has indicated, you need to deal with NULLs in all the columns, then least() and greatest() will provide non-null results.

&, Please:
In future questions, do not choose database type topics that are not relevant to your question. As you can see several volunteers took time to answer this question, but concentrated on the SQL Server topic which isn't relevant for Oracle.
fb1990Author Commented:
Thanks to everyone that contributed towards solving the problem for me.

 I have values for null values now they dates have a default date of 01/01.1999.  I wonder if there is a way to get null if there are no dates.  I plan to handle in excel for now.

Paul-  Thanks for the feedback.

Much appreciated!
slightwv (䄆 Netminder) Commented:
I see you gave the union query an assist.  I would never use that. First it should be UNION ALL to avoid a sort.  The other reason I wouldn't use it is that it hits the table several times when it isn't necessary.

>> I wonder if there is a way to get null if there are no dates

It's ugly but the way that comes to mind using my example:
select case when least_date=to_date('01/01/9999','MM/DD/YYYY') then null else least_date end,
case when greatest_date=to_date('01/01/0001','MM/DD/YYYY') then null else greatest_date end
from
(
select least( nvl(date1,to_date('01/01/9999','MM/DD/YYYY')), nvl(date2,to_date('01/01/9999','MM/DD/YYYY')), nvl(date3,to_date('01/01/9999','MM/DD/YYYY')) ) least_date
, greatest( nvl(date1,to_date('01/01/0001','MM/DD/YYYY')), nvl(date2,to_date('01/01/0001','MM/DD/YYYY')), nvl(date3,to_date('01/01/0001','MM/DD/YYYY')) ) greatest_date
from tab1
);

Open in new window

fb1990Author Commented:
You are very right about the union all.  I actually changed and it was longer...

Thanks for you assistance as always
PortletPaulEE Topic AdvisorCommented:
I was unable to return to this question before it was closed, sorry.

An alternative approach, which is dbms independent and requires just a single pass of the data - AND solves NULLs as well - is to use a small unioned set of numbers, one per date field. Cross join this to the source data and move all the dates into a single columns using a combination of case expressions and coalesce. Once the dates are in a single column it's a simple mater of finding the MIN/MAX of that column.

Sample data:
CREATE TABLE Table1
    (USER1 int, DATE1 DATE, DATE2 DATE, DATE3 DATE, DATE4 DATE, DATE5 DATE, DATE6 DATE, DATE7 DATE)
;

INSERT ALL 
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1111, NULL, TO_DATE('2015-03-02','YYYY-MM-DD'), TO_DATE('2015-07-03','YYYY-MM-DD'), TO_DATE('2015-02-07','YYYY-MM-DD'), TO_DATE('2015-01-13','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'))
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1112, TO_DATE('2015-03-03','YYYY-MM-DD'), NULL, TO_DATE('2015-07-03','YYYY-MM-DD'), TO_DATE('2015-02-07','YYYY-MM-DD'), TO_DATE('2015-01-13','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'))
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1113, TO_DATE('2015-03-03','YYYY-MM-DD'), TO_DATE('2015-03-02','YYYY-MM-DD'), NULL, TO_DATE('2015-02-07','YYYY-MM-DD'), TO_DATE('2015-01-13','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'))
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1114, TO_DATE('2015-03-03','YYYY-MM-DD'), TO_DATE('2015-03-02','YYYY-MM-DD'), TO_DATE('2015-07-03','YYYY-MM-DD'), NULL, TO_DATE('2015-01-13','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'))
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1115, TO_DATE('2015-03-03','YYYY-MM-DD'), TO_DATE('2015-03-02','YYYY-MM-DD'), TO_DATE('2015-07-03','YYYY-MM-DD'), TO_DATE('2015-02-07','YYYY-MM-DD'), NULL, TO_DATE('2015-08-16','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'))
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1116, TO_DATE('2015-03-03','YYYY-MM-DD'), TO_DATE('2015-03-02','YYYY-MM-DD'), TO_DATE('2015-07-03','YYYY-MM-DD'), TO_DATE('2015-02-07','YYYY-MM-DD'), TO_DATE('2015-01-13','YYYY-MM-DD'), NULL, TO_DATE('2015-08-16','YYYY-MM-DD'))
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1117, TO_DATE('2015-03-03','YYYY-MM-DD'), TO_DATE('2015-03-02','YYYY-MM-DD'), TO_DATE('2015-07-03','YYYY-MM-DD'), TO_DATE('2015-02-07','YYYY-MM-DD'), TO_DATE('2015-01-13','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'), NULL)
    INTO Table1 (USER1, DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7)
         VALUES (1118, TO_DATE('2015-03-03','YYYY-MM-DD'), TO_DATE('2015-03-02','YYYY-MM-DD'), TO_DATE('2015-07-03','YYYY-MM-DD'), TO_DATE('2015-02-07','YYYY-MM-DD'), TO_DATE('2015-01-13','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'), TO_DATE('2015-08-16','YYYY-MM-DD'))
SELECT * FROM dual
;

Open in new window

Query:
SELECT
      USER1
    , MIN(ALL_DATES) MIN_DATE
    , MAX(ALL_DATES) MAX_DATE
FROM (
        SELECT
                T.USER1
              , COALESCE(
                CASE WHEN U.N = 1 THEN T.DATE1 END
              , CASE WHEN U.N = 2 THEN T.DATE2 END
              , CASE WHEN U.N = 3 THEN T.DATE3 END
              , CASE WHEN U.N = 4 THEN T.DATE4 END
              , CASE WHEN U.N = 5 THEN T.DATE5 END
              , CASE WHEN U.N = 6 THEN T.DATE6 END
              , CASE WHEN U.N = 7 THEN T.DATE7 END
                  ) ALL_DATES
        FROM TABLE1 T
        CROSS JOIN (
                  SELECT 1 AS N FROM DUAL
                  UNION ALL
                  SELECT 2 FROM DUAL
                  UNION ALL
                  SELECT 3 FROM DUAL
                  UNION ALL
                  SELECT 4 FROM DUAL
                  UNION ALL
                  SELECT 5 FROM DUAL
                  UNION ALL
                  SELECT 6 FROM DUAL
                  UNION ALL
                  SELECT 7 FROM DUAL
                  ) U
      ) G
GROUP BY USER1
ORDER BY USER1;

Open in new window

Result:
| USER1 |                   MIN_DATE |                 MAX_DATE |
|-------|----------------------------|--------------------------|
|  1111 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1112 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1113 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1114 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1115 | February, 07 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1116 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1117 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1118 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |

Open in new window

see: http://sqlfiddle.com/#!4/39388/13
explain plan (sample data)
PortletPaulEE Topic AdvisorCommented:
And another, Oracle specific, using the UNPIVOT operator
SELECT
      USER1
    , MIN(ALL_DATES) MIN_DATE
    , MAX(ALL_DATES) MAX_DATE
FROM (
      SELECT * 
      FROM Table1
        UNPIVOT EXCLUDE NULLS (all_dates FOR Colsource IN ( DATE1, DATE2, DATE3, DATE4, DATE5, DATE6, DATE7))
      )
GROUP BY USER1
ORDER BY USER1; 

Open in new window

Result:
| USER1 |                   MIN_DATE |                 MAX_DATE |
|-------|----------------------------|--------------------------|
|  1111 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1112 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1113 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1114 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1115 | February, 07 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1116 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1117 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |
|  1118 |  January, 13 2015 00:00:00 | August, 16 2015 00:00:00 |

Open in new window

http://sqlfiddle.com/#!4/39388/17
Explain Plan
fb1990Author Commented:
wow!  Thank you so much Paul.  Much appreciated!
PortletPaulEE Topic AdvisorCommented:
No problem.

Which solution did you end up using?

By the way, most of my suggested solutions (all except least/greatest) have centered on the principle of normalizing the data. That is, transposing those multiple date columns into multiple rows with just one date column.  Once you have that the rest is easy.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.