asked on

TSQL - Taking a range of dates per month into three different columns

Hi Experts,

I so far have this Query:

Select min([transactionDate]) AS ControlDate, max([transactionDate]) AS LastDate
  FROM [ncausa].[dbo].[ams_trans]

Open in new window

But this gives me the start and end of all that is in the table. I want it to be by month.

But it is not working the way I need it to and it was mentioned to use a DataAdd with looping to get what I want?

Here is the input from table.transactionDate :

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 10:58:54.000
2005-07-20 14:21:49.000
2005-07-20 14:21:49.000
2005-07-20 04:04:59.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

Open in new window

But this is what I am looking for:

Like:
Column: ControlDate                 LastDay                                 LastDay
             2005-07-15               2005-08-01                           2005-08-01

Open in new window

Please help and thanks...

Kyle Abrahams, PMP

You can group by the year and month.

    select DATEPART(year, transactionDate) yr, DATEPART(month, transactionDate) mnth, MIN(transactionDate) controldate, MAX(transactionDate) lastDate FROM [ncausa].[dbo].[ams_trans]
                               group by DATEPART(year, transactionDate), DATEPART(month, transactionDate)

Open in new window

Mayank Gairola

So if I am understanding the question correctly then you want the query to return a single row for every month having columns as min date and max date twice. Not sure why you want the same column twice but the below query should work.

select MIN(transactionDate) controldate, MAX(transactionDate) lastDate, MAX(transactionDate) lastDay
FROM [ncausa].[dbo].[ams_trans]
group by DATEPART(year, transactionDate), DATEPART(month, transactionDate)

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Great