Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of sameer2010
sameer2010
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If there are multiple months, you need to select month along with row_number in the inner query
Avatar of Amour22015
Amour22015

ASKER

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

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

This is little confusing. Can you put actual input and output?
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...
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...
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.
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
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...
Thanks