MIN value from dates in two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN value from dates in two tables

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2013-08-22 : 10:33:13

I have two tables. They each have an ID and one or more dates per ID. I.E.

TABLE1
0001 1/1/2012
0001 2/1/2012
0002 5/1/2012
0003 6/1/2012
ETC

TABLE2
0001 4/1/2012
0001 5/1/2012
0002 6/1/2012
ETC.

I would like to create a result set that will pull the minimum date value from each table per ID. For example:

RESULTS
ID TABLE1-MIN TABLE2-MIN
0001 1/1/2012 4/1/2012
0002 5/1/2012 6/1/2012
ETC

Need a little bit of help.

To take it one step further, I would also like to be able to show the min date from table2 only when that min date is within a certain range, i.e. between 1/1/2012 - 12/31/2012, else it just remains a null in the results.

Thanks!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 10:53:58

Something like this:
[CODE]
SELECT T1.[ID], MIN(T1.[DateAndTime]) as Table1Date,
(CASE WHEN MIN(T2.[DateAndTime]) BETWEEN '1/1/2013' and '12/31/2013' THEN MIN(T2.[DateAndTime]) ELSE NULL END) as Table2Date
from [Table1] T1 INNER JOIN [Table2] T2
ON T1.[ID] = T2.[ID] GROUP BY T1.[ID], T2.[ID];

[/CODE]

Bazinga
Starting Member

19 Posts

Posted - 2013-08-22 : 11:34:14

Thanks! That worked!

Now, can I calculate the number of days from the Min Table1 date to the Min Table2 date and have it show as a column in the results? So something like:

0001 1/1/2012 4/1/2012 90 Days

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 11:38:54

quote:
Originally posted by Bazinga

Thanks! That worked!

Now, can I calculate the number of days from the Min Table1 date to the Min Table2 date and have it show as a column in the results? So something like:

0001 1/1/2012 4/1/2012 90 Days

[CODE]

DATEDIFF(dd, MIN(T1.[DateAndTime]), (CASE WHEN MIN(T2.[DateAndTime]) BETWEEN '1/1/2013' and '12/31/2013' THEN MIN(T2.[DateAndTime]) ELSE NULL END)) as [Days]
[/CODE]

Bazinga
Starting Member

19 Posts

Posted - 2013-08-22 : 14:01:14

Perfect. Taking it one step further again.

I want the results to only include data where both the MIN conditions that are part of the data range are true. Right now, I see null values when the dates are not within the range.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 14:56:10

Is this what you are looking for:
[CODE]

COALESCE(DATEDIFF(dd,
(CASE WHEN MIN(T1.[DateAndTime]) BETWEEN '1/1/2013' and '12/31/2013' THEN MIN(T2.[DateAndTime]) ELSE NULL END),
(CASE WHEN MIN(T2.[DateAndTime]) BETWEEN '1/1/2013' and '12/31/2013' THEN MIN(T2.[DateAndTime]) ELSE NULL END)), '') as [Days]

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources