Date split end of month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date split end of month

Author

Topic

hawk1992
Starting Member

3 Posts

Posted - 2011-04-28 : 05:02:04

Hi everyone i was looking for a tric to do what i think a simple manipulation for an Sql expert.

i have a table wich contain a start date and end date

every end of month i need to insert into another table end date - start date but not at once, i think still not clear let's see an exemple :

first table : start date = 02-05-2011

end_date = 08-07-2011

I will run the script every month and first end of month (31/05/2011)
it should insert into my second table this :

sub_start_date : 02-05-2011
sub_end_date : 31-05-2011

next end of month(30/06-2011)

sub_start_date : 01-06-2011
sub_end_date : 30-06-2011

next end of month (31/07/2011)

sub_start_date : 01-07-2011

sub_end_date : 08-07-2011 (the end date not the end of the month)

i saw in a previous post a similar problem the answer was liek this :
---------------------------------------------------------------------
declare @TT table
(
TTID varchar(10),
start_date datetime,
end_date datetime
)

insert into @TT select '21354', '2011-01-12', '2011-03-02'
insert into @TT select '21355', '2011-04-12', '2011-04-27'

-- The Query
select TTXID = t.TTID + convert(varchar(10), row_number() over (partition by t.TTID order by v.number)),
actual_start_date = case when v.number = 0
then t.start_date
else dateadd(month, datediff(month, 0, t.start_date) + v.number, 0)
end,
actual_end_date = case when month(t.end_date) = month(dateadd(month, datediff(month, 0, t.start_date) + v.number + 1, -1))
then t.end_date
else dateadd(month, datediff(month, 0, t.start_date) + v.number + 1, -1)
end
from @TT t
inner join master..spt_values v on v.type = 'P'
where v.number >= 0
and v.number <= datediff(month, start_date, end_date)
------------------------------------------------------------------

but this is doing all at once, and not inserting the result in the second table.

please help a newbie in sql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-28 : 06:00:46

Something like this?

DECLARE	@Sample TABLE
	(
		ID VARCHAR(10) NOT NULL,
		StartDate DATETIME NOT NULL,
		EndDate DATETIME NOT NULL
	)

INSERT	@Sample
SELECT	'21354',
	'20110112',
	'20110302'

UNION ALL

SELECT	'21355',
	'20110412',
	'20110427'

-- Solution starts here
/*
INSERT		dbo.TargetTable
		(
			ID,
			StartDate,
			EndDate,
			NextEndDate
		)
*/
SELECT		ID,
		CASE
			WHEN DATEADD(MONTH, v.Number, s.StartMonth) < s.StartDate THEN s.StartDate
			ELSE DATEADD(MONTH, v.Number, s.StartMonth)
		END AS StartDate,
		CASE
			WHEN DATEADD(MONTH, v.Number, s.EndMonth) > s.EndDate THEN s.EndDate
			ELSE DATEADD(MONTH, v.Number, s.EndMonth)
		END AS EndDate,
		DATEADD(MONTH, 1 + v.Number, s.EndMonth) AS NextEndMonth
FROM		(
			SELECT	ID,
				StartDate,
				EndDate,
				DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) AS StartMonth,
				DATEADD(MONTH, DATEDIFF(MONTH, -1, StartDate), -1) AS EndMonth
			FROM	@Sample
		) AS s
INNER JOIN	[MASTER].dbo.spt_values AS v ON v.[Type] = 'P'
			AND v.Number <= DATEDIFF(MONTH, s.StartDate, s.EndDate)

N 56°04'39.26"
E 12°55'05.63"

hawk1992
Starting Member

3 Posts

Posted - 2011-04-28 : 07:54:39

Hi thank you for your quick answer just a remark

what i miss here is that i want to insert in the targetTable just the acheived interval if we take the same example : if i run the script at the end of january (it will be a monthly executed stored procedure) it must just insert in the target table :

21354(Id) ; 2011-01-12 2011-01-31

however i don't know if it's possible just with SQL because i don't have an idea how to look if the ID isn't exesting before inserting because i don't wont that every month it duplicate all things.

so to resume it would be cool if we can cut the interval in the sample table just like you did it was great but to insert each part not at once but every end of month (time in wich the SP will run)

kind regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources