Skip to content
Advertisement

Calculate read pages through select query in reading progress table

I have a small program that I use to track my progress in reading books and stuff like goodreads to know how much I read per day.
I created two tables for that, tbl_materials(material_id int, name varchar), tbl_progress(date_of_update timestamp, material_id int foreign key, read_pages int, skipped bit).
Whenever I read some pages I insert into tbl_progress the current page that I’ve finished
I may read in the book multiple times. And if I skipped some pages I insert them into tbl_progress and mark the bit skipped to true. The problem is I can’t query the tbl_progress to know how much I read per day

What I have tried is to find the last inserted progress for every single material in every single day so for example: +-------------+------------+---------+---------------------+ | material_id | read_pages | skipped | last_update | +-------------+------------+---------+---------------------+ | 4 | 1 | | 2017-09-22 00:56:02 | | 3 | 1 | | 2017-09-22 00:56:14 | | 12 | 1 | | 2017-09-24 20:13:01 | | 4 | 30 | | 2017-09-25 01:56:38 | | 4 | 34 | | 2017-09-25 02:19:47 | | 54 | 1 | | 2017-09-29 04:22:11 | | 59 | 9 | | 2017-10-14 15:25:14 | | 4 | 68 | T | 2017-10-18 02:33:04 | | 4 | 72 | | 2017-10-18 03:50:51 | | 2 | 3 | | 2017-10-18 15:02:46 | | 2 | 5 | | 2017-10-18 15:10:46 | | 4 | 82 | | 2017-10-18 16:18:03 | | 4 | 84 | | 2017-10-20 18:06:40 | | 4 | 87 | | 2017-10-20 19:11:07 | | 4 | 103 | T | 2017-10-21 19:50:29 | | 4 | 104 | | 2017-10-22 19:56:14 | | 4 | 108 | | 2017-10-22 20:08:08 | | 2 | 6 | | 2017-10-23 00:35:45 | | 4 | 111 | | 2017-10-23 02:29:32 | | 4 | 115 | | 2017-10-23 03:06:15 | +-------------+------------+---------+---------------------+ I calculate my total read pages per day = last read page in this day – last read page in a date prior to this date and this works but the problem is I can’t avoid skipped pages.
the first row in 2017-09-22 I read 1 page then another 1 page so the total read in this day = 2 (for only material_id = 4)
in 2017-09-25 the last update for material_id 4 is 34 pages which means I read 34-1 = 33 pages (last update in this day 34 – last update prior to this date 1) = 33
till now every thing works well but when it comes to considering skipped pages I could’t do it for example:
in 2017-10-18 the last number of read pages for material_id = 4 was 34 (in 2017-09-25) then I skipped 34 pages and now the current page is 68 then read 4 pages (2017-10-18 03:50:51 ) then another 10 pages (2017-10-18 16:18:03) so the total for material_id = 4 is 14

I created a view to select the most recent last_update for every book in every day

so if there are many updates for single book in one day, this view retrieves the last one (with the max of last_update) which accompany the biggest number of read pages and so for every single book and another view to get the total read pages every day:

but the problem is that the last view calculates skipped pages.
expected result:

Advertisement

Answer

You can make a view the uses the same columns of table progresses + another derived column which uses the same idea as @Arth suggested (pages_completed column)
This column will contain the current completed_pages – completed_pages with last update prior to the first completed pages which is the difference.
So for example if your progress table like this:

we will add another derived column called diff.
where diff read_pages in 2017-10-18 02:33:04read_pages directly prior to 2017-10-18 02:33:04

note: that 68 - null is null but I put it 0 for clarification
The derived column here is the difference between this read_pages – read_pages directly before this read_pages.
Here is a view

Then you can select the sum of this derived column per day:

Which will result in something like this:

Note that the last row is from my mind lol

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement