Member Avatar for RachelR

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
  • 187 Views
  • 49 Minutes Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.