Menu
I have a table (in MS SQL 2005) with a selection of dates. I want to be able to apply a WHERE statement to return a group of them and then return which date is the earliest from one column and which one is the latest from another column. Here is an example table:
ID StartDate EndDate Person
1 01/03/2010 03/03/2010 Paul
2 12/05/2010 22/05/2010 Steve
3 04/03/2101 08/03/2010 Paul
So I want to return all the records where Person = 'Paul'. But return something like (earliest ) StartDate = 01/03/2010 (from record ID 1) and (latest) EndDate = 08/03/2010 (from record ID 3).
Thanks in advance
714
First, create an aggregate query that has two fields: GroupID and RecordDate. Group by the GroupID field, and choose the "Min" option for the RecordDate, to return the earliest date for each query. Now, create a new query, doing a left-join from your original table to your aggregate query, joining on the GroupID field.
If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case. ORDER BY ExamDate DESC ; Note that in T-SQL, NULL s are displayed first when sorting in ascending order and last when sorting in descending order.
The above query will display the year, month, day, and last date of the current month. We have used: YEAR function to display current year from SQL Server GETDATE function. MONTH function to display current month number from SQL Server GETDATE function.
You need the min and max aggregate functions, e.g. a very simple case:
select min(StartDate), max(EndDate)
from data
where Person = 'Paul'
You have all the usual power of SQL, so selection from a sub-query is available.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
