I can't suggest a complete solution but I think the answer is going to start with the week() function. See http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1403.
You're lucky; figuring out which week a date belongs to was something I had to do in DB2 a few years back. The user was on an old version of DB2 that didn't have the week() function yet and I had to do a *lot* of work to come up with a way to calculate weeks without it. That solution involved writing scripts to automatically build tables showing the week start and end dates so that he could join to them just to get week numbers. In your case, you can probably get what you need by simply selecting all the rows you want to report on, then adding a GROUP BY for the week number. That should work for the second of your reports, the one that shows actual totals for the week in question. I'm not so sure how to do cumulative totals in MySQL. It's easy enough in a spreadsheet so you should be able to think of some way to do it in MySQL. Like I said, I don't know the whole answer but I think you definitely want to start with the week() function in a GROUP BY. Be sure you look at the different modes supported by the week() function and make sure you pick the one that matches the rules in your environment. Rhino ----- Original Message ----- From: "Ramesh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, April 01, 2004 11:12 PM Subject: MySql SQL Help > > Hi, > > Need a SQL/Design help. > > I have a table as follows: > > ORDER_HISTORY > -------------- > ORDER_ID | ORDER_NAME | ORDER_AMOUNT | ORDER_DATE > -------------------------------------------------- > 10 | Order 1 | 20.00 | 10-Jan-2003 > 11 | Order 2 | 200.00 | 15-Jan-2003 > 12 | Order 3 | 250.00 | 25-Jan-2003 > 13 | Order 4 | 260.00 | 30-Mar-2003 > 14 | Order 5 | 320.00 | 10-Jan-2004 > > I want to write a SQL which generates a weekly report from the ORDER_HISTORY table and shows it as: > > WEEKLY_DATE | CUMULATIVE AMOUNT > -------------------------------------- > 10-JAN-2003 | 20 > 17-JAN-2003 | 220 > 24-JAN-2003 | 220 > 31-JAN-2003 | 490 > 07-FEB-2003 | 490 > > Similarly another table without a Cumulative: > > WEEKLY_DATE | AMOUNT > -------------------------------------- > 10-JAN-2003 | 20 > 17-JAN-2003 | 200 > 24-JAN-2003 | 0 > 31-JAN-2003 | 250 > 07-FEB-2003 | 0 > > My thought process currently is to first get all the date intervals within the min and max dates of ORDER_DATE with a 1 WEEK addition to each min date and then for each of these intervals, I can then get the AMOUNT and add it to up to the previous amount if cumulative or just leave it if not cumulative. > > I am trying to find an elegant solution to implement this and not have too many trips to the database, i.e once to get each interval of date between the min and max and then for each interval go to the db again to get the amount. > > Is there any function which will return all the starting dates of each week between two dates? > > Appreciate any help or links to prior solutions or any book on this topic. > > thanks > > > > _______________________________________________ > Join Excite! - http://www.excite.com > The most personalized portal on the Web! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
