StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIS a MIN(coalesce) across multiple tables possible / the best solution for this query in sql 2005?
text
Body
copied!<p>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:</p> <pre><code>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) </code></pre> <p>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??)</p> <p>The pseudo syntax for what I want would be something like:</p> <pre><code>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 </code></pre> <p>So that I can then group all those with table4_date_created as the first event by year to end up with something like</p> <pre><code>YEAR | Count 2010 | 1500 2009 | 2500 2008 | 1596 </code></pre> <p>Thanks!</p> <p><strong>EDIT TO INCLUDE SAMPLE DATA</strong></p> <p>CASE 1 should return a '1' in quassnoi's syntax (returns a 0)</p> <pre><code>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 </code></pre> <p>CASE 2 (should return a 0 ) currently gives a 0</p> <pre><code>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 </code></pre> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload