Hello all. I have a need for assistance re: mySQL. I am using coldfusion to create some reports but there is just one that is escaping me at this time.
I have a table with dateofpayment and paymentamount for ALL customers. There MAY be multiple paymentamounts for each dateofpayment. My web page lets the enduser select any date from a calendar date picker. My week is from Wednesday through Tuesday rather than Sunday through Monday. I want the following table based upon the date from the calendar date picker: ---- Total Payments Weds Thurs Fri Sat Sun Mon Tues $85 $22 $100 $11 $55 $98 $66 If date from date picker is Weds, then the report will show values ONLY for Weds If date from date picker is Thurs, then the report will show values for Both Weds and Thurs If date from date picker is Fri, then the report will show values for Weds, Thurs, and Fri If date from date picker is Sat, then the report will show values for Weds, Thurs, Fri, and Sat If date from date picker is Sun, then the report will show values for Weds, Turs, Fri, Sat and Sun If date from date picker is Mon, then the report will show values for Weds, Turs, Fri, Sat Sun, and Mon If date from date picker is Tues, then the report will show values for Weds, Turs, Fri, Sat, Sun, Mon, and Tues So, I started in my head with this pseudo-code to get the past 7 days even though it isn't quite what i need. But it was a start. --- SELECT paymentdate,paymentamount,sum(paymentamount) AS pay FROM mytable WHERE paymentdate BETWEEN datefrompicker AND ???? GROUP BY paymentdate So how do I complete my broken thought process? Thank you in advance and God Bless ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2591 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
