# Create data records (one record per month)

• ### Frage

• Hello together,

I would like to create n data records depending on the difference between start and an end date. my original record looks like this:

[START DATE] [ENDDATE] [VALUE]
Jan 2009           Mar 2009    3000

Result should be
[Date] [Value]
Jan 2009   1000
Feb 2009   1000
Mar 2009  1000

Calculating the value per month is not the problem for me. The Problem is how to create the data records?

Thx for you help and brgds,

Maik

Montag, 26. Oktober 2009 15:19

### Antworten

• Hello Maik,

you can use a common table expression (CTE) to generate the dates to split the values for each month.
But I agree with my namesake, you better use a permant table instead of a CTE; with larger amounts it will have a bad performance.

CREATE TABLE #val

([START] datetime,

[END] datetime,

[VALUE] int)

INSERT INTO #val VALUES ('20090101', '20090301', 3000)

INSERT INTO #val VALUES ('20090501', '20090601', 4000)

GO

CREATE TABLE #val

([START] datetime,

[END] datetime,

[VALUE] int)

INSERT INTO #val VALUES ('20090101', '20090301', 3000)

INSERT INTO #val VALUES ('20090501', '20090601', 4000)

GO

WITH [loops] (EveryFirstDayOfYear) AS

(SELECT CONVERT(datetime, '20090101', 112) AS EveryFirstDayOfYear

UNION ALL

SELECT DATEADD(m, 1, EveryFirstDayOfYear) AS EveryFirstDayOfYear

FROM [loops]

WHERE EveryFirstDayOfYear < CONVERT(datetime, '20091231', 112))

SELECT EveryFirstDayOfYear

,[Value] / (1 + DATEDIFF(m, [Start], [End]))

FROM #val

INNER JOIN [loops]

ON [loops].EveryFirstDayOfYear

BETWEEN #val.[Start] AND #val.[End]

GO

DROP TABLE #val

EveryFirstDayOfYear

----------------------- -----------

2009-01-01 00:00:00.000 1000

2009-02-01 00:00:00.000 1000

2009-03-01 00:00:00.000 1000

2009-05-01 00:00:00.000 2000

2009-06-01 00:00:00.000 2000

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
Donnerstag, 29. Oktober 2009 12:16

### Alle Antworten

• For creating the data records I would use a permanent help table containing the date-data for each month (month, year).
You can select the data rows with the difference of your [START DATE] [ENDDATE].
This table could be populated with a while loop.
Mittwoch, 28. Oktober 2009 11:54
• Hello Maik,

you can use a common table expression (CTE) to generate the dates to split the values for each month.
But I agree with my namesake, you better use a permant table instead of a CTE; with larger amounts it will have a bad performance.

CREATE TABLE #val

([START] datetime,

[END] datetime,

[VALUE] int)

INSERT INTO #val VALUES ('20090101', '20090301', 3000)

INSERT INTO #val VALUES ('20090501', '20090601', 4000)

GO

CREATE TABLE #val

([START] datetime,

[END] datetime,

[VALUE] int)

INSERT INTO #val VALUES ('20090101', '20090301', 3000)

INSERT INTO #val VALUES ('20090501', '20090601', 4000)

GO

WITH [loops] (EveryFirstDayOfYear) AS

(SELECT CONVERT(datetime, '20090101', 112) AS EveryFirstDayOfYear

UNION ALL

SELECT DATEADD(m, 1, EveryFirstDayOfYear) AS EveryFirstDayOfYear

FROM [loops]

WHERE EveryFirstDayOfYear < CONVERT(datetime, '20091231', 112))

SELECT EveryFirstDayOfYear

,[Value] / (1 + DATEDIFF(m, [Start], [End]))

FROM #val

INNER JOIN [loops]

ON [loops].EveryFirstDayOfYear

BETWEEN #val.[Start] AND #val.[End]

GO

DROP TABLE #val

EveryFirstDayOfYear

----------------------- -----------

2009-01-01 00:00:00.000 1000

2009-02-01 00:00:00.000 1000

2009-03-01 00:00:00.000 1000

2009-05-01 00:00:00.000 2000

2009-06-01 00:00:00.000 2000

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
Donnerstag, 29. Oktober 2009 12:16