Date help.

[VAMick]

No, I'm not trolling for a date. I need help with the date function.

Background:
Currently I have a spreadsheet that I keep weekly data in for a list of companies. The list is very long now and i can't not crunch the data like I would like to, so I thought to put it in a database.

What i need to do is turn the weekly data into a total for each month. But, our month for billing purposes runs from the 16th of the previous month to the 15th of the current month. (hope that makes sense).

So, I'm not sure how or if even possible it would be to code things so that I could easily get a total for say the month of May and it would look at the correct weeks.

Thanks for any assist here. I'll keep reading the documentation in the meantime to see if I can sort this out.

 

[TonyGroves]

You have a couple of options.

(0) Write a user-defined function, using C or C++, compile it, and register it with the MySQL server. Then you can say something like:[tt]
GROUP BY BILLINGMONTH(datefld)[/tt]
Of course, you need programming skills for that. And, you wouldn't normally define application-orientated functions in that way.

(1) Create a view:[tt]
CREATE VIEW billdata_plus AS
SELECT
*,
IF(
DAY(billweek)>15,
CONCAT(LEFT(billweek,8),'01'),
CONCAT(LEFT(billweek - INTERVAL 1 MONTH,8),'01'),
)
billmonth
FROM billdata
[/tt]
Your queries could then select from "billdata_plus", which contains the field "billmonth", instead of selecting from "billdata".