TSQL - Date ranges

Amour22015
Amour22015 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
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

sameer2010

Commented:
If there are multiple months, you need to select month along with row_number in the inner query
Amour22015

Author

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...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Amour22015

Author

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

Amour22015

Author

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

sameer2010

Commented:
This is little confusing. Can you put actual input and output?
Amour22015

Author

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...
Amour22015

Author

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...
Amour22015

Author

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.
sameer2010

Commented:
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
Amour22015

Author

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...
Amour22015

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial