Create monthly table from sqlite?
Posted: Sat Jul 20, 2019 1:32 pm
Let's say we have two tables in sqlite db.
The first table, "Workers", has workers with the columns: id, first_name, last_name.
The second table, "Hours", has working details: worker_id, date, working_hours.
What we need is to create a monthly view that will have the columns: first_name, last_name, 1, 2, 3, 4, 5, 6,... 30 or 31 according to month's max day number. The rows will have worker's name and working hours. Of course there will be many blank table cells. That is not a problem. I can get the data with:
With the above query we get dates in a column. Is there a way to separate days and have different column for every single day?
The first table, "Workers", has workers with the columns: id, first_name, last_name.
The second table, "Hours", has working details: worker_id, date, working_hours.
What we need is to create a monthly view that will have the columns: first_name, last_name, 1, 2, 3, 4, 5, 6,... 30 or 31 according to month's max day number. The rows will have worker's name and working hours. Of course there will be many blank table cells. That is not a problem. I can get the data with:
Code: Select all
SELECT W.first_name, W.last_name, H.date, H.working_hours FROM Workers W INNER JOIN Hours H WHERE W.id = H.id AND H.date >= '2019-09-01' AND H.date <='2019-09-30'