SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MIN value from dates in two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bazinga
Starting Member

19 Posts

Posted - 08/22/2013 :  10:33:13  Show Profile  Reply with Quote
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!

Edited by - Bazinga on 08/22/2013 10:35:45

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/22/2013 :  10:53:58  Show Profile  Reply with Quote
Something like this:

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];

Go to Top of Page

Bazinga
Starting Member

19 Posts

Posted - 08/22/2013 :  11:34:14  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/22/2013 :  11:38:54  Show Profile  Reply with Quote
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





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]
Go to Top of Page

Bazinga
Starting Member

19 Posts

Posted - 08/22/2013 :  14:01:14  Show Profile  Reply with Quote
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.

Edited by - Bazinga on 08/22/2013 14:01:47
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/22/2013 :  14:56:10  Show Profile  Reply with Quote
Is this what you are looking for:


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]

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000