2
Good morning I’m doing a db query as follows:
SELECT IFNULL(COUNT(*), 0) as numero, DAY(cdr.calldate) as dia, MONTH(cdr.calldate) as mes, YEAR(cdr.calldate) as ano FROM cdr LEFT JOIN peers on cdr.dst = peers.name WHERE cdr.calldate >= '2018-08-01 00:00:00' and cdr.calldate <= '2018-08-23 23:59:59' and length(src) > 5 and dcontext = 'default' and (cdr.dst like '2209' OR cdr.dstchannel like 'KHOMP/b0c9%') GROUP BY DAY(cdr.calldate)
he brings me the following result:
numero dia mes ano
4 1 8 2018
14 6 8 2018
20 7 8 2018
10 8 8 2018
19 9 8 2018
10 10 8 2018
17 13 8 2018
9 14 8 2018
13 15 8 2018
9 16 8 2018
10 17 8 2018
10 20 8 2018
16 21 8 2018
3 22 8 2018
1 23 8 2018
Notice that it does not bring a few days because there is no information these days. I already searched the internet and could not bring the days that have no results as 0 value in Count. Does anyone know how to do it? or can you tell me what function name in mysql or command returns it?
In that answer, it’s showing how to do with months. The logic is the same: https://answall.com/a/300026/69359 basically you need to have every day, and give left Join with the other tables.
– Rovann Linhalis
understood then I will generate a table with every calendar of the year or at least I will have to devise a for in php that makes a query per day.
– Jasar Orion
the interval of days you determine, I did this example: https://www.db-fiddle.com/f/jmTZtkNZqnErDZAQKhtGfU/0
– Rovann Linhalis
very legau da for me to do the query in real time. as I change this your ex to insert an incial date and another final ?
– Jasar Orion
then you need to calculate the difference of days between the two dates, change the limit for that difference, and put the initial date where you are now in the example. I’m busy at the moment but soon, if no one did, I’ll give you an answer
– Rovann Linhalis
legau but vo tries Aki at least you already told the way of the stones. thank you very much.
– Jasar Orion