MySQL: Generate decreasing daily work_hours at a constant pace

Asked May 12 '17 at 15:41

Active May 12 '17 at 16:00

Viewed 26 times

I have this query to extract total_hours, start_date and end_date:

select proj.start_date, proj.end_date, sum(ifnull(work.hours_estimate,0)) as total_hours
from project_table proj
left outer join project_task work on
    work.project_id = proj.id
where proj.id = 3

This query gives me a single row of result:

 start_date |   end_date    | total_hour
----------------------------------------
2017-04-24  |   2017-05-15  |   119

What I want is to generate a daily interval of rows, constantly decreasing the total_hours by a certain amount, say 19 hours, and the day increasing by 1 day.

Expected results:

    day     |  hours_left
------------------------
2017-04-24  |  119
2017-04-25  |  100
2017-04-26  |  81
2017-04-27  |  62
2017-04-28  |  43
2017-04-29  |  24

... and so on and so forth until it reaches 2017-05-15 (of course, no negative for hours_left, just zero if negative)

can't seem to figure out how to do this.

QUESTIONS:

1.) Is this possible in MySQL?

2.) If this is possible in MySQL, is it efficient/convinient?

If not, I could just do it in application, as state in the comments

edited Jun 20 '20 at 09:12

Community

asked May 12 '17 at 15:41

Geoman Yabes

2,119
2
16
38

2

Consider handling such issues in application code. – Strawberry May 12 '17 at 15:46
so this is not posible in mysql? ill edit my question then. – Geoman Yabes May 12 '17 at 15:47
3

All things are possible. Some things are sensible. – Strawberry May 12 '17 at 15:47
well, you got me there. post your comments as answer, ill accept. – Geoman Yabes May 12 '17 at 15:49
1

This is a lot more easier to achieve in the application layer. SQL language in general is not really great at creating new records on the fly. It can be done, but it will be ugly. – Shadow May 12 '17 at 15:50
dudes, your comments are good enough to be answers. – Geoman Yabes May 12 '17 at 15:52
Just to show you that we know what we are talking about, I will provide already existing SO answers to the two fundamental parts of your question: 1) fill gaps in a date range 2) cumulative sum (in this case, cumulative minus) You need to combine the two in order to get what you are looking for. – Shadow May 12 '17 at 15:55
filling gaps: http://stackoverflow.com/a/31141256/5389997 – Shadow May 12 '17 at 15:56
cumulative sum: http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql – Shadow May 12 '17 at 15:57

MySQL: Generate decreasing daily work_hours at a constant pace

0 Answers0