Create data records (one record per month)

Question

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

Answer 1

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.

Answer 2

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