asked on

SQL Server 2008 - Returning a result set with multiple rows based on max date and MIN Date

Hi Experts,

I have this query:

Select c.transactionDate
From ams_trans c inner join
(select MAX([transactionDate]) as maxdate From ams_trans group by transactionDate) c2
On c2.maxdate = c.transactionDate
Order by c.transactionDate

Open in new window

But my results are not what I am looking for:

transactionDate
2005-07-15 16:12:46.000
2005-07-15 16:12:46.000
2005-07-15 16:19:22.000
2005-07-18 09:31:29.000
2005-07-18 09:31:29.000
2005-07-18 11:00:36.000
2005-07-18 11:00:36.000
2005-07-20 04:04:59.000
2005-07-20 10:58:54.000
2005-07-20 14:21:49.000
2005-07-20 14:21:49.000
2005-07-20 16:13:38.000
2005-07-20 16:13:38.000
2005-07-21 08:32:49.000
2005-07-21 08:32:49.000
2005-07-21 08:36:49.000
2005-07-21 08:45:13.000
2005-07-21 15:14:59.000
2005-07-22 14:03:08.000
2005-07-22 14:03:08.000
2005-07-25 09:07:57.000
2005-07-25 09:07:57.000
2005-07-25 11:43:37.000
2005-07-25 11:45:24.000
2005-07-25 11:46:52.000
2005-07-25 11:47:34.000
2005-07-25 11:48:25.000
2005-07-25 11:50:22.000
2005-07-25 12:39:06.000
2005-07-25 12:48:04.000
2005-07-25 12:48:59.000
2005-07-26 10:38:08.000
2005-07-26 10:38:08.000
2005-07-26 10:44:08.000
2005-07-26 10:44:08.000
2005-07-26 18:16:36.000
2005-07-26 18:16:36.000
2005-07-27 09:30:16.000
2005-07-29 10:51:09.000
2005-08-01 17:31:21.000
2005-08-01 17:31:21.000

Open in new window

Record Set = Min(beginning of month) and MAX(Ending of each month)
I would like to be able to get a result set like this - one record for each record set with the latest date and with the beginning date:
Column: MINDate MAXDate MAXDate
2005-07-15 2005-08-01 2005-08-01

I know my query is not complete and it needs modifying to include the MIN date as well.

Maybe Experts are confused because I am doing this all wrong and need to create a program to loop with a StartDate/Enddate??? In which case please let me know to forget the query and do over???

Like Experts answered this question:
TSQL While Loop for a date range
Question by: JFranks22 On 2012-01-25 03:33 PM Views 3,143
Hi Experts,

We have a query that works well for one day, but... we need the data produced from the query for the past 4 years. This is a once off query, so performance is not an issue.

I am looking for a way to use a loop to run a query:
- Between a date range (Jan 1, 2008 - Jan 1, 2012), OR
- Use a count for the number of days - 4 years = 1460

Sample pseudo code:
DECLARE @StartDate = 1/1/2008

WHILE @StartDate =< 1/1/2012
{
Print 'SELECT @StartDate, COUNT(*) FROM Reservations where DateCreated = @StartDate'
@StartDate++;
}
END

Not sure if we need to write to a temp table and then select the results from there.

Utlimately, the result should be a result table with the result per day:
Date | Result
1/1/2008 | 15
2/1/2008 | 22
3/1/2008 | 9
4/1/2008 | 11
5/1/2008 | 13
6/1/2008 | 17

Thanks for your thoughts!

Please help and thanks....

The only difference is that I am not hardcoding my dates. Like:
Between a date range (Jan 1, 2008 - Jan 1, 2012),

I am using the fields from the table like:
Between a date range (MIN([transactionDate]) - MAX([transactionDate])), And each is by month.

Please help and thanks.....

SOLUTION