Link to home
Start Free TrialLog in
Avatar of dkMTASustainability

asked on

splitting a time period into calendar months--mysql

I am basically facing the same problem as described in this thread: The difference is that I need to find a solution with MYSQL.

I am dealing with a Electricity Usage table:

ID         Account       FromDate         ToDate              ElecUse      ElecDemand    ElecBillAmt
2903    100009        2010-10-14       2010-11-12        352400      668.1              12592.53
2904    100009        2010-11-12       2010-12-15        426400      666                 14284.39
2905    100009        2010-12-15       2010-01-14        406800      708.4              13812.54
2906    100009        2010-01-14       2010-02-15        443200      697.9              14514.99

I would like to report the usage on a monthly basis beginning with the start of each month and ending with the end of each month. Thus, I would like to have a MYSQL query that can produce the following table from the above:

 Account      FromDate        ToDate             ElecUse       ElecDemand        ElecBillAmt
100009       2010-10-14      2010-10-31       206579       391.51                 7381.74
100009       2010-11-01      2010-11-30       378402       639.24                 13002.12
100009       2010-12-01      2010-12-31       410778       680.46                 13859.46
100009       2011-01-01      2011-01-31       425290       701.14                 14156.77
100009       2011-02-01      2011-02-15       207750       327                      6803.85

I appreciate the help,
Avatar of johanntagle
Flag of Philippines image

I don't see any way this can be done with just a query.  You need a script to transform and process the data (i.e. "expand" a row to multiple entries depending on number of months) then either output the report directly or to save the transformed data to another table to be queried later.

I suggest you click on "Request Attention" to have the zone of the programming language you are most comfortable with be added.
Avatar of dkMTASustainability


If we can accomplish the same result using a VBScript, that would be the 2nd best option.  Perhaps I could maintain 2 tables then - 1) my Master Electricity Usage Table (as is) that would contain all of the original data that I collect from the vendors, and 2) a table that is the result of the VBScript, having calculated the rates at monthly intervals from the Master Electricity Usage Table.

Ultimately, I will be querying the database using mySQL and php, and parsing the result into JSON to display the result as a google chart. An example query in php / mysql would return multiple account numbers from the database. While processing the data in php before it is displayed in the chart is possible, it is not the preferred solution.
Yes if this will be for a web-based reporting tool it is best to pre-compute then store the results for future use.  This way you don't do heavy computations whenever there is a request and can quickly output a report/chart to the user.
I was able to get a partial response to the problem using mysql from the development community:

Are you sure there's not a complete solution to the problem in mysql?
I figured out the solution. Find attached for your reference. Hope this helps folks that were in my shoes.

Don't think you were able to attach it properly (many people encounter this because the attachment process in E-E is not intuitive - it takes 2 steps).

Yes I'd be very curious on what solution you came up with.  Thanks.
Avatar of dkMTASustainability

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yup - just what I thought it would be a multi-step approach and not "just a query" =).  I have a feeling though that doing this via a scripting language would be simpler and faster.  But if what you have works for you, so be it =)