Need to convert single rows in multiple rows per month

Question

I have a table with a period column which needs to be split into multiple rows per month

The table has lots of records like this:

ID, PERIOD,        AMOUNT
1,  201812-201902, 300.00
2,  201804-201903, 1150.00
3,  201901-201902, 200.00

I need to following query result:

1, 12-2018, 100.00
1, 01-2019, 100.00
1, 02-2019, 100.00

2, 04-2018, 95.83
2, 05-2018, 95.83
2, 06-2018, 95.83
2, 07-2018, 95.83
2, 08-2018, 95.83
2, 09-2018, 95.83
2, 10-2018, 95.83
2, 11-2018, 95.83
2, 12-2018, 95.83
2, 01-2019, 95.83
2, 02-2019, 95.83
2, 03-2019, 95.83

3, 01-2019, 100.00
3, 02-2019, 100.00

How to achieve this in MS-Access SQL?

Answer 1

You can create this with a series of queries (that I had on hand):

Save this as Ten:

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

Save this as MonthDateRange:

PARAMETERS 
    [DateStart] DateTime, [DateEnd] DateTime;
SELECT 
    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000 AS Id, [DateStart] AS DateStart, [DateEnd] AS DateEnd, DateAdd("m",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000,[DateStart]) AS DateMonth
FROM 
    Ten AS Ten_0, Ten AS Ten_1, Ten AS Ten_2, Ten AS Ten_3, Ten AS Ten_4, Ten AS Ten_5, Ten AS Ten_6
WHERE 
    (((DateAdd("m",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000,[DateStart]))<=DateAdd("m",DateDiff("m",[DateStart],DateAdd("d",-1,[DateEnd])),[DateStart])) AND ((Ten_0.N)<=DateDiff("m",[DateStart],[DateEnd])\1) AND ((Ten_1.N)<=DateDiff("m",[DateStart],[DateEnd])\10) AND ((Ten_2.N)<=DateDiff("m",[DateStart],[DateEnd])\100) AND ((Ten_3.N)<=DateDiff("m",[DateStart],[DateEnd])\1000) AND ((Ten_4.N)<=DateDiff("m",[DateStart],[DateEnd])\10000) AND ((Ten_5.N)<=DateDiff("m",[DateStart],[DateEnd])\100000) AND ((Ten_6.N)<=DateDiff("m",[DateStart],[DateEnd])\1000000));

Then build your final query using your table Periods:

SELECT 
    Periods.Id, MonthsDateRange.DateMonth AS Period, Periods.Amount
FROM 
    Periods, MonthsDateRange
WHERE 
    (((MonthsDateRange.DateMonth) Between CDate(Format(Left([Period],6),"@@@@\/@@")) And CDate(Format(Right([Period],6),"@@@@\/@@"))))
ORDER BY 
    Periods.Id, MonthsDateRange.DateMonth;

Now, call this passing a start and end date beyond your range of periods, say 2000-01-01 and 2020-01-01, and the output will be:

To have the amount split over the periods, include a subquery:

SELECT 
    Periods.Id, MonthsDateRange.DateMonth AS Period, 
    [Amount]/(Select Count(*) From MonthsDateRange As T Where T.DateMonth Between CDate(Format(Left([Period],6),"@@@@/@@")) And CDate(Format(Right([Period],6),"@@@@/@@"))) AS AmountPeriod
FROM 
    Periods, MonthsDateRange
WHERE 
    (((MonthsDateRange.DateMonth) Between CDate(Format(Left([Period],6),"@@@@\/@@")) And CDate(Format(Right([Period],6),"@@@@\/@@"))))
ORDER BY 
    Periods.Id, MonthsDateRange.DateMonth;

to obtain this output: