TSQL - Date ranges

Hi Experts,

I have this data:

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
NULL
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


I took the Null out so please disregard.
 
I will be inserting this into:

Table.ControlDate Where transactionDate is the first day of the month
Table.LastDate Where transactionDate is the first day of the next month
Table.PostDate Where transactionDate is the first day of the next month

So:
Table.ControlDate  = 2005-07-15 16:12:46.000
Table.LastDate = 2005-08-01 17:31:21.000
Table.PostDate = 2005-08-01 17:31:21.000

I hope this is clear and if not please let me know.

Thanks
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sameer2010Commented:
Try this 
declare @a table(a datetime)
insert into @a values('2005-07-15 16:12:46.000'),('2005-07-15 16:19:22.000'),('2005-07-26 10:44:08.000'),('2005-08-01 17:31:21.000')
select a1 as controldt, (select min(a) from @a where datepart(month,a1)+1=datepart(month,a)) as lastdt, 
(select min(a) from @a where datepart(month,a1)+1=datepart(month,a)) as postdt
from(
select a as a1,rank() over(order by a desc) rnk 
from @a where datepart(mm,a) = (select datepart(MONTH,min(a)) from @a)
) t1
where rnk=1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sameer2010Commented:
If there are multiple months, you need to select month along with row_number in the inner query
Amour22015Author Commented:
Wow,

I am knew to all this, but this is what i have so far:
Select
'ZCNV'+ Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS BatchID,
'NCA' AS AcctCompany,
min([transactionDate]) AS ControlDate,
'Conversion'+ ' ' + 'Batch' + ' ' + Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS [Description],
max([transactionDate]) AS LastDate
  FROM [ncausa].[dbo].[ams_trans]
  Where transactionDate Is Not Null
  Group By [transactionDate]
  Order by BatchID

Open in new window


As you can see there is still work from my last post that you answered great.

So I will need help on what you provided into my current query?

Please help and thanks...
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

FREE DOWNLOAD
Amour22015Author Commented:
And I should say that it is currently not working the way I want.

I tryed:
Select Distinct
but still I get several rows like:
ZCNV0507
ZCNV0507
ZCNV0507
etc...

and i am only looking for one:
ZCNV0507 then the next:
ZCNV0508
etc...

Please help and thanks...
Select
'ZCNV'+ Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS BatchID,
'NCA' AS AcctCompany,
min([transactionDate]) AS ControlDate,
'Conversion'+ ' ' + 'Batch' + ' ' + Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS [Description],
max([transactionDate]) AS LastDate
  FROM [ncausa].[dbo].[ams_trans]
  Where transactionDate Is Not Null
  Group By [transactionDate]
  Order by BatchID

Open in new window

Amour22015Author Commented:
So I would need to do something like:
declare @a table(a datetime)
insert into @a values([ncausa].[dbo].[ams_trans].[transactionDate]),([ncausa].[dbo].[ams_trans].[transactionDate]),([ncausa].[dbo].[ams_trans].[transactionDate]),([ncausa].[dbo].[ams_trans].[transactionDate])
select a1 as controldt, (select min(a) from @a where datepart(month,a1)+1=datepart(month,a)) as lastdt, 
(select min(a) from @a where datepart(month,a1)+1=datepart(month,a)) as postdt
from(
select a as a1,rank() over(order by a desc) rnk 
from @a where datepart(mm,a) = (select datepart(MONTH,min(a)) from @a)
) t1
where rnk=1

Open in new window


But I get syntax errors on trying to include the table.transactionDate

Please help and thanks...
Select
'ZCNV'+ Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS BatchID,
'NCA' AS AcctCompany,
min([transactionDate]) AS ControlDate,
'Conversion'+ ' ' + 'Batch' + ' ' + Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS [Description],
max([transactionDate]) AS LastDate
  FROM [ncausa].[dbo].[ams_trans]
  Where transactionDate Is Not Null
  Group By [transactionDate]
  Order by BatchID

Open in new window

sameer2010Commented:
This is little confusing. Can you put actual input and output?
Amour22015Author Commented:
Why are you having me use a temp table?

I thought I would be using loops to get the range?

min([transactionDate]) AS ControlDate
max([transactionDate]) AS LastDate
max([transactionDate]) AS PostDate

Please help and thanks...
Amour22015Author Commented:
Ok,

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


Here is what I need:
Column: FirstDay
2005-07-15
Next Column: LastDay
2005-08-01
Next Column: PostDay
2005-08-01

Open in new window

Like:
Column: FirstDay                 LastDay                                  PostDay
             2005-07-15               2005-08-01                           2005-08-01

all on the same Row/Record.

Please help and thanks...
Amour22015Author Commented:
Looks like there was a lot of work to do on this, I finally got the correct answer by surfing google on the topic and this is what I ended up with:
DECLARE @dt1 Datetime
DECLARE @dt2 Datetime
 
Select @dt1 = dbo.fncUtlDateBeginning(MIN(transactionDate),'mm'), @dt2 = dbo.fncUtlDateBeginning(MAX(transactionDate),'mm') 
From ams_Trans 
While @dt1 <= @dt2
Begin
Insert Into [NOAH2-NCA].dbo.tblNAsBatchControl(BatchID, AcctCompany, ControlDate, Description, LastDate, Posted, Assn)
	Select 'ZCNV' + Right('0' + Convert(nvarchar, Year(@dt1)),2) + Right('0' + Convert(nvarchar, Month(@dt1)),2) AS BatchID,
		'NCA' AS AcctCompany,
		@dt1 AS [ControlDate],
		'Conversion Batch' As [Description],
		DateAdd(m,1,@dt1) AS LastDate,
		DateAdd(m,1,@dt1) AS Posted,
		'NCA' AS Assn
	Set @dt1 = DateAdd(m,1,@dt1)
End

Open in new window


Thank you for helping me,  I am knew to all this and should have first been advised to write a little program to do this?

Maybe that was what sameer2010 was trying to do with ID: 40872702, but that just confused me.
sameer2010Commented:
Hi,

The query I had given does exactly the same thing that you posted in your requirement. Did you find it not working?

Thanks,
Sam
Amour22015Author Commented:
sameer2010,

I ran your query, maybe I was confused but it did not seem to do the same thing.

Like:
('2005-07-15 16:12:46.000'),('2005-07-15 16:19:22.000'),('2005-07-26 10:44:08.000'),('2005-08-01 17:31:21.000')

Isn't that hardcoding?

I wanted to use:
transactionDate
and where am I bring in the table?

Thanks...
Amour22015Author Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.