Splitting Data records into financial Quarters

[Dizzzy44]

Hi all

I've been searching how to do this with no luck for a couple of days now.

What I have is a set table with a set of records that have a start Date, End Date and a value.

The date ranges (between Start Date and End Date) for these data records sometimes are split across two or more financial quarters. What I am trying to achieve is split all data records in the table the roll over multiple financial quarters. Any idea on how to do this would be much appreciated.

For example, I have a table. The table has 3 fields with Start Date, End Date and Value. The records look like this:

eg:
Start Date End Date Value
1/1/2008 23/2/2008 100
1/2/2008 20/4/2008 200
4/2/2008 31/12/2008 400


In this case what I need to do VBA is split these records accordingly:
eg:
Start Date End Date Value
1/1/2008 23/2/2008 100 'no split required

1/2/2008 31/3/2008 149.4 'These records split accordingly
1/4/2008 20/4/2008 50.6

4/2/2008 30/6/2008 178.3 'These records split accordingly
1/7/2008 30/9/2008 110.8
1/10/2008 31/12/2008 110.8

Any help would be much appreciated.

 

[namliam]

I would probably make a table that contains the quarters:

StartQ		EndQ
01-Jan-08	31-Mar-08
01-Apr-08	30-Jun-08
01-Jul-08	30-Sep-08
01-Oct-08	31-Dec-08

Then do something like this (aircode) sql:

SELECT StartDate
     , EndDate
     , Value
     , Value/(EndDate-StartDate+1) AS PerDayValue
     , Value/(EndDate-StartDate+1)*(IIf(EndDate<endq,EndDate,endq)
                                   -IIf(StartDate>startq,StartDate,startq)+1) AS PerQ
     , StartQ
     , EndQ
FROM tblYourTable, tblQuarters
WHERE StartQ<=EndDate
  AND EndQ>=StartDate

I hope that can get you started.

 

[Dizzzy44]

Thanks namliam.

I have still been unable crack this one.
The quarter are not static for one particular year. I am think that I will need to treat this as a variable.

Anyone have any ideas

Cheers

 

[namliam]

I realize your quarters are not particular to any year but you need someplace to start...
Either you fix quarters and multiply for each year or 'simply' extend above quarter table to include 2000 through 2020 or however long you need it to be.

 

[Dizzzy44]

No worries. I think I have solved it.

i ended up creating a second table that mimicked the first. I then opened two recordsets, one for each table. Using the DatePart() Function I was open to identify whether the dates were in the same quarter and if not split the data using the DateDiff function. I got past the issue with the varying years by by referring to the year in either that start date or end date (which ever was applicable). All records are then written to the secondary table using a Do Loop untill the end of the input recordset.

Code works well and fast.

Cheers

 

[namliam]

Messy though working... Good job I guess, though in my oppinion double storing data is a bad thing and this should be resolved in a query the way I described it...

The pain of your code is it needs to do a full history run each time to make sure any changes are reflected in your second table, while my solution will always be up-to-date.

Just for the kick of it to see if it would really work, I made attached Q&D sample

 

[Dizzzy44]

Thanks heaps for that. I'll try and work with your solution and see if I can come up with something better than what I already have.

I'm not sure if I'm going about what I'm trying to achieve the right way in any case. I'm no expert and generally i just hack at it until it does what I need it to do.

I'm actually importing the data from a excel spreadsheet which is used as a data collection template. This is then being appended to a master table in the database and the imported table and secondary table deleted once this is done. The data isn't being stored twice I am just trying to manipulate it to the point where I can append it to the master table.

 

[namliam]

if your 'just adding it' to the target table, thats much less of an issue offcourse... Though IMHO this query setup is much easier than any code, though I am a stickler for query solutions

And off course it is possible that I am sslightly biast