Pivot query to display data for each day of the year

Question

I have one database table as following:

id|start_date|end_date|sec
1|2018-08-01|2018-08-03|2500
2|2018-08-02|2018-08-13|100
3|2018-08-01|2018-08-05|500

So I want to display the report date wise so that user can know how many seconds available in specific days.

For example, I need below things:

Date /Day            total
2018-08-01  (2500+500)=3000     //this date comes in 1&3 records
2018-08-02  (2500+100+500)=3100 //this date comes in all 1,2,&3 records
2018-08-03  (2500+100+500)=3100 //this date comes in all 1,2,&3 records
2018-08-04  (100+500)=600       //this date comes in 2&3 records
2018-08-05  (100+500)=600       //this date comes in 2&3 records
2018-08-06  (100)=100           //this date comes in 3  records

I am trying to use mysql and php but I don't know how to do.

What are the "specific days" exactly ? Are they also fetched from DB ? Or are they days from current month ? Or something else ? — Aurelien, Aug 08 '18 at 15:25

Aurelien · Accepted Answer · 2018-08-09T07:32:07.300

1

There is no clean way to generate a table with each day of current year on the fly, I took the subtable code from generate days from date range (to give him credit) because it didn't use any loops etc and had a short execution time. That being said, you can go with :

SELECT
dpy.day as Day,
SUM(IFNULL(t.sec,0)) as total
FROM
(
select a.Date as day
from (
    select DATE_FORMAT(CURRENT_DATE(), '%Y-12-31') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') and DATE_FORMAT(CURRENT_DATE(), '%Y-12-31')
) dayPerYear dpy
LEFT JOIN
your_table t ON dpy.day > t.start_date AND dpy.day < t.end_date
GROUP BY dpy.day

Just replace your_table by your table name

edited Aug 09 '18 at 07:32

answered Aug 08 '18 at 15:39

Aurelien

1,497
7
15

What do you mean by 'clean'? This seems fairly straightforward !?! Although, given that the OP mentions PHP, the above seems like it may be unnecessary. – Strawberry Aug 08 '18 at 15:50
Don't get me wrong, I just meant visually. I understood OP's post as he was trying to use php to do so because he lacked a pure sql solution. I might be wrong though. – Aurelien Aug 08 '18 at 15:52
@Aurelien thanks for your answer! It's only giving results upto 2018-08-09 date not the end of the day i.e. 2018-12-31 – Pratik Aug 09 '18 at 07:18
@Aurelien I think it's get only upto today's date. I need the day for whole year. – Pratik Aug 09 '18 at 07:25
@Pratik Yes, sorry, there was something left in the subtable code that I didn't adapt. I fixed it in my answer. – Aurelien Aug 09 '18 at 07:32

score 0 · Answer 2 · answered Aug 08 '18 at 15:40

Lets begin by assuming that your records are already fetched and are available in an associative array named $rows (after a query and a mysqli_fetch_array type call):

$ra=array();
$nr=count($rows);    
for ($i=0; $i<$nr; $i++) {
   $start=$rows['start_date'];
   $end=$rows['end_date'];
   $sec=$rows['sec'];
   if (isset($ra[$start])) {
      $ra[$start]+=$sec;
   } else {
      $ra[$start]=$sec;
   }
   if (isset($ra[$end])) {
      $ra[$end]+=$sec;
   } else {
      $ra[$end]=$sec;
   }
}
print_r($ra);

score 0 · Answer 3 · answered Aug 08 '18 at 15:55

0

You have to loop through days

select sum(sec) from table where ('2018-08-01') between start_date and end_date ;

For generating the days With php, populate an array of days in current month

answered Aug 08 '18 at 15:55

Danesh G

121
4

Pivot query to display data for each day of the year

3 Answers3