Insert records incrementing date field

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 2000 Forums

Transact-SQL (2000)

Insert records incrementing date field

Author

Topic

rolavarrieta
Starting Member

2 Posts

Posted - 2008-12-10 : 15:17:36

I have a table which contains records as follows:

Unique ID StartDate EndDate NumMonths Total
1234 20080101 20080531 5 500

I would like to insert into a new table as many records are the NumMonths above incrementing the StartDate by one month for each records. Essential I would like my output to be as follows:

1234 20080101 20080131 1 100
1234 20080201 20080229 1 100
1234 20080301 20080331 1 100
1234 20080401 20080430 1 100
1234 20080501 20080531 1 100

Can anyone provide some insight as to how I can achieve this?

Thank you,
Rene O

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 15:46:56

Try INNER JOIN the master..spt_values table.

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

rolavarrieta
Starting Member

2 Posts

Posted - 2008-12-10 : 15:51:58

I'm sorry but I'm not sure I understand what you mean. Can you elaborate? Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 16:11:18

[code]DECLARE @Sample TABLE
(
UniqueID INT,
StartDate DATETIME,
EndDate DATETIME,
NumMonths INT,
Total INT
)

INSERT @Sample
SELECT 1234,
'20080101',
'20080531',
5,
500

SELECT s.UniqueID,
DATEADD(MONTH, v.Number, s.StartDate) AS StartDate,
DATEADD(MONTH, 1 + v.Number, s.StartDate - 1) AS EndDate,
1 AS NumMonths,
s.Total / (1.0E + DATEDIFF(MONTH, s.StartDate, s.EndDate)) AS Total
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number <= DATEDIFF(MONTH, s.StartDate, s.EndDate)[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources