AboutPricingCommunityTeamsStart 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
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Avatar of undefined
Last Comment
Amour22015
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
sameer2010
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
sameer2010
If there are multiple months, you need to select month along with row_number in the inner query
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...
Amour22015
ASKER
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Amour22015
ASKER
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
This is little confusing. Can you put actual input and output?
Amour22015
ASKER
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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Amour22015
ASKER
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
ASKER
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
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Amour22015
ASKER
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
ASKER
Thanks
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent