IS a MIN(coalesce) across multiple tables possible / the best solution for this query in sql 2005?

Question

Hi, I am looking to pull out the minimum value across three tables and then compare this to the lowest value in a fourth table, where the tables are structure like this:

Table 1
(event_id (PK uniqueidentifier),date_created,contact_id)

Table 2
(event_id (PK uniqueidentifier),date_created,contact_id)

Table 3
(event_id (PK uniqueidentifier),date_created,contact_id)

Table 4
(event_id (PK uniqueidentifier),date_created,contact_id)

Where contact_id joins all of the tables and not all contacts will have an event_id specified in all of the tables (hence the possible need for a coalesce function??)

The pseudo syntax for what I want would be something like:

SELECT(COALESCE(MIN(table1date_created),MIN(table2date_created),MIN(table3date_created) AS earliest_date

CASE WHEN (MIN(earliest_date) < MIN(table4_date_created) THEN '0' ELSE '1' END AS first_event

So that I can then group all those with table4_date_created as the first event by year to end up with something like

YEAR  |  Count
2010  |  1500
2009  |  2500
2008  |  1596

Thanks!

EDIT TO INCLUDE SAMPLE DATA

CASE 1 should return a '1' in quassnoi's syntax (returns a 0)

Dates in table 1
1991-10-24 00:00:00.000 --first event ever
1995-08-03 00:00:00.000
1990-01-03 00:00:00.000
2000-12-31 00:00:00.000
2000-12-31 00:00:00.000
2000-02-08 00:00:00.000
2002-07-03 00:00:00.000
1999-01-06 00:00:00.000

Dates in table 2
2007-02-02 00:40:30.823

Dates in table 3
2006-09-23 01:13:15.530

Dates in table 4
2006-05-31 00:00:00.000    

CASE 2 (should return a 0 ) currently gives a 0

dates in table 1
2007-11-29 00:00:00.000
2007-08-17 00:00:00.000
2005-12-06 00:00:00.000
2007-05-04 00:00:00.000
2006-08-07 00:00:00.000
2007-06-13 00:00:00.000

Dates in  table 2
2006-06-08 02:08:07.253

dates in table 3
2006-06-08 02:08:07.253

dates in table 4
2001-05-31 00:00:00.000
2000-05-31 00:00:00.000 --first event ever  

So the problem is not just a case of the wrong numeric response being returned, they are all being given the same number (0) when some should be given a 0 and others a 1 Would it be a problem that within each table there may be mutliple occurences of the same date?

Answer 1

SELECT  CASE
        WHEN
        (
        SELECT  MIN(date_created)
        FROM    (
                SELECT  date_created
                FROM    table1
                UNION ALL
                SELECT  date_created
                FROM    table2
                UNION ALL
                SELECT  date_created
                FROM    table3
                ) q
        ) >=
        (
        SELECT  MIN(date_created)
        FROM    table4
        )
        THEN    1
        ELSE    0
        END

Update

SET LANGUAGE ENGLISH
DECLARE @table1 TABLE (date_created DATETIME)
DECLARE @table2 TABLE (date_created DATETIME)
DECLARE @table3 TABLE (date_created DATETIME)
DECLARE @table4 TABLE (date_created DATETIME)

INSERT
INTO    @table1
VALUES  ('1991-10-24 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('1995-08-03 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('1990-01-03 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2000-12-31 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2000-12-31 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2000-02-08 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2002-07-03 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('1999-01-06 00:00:00.000')

INSERT
INTO    @table2
VALUES  ('2007-02-02 00:40:30.823')

INSERT
INTO    @table3
VALUES  ('2006-09-23 01:13:15.530')

INSERT
INTO    @table4
VALUES  ('2006-05-31 00:00:00.000') 

SELECT  CASE
        WHEN
        (
        SELECT  MIN(date_created)
        FROM    (
                SELECT  date_created
                FROM    @table1
                UNION ALL
                SELECT  date_created
                FROM    @table2
                UNION ALL
                SELECT  date_created
                FROM    @table3
                ) q
        ) >=
        (
        SELECT  MIN(date_created)
        FROM    @table4
        )
        THEN    1
        ELSE    0
        END

DELETE @table1
DELETE @table2
DELETE @table3
DELETE @table4

INSERT
INTO    @table1
VALUES  ('2007-11-29 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2007-08-17 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2005-12-06 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2007-05-04 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2006-08-07 00:00:00.000')
INSERT
INTO    @table1
VALUES  ('2007-06-13 00:00:00.000')
INSERT
INTO    @table3
VALUES  ('2006-06-08 02:08:07.253')
INSERT
INTO    @table3
VALUES  ('2006-06-08 02:08:07.253')
INSERT
INTO    @table4
VALUES  ('2001-05-31 00:00:00.000')
INSERT
INTO    @table4
VALUES  ('2000-05-31 00:00:00.000')

SELECT  CASE
        WHEN
        (
        SELECT  MIN(date_created)
        FROM    (
                SELECT  date_created
                FROM    @table1
                UNION ALL
                SELECT  date_created
                FROM    @table2
                UNION ALL
                SELECT  date_created
                FROM    @table3
                ) q
        ) >=
        (
        SELECT  MIN(date_created)
        FROM    @table4
        )
        THEN    1
        ELSE    0
        END

The two queries returns 0 and 1, exactly as they should.

Answer 2

Select the TOP 1 MIN if each table, use UNION, then select the min of that UNION result. (the first three), then UNION that with a TOP 1 MIN on the fourth table MIN value with an artificial value column (0 or 1) for each of the first 3 result and the fourth table result.

Psuedo code:

SELECT holder FROM (
  SELECT TOP 1 MIN(B), holder FROM (
    SELECT TOP 1 MIN(table4.mycolmn) as B, 0 as holder from table4
    UNION
    SELECT TOP 1 MIN(C) as B, 1 as holder FROM (
         SELECT TOP 1 MIN(mycolum) as C FROM table1
         UNION
         SELECT TOP 1 MIN(mycolum) as C FROM table2
         UNION
         SELECT TOP 1 MIN(mycolum) as C FROM table3)
    )
)