I have the following table and i want to split the date range into multiple rows if the start date and the end date have a difference greater than 1 month and depending on how many months were in that period split the "net price" in that many pieces.

The Existing table looks like this:

And the outcome table should look like this:

I have tried it using the solution on this link but I can't get it to work. Even in it's "purest" copy pasted form it just sets AbDat and BisDat to the same Date and doesn't create new rows. I would greatly appriciate any form of help on splitting the price and creating new rows.

CodePudding user response：

First, create a small query and save it as Ten:

SELECT DISTINCT Abs([id] Mod 10) AS N FROM MSysObjects;

Then use this in a Cartesian (multiplying) query to generate the months:

SELECT 
    Betrag.MessNr, 
    Betrag.RechnNr, 
    DateAdd("m", [N], [AbDat]) AS AbDatum, 
    DateAdd("d", -1, DateAdd("m", [N]   1, [AbDat])) AS BisDatum, 
    [NettBetr] / (1   DateDiff("m", [AbDat], [BisDat])) AS NettBetrag
FROM 
    Betrag, 
    Ten
WHERE
    Ten.N <= DateDiff("m", [AbDat], [BisDat])
ORDER BY 
    Betrag.MessNr, 
    Betrag.RechnNr, 
    DateAdd("m", [N], [AbDat]);

Output: