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.
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.TABLE10001 1/1/20120001 2/1/20120002 5/1/20120003 6/1/2012ETCTABLE20001 4/1/20120001 5/1/20120002 6/1/2012ETC.I would like to create a result set that will pull the minimum date value from each table per ID. For example:RESULTSID TABLE1-MIN TABLE2-MIN0001 1/1/2012 4/1/20120002 5/1/2012 6/1/2012ETCNeed 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] |
|
|
|
|
|
|
|