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]
