0

Hello,

I have a bit of challenge that I’m wondering if I can solve by SQL.

My source table (thinned out) holds the following two columns:
TASK_NAME [VarChar], DUE_DATE [Date]

My end state is a list (will be a JQM list element) that will list out every task under headings titled as ‘Overdue’, ‘Due Today’, ‘Due Tomorrow’, ‘Due [Monday/Tuesday/Wednesday/Thursday/Friday/etc w/ DUE_DATE appended]’

Example: Heading TASK_NM

Overdue:
Fix sink
Due Today:
Fix Roof
Due Tomorrow:
Fix Light
**Due Thursday, July 5th, 2012: **
Fix Ceiling

Any suggestions on the best way to go about this? Can a mysql query alone handle this? I could always create a separate SQL select for each item and load them into a separate JSON piece that JQM could pick up. The problem is how to know which SELECT statement is "assigned" to which Heading in the list. (For instance, I could have an OverDue List, a Due Tomorrow List, etc and just manipulate the UI to look like they all flow together). Would more scripting need to be involved like javascript or some kind of ETL design?

Any help would be greatly appreciated!

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by smantscheff
0

You can group all rows by their due date and concatenate the task names using the group_concat function. Along those lines:

select (if due_date < now(), 'overdue', if( due_date < date_add(now(), interval 1 day), 'today', due_date)) as "Due date", group_concat(taskName) as "Tasks" from MyTaskTable group by "Due date";

This is of course untested code.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.