Variable column SQL statments
-
SO I have two tables - one of "tasks" and another to log the time they are carried out each day - viz:
table "tasks"
ID - autoincrement PK
sJob - varchar task nametable "tasks_log"
ID - autoincrement PK
JobID - mapped to tasks.ID
dDateTime - date and time task is performedNow, what I want to do is produce in one SQL statement a result set in which each row consists of the task (sJob), followed by the time it was carried out on each day within a given range So... the result set will have variable columns depending on the date range, and the SQL sdtatement will need to be built dynamically. A (probably not the best!) partial solution is as follows Suppose we want the results for three days, Dec 1 to Dec 3 SELECT T.sJob, L1.dDateTime AS Dec_1, L2.dDateTime AS Dec_2, L2.dDateTime AS Dec_3 FROM tasks AS T LEFT JOIN tasks_log AS L1 ON L1.JobID = T.ID LEFT JOIN tasks_log AS L2 ON L2.JobID = T.ID LEFT JOIN tasks_log AS L3 ON L3.JobID = T.ID WHERE DATE(L1.dDate) = '2013-12-01' AND DATE(L2.dDate) = '2013-12-02' AND DATE(L3.dDate) = '2013-12-03' ORDER BY T.sJob One obvious drawback of this is that it will be limited to a relatively small date range, but worse is that there is something I haven't told you! :) That is, that not all the tasks are carried out on all days. Given this, the result set will only consist of those tasks that were, in fact, carried out on each day within the given date range. What we need, if it's not asking the impossible, is to list them all, with NULLs showing on days any particular task was not performed. Obviously there are other approaches, but it wuild be nice if there was simple (...) SQL statement that could do it all in one go... :)
-
SO I have two tables - one of "tasks" and another to log the time they are carried out each day - viz:
table "tasks"
ID - autoincrement PK
sJob - varchar task nametable "tasks_log"
ID - autoincrement PK
JobID - mapped to tasks.ID
dDateTime - date and time task is performedNow, what I want to do is produce in one SQL statement a result set in which each row consists of the task (sJob), followed by the time it was carried out on each day within a given range So... the result set will have variable columns depending on the date range, and the SQL sdtatement will need to be built dynamically. A (probably not the best!) partial solution is as follows Suppose we want the results for three days, Dec 1 to Dec 3 SELECT T.sJob, L1.dDateTime AS Dec_1, L2.dDateTime AS Dec_2, L2.dDateTime AS Dec_3 FROM tasks AS T LEFT JOIN tasks_log AS L1 ON L1.JobID = T.ID LEFT JOIN tasks_log AS L2 ON L2.JobID = T.ID LEFT JOIN tasks_log AS L3 ON L3.JobID = T.ID WHERE DATE(L1.dDate) = '2013-12-01' AND DATE(L2.dDate) = '2013-12-02' AND DATE(L3.dDate) = '2013-12-03' ORDER BY T.sJob One obvious drawback of this is that it will be limited to a relatively small date range, but worse is that there is something I haven't told you! :) That is, that not all the tasks are carried out on all days. Given this, the result set will only consist of those tasks that were, in fact, carried out on each day within the given date range. What we need, if it's not asking the impossible, is to list them all, with NULLs showing on days any particular task was not performed. Obviously there are other approaches, but it wuild be nice if there was simple (...) SQL statement that could do it all in one go... :)
What you need is a pivot query. This article [^]may help. The ides is to prepare your data before pivoting. Create a table with the dates you want to report, and in your pivot join those date to the data table and then pivot the result on the dates.
Never underestimate the power of human stupidity RAH
-
What you need is a pivot query. This article [^]may help. The ides is to prepare your data before pivoting. Create a table with the dates you want to report, and in your pivot join those date to the data table and then pivot the result on the dates.
Never underestimate the power of human stupidity RAH
Thanks!