Geek Answers Handbook

MySQL: using dates in between conditions for results

I have an SQL statement in which I do this

... group by date having date between '2010-07-01' and '2010-07-10';

The result looks like this:

sum(test)        day
--------------------
20        2010-07-03
120       2010-07-07
33        2010-07-09
42        2010-07-10

So, I have these results, but is it possible that I can write an instruction that returns me every day in the “between” condition of a result string of this type:

sum(test)        day
--------------------
0         2010-07-01
0         2010-07-02
20        2010-07-03
0         2010-07-04
0         2010-07-05
0         2010-07-06
120       2010-07-07
...       ...
42        2010-07-10

Otherwise, if this is not possible, I must do this in my program logic.

Many thanks in advance and best regards.

Update: it might be better if I show you the full SQL statement:

select COALESCE(sum(DUR), 0) AS "r", 0 AS "opt", DATE_FORMAT(date, '%d.%m.%Y') AS "day" from (
    select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, 
        a_au.re as RE, a_au.stat as STAT from b_c
        join c on b_c.c_id = c.id
        join a on c.id = a.c_id
        join a_au on a.id = a_au.id
        join revi on a_au.re = revi.re
        join (
            select a.id as ID, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, 
            max(a_au.re) as MAX_RE from b_c
                join c on b_c.c_id = c.id
                join a on c.id = a.c_id
                join a_au on a.id = a_au.id
                join revi on a_au.re = revi.re
                where b_c.b_id = 30 group by ID, date) x on
                x.id = a.id and x.date = date and x.MAX_RE = a_au.rev
                where a_au.stat != 7
            group by ID, x.date)
         AS SubSelTable where date between '2010-07-01' and '2010-07-15' group by date;

Update: My new SQL statement (-> Dave Rix):

select coalesce(`theData`.`real`, 0) as 'real', 0 as 'opt', DATE_FORMAT(`DT`.`ddDate`, '%d.%m.%Y') as 'date'
    from `dimdates` as DT 
    left join (
        select coalesce(sum(DUR), 0) AS 'real', 0 AS 'opt', date 
            from (
                select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, a_au.RE as RE, a_au.stat as STAT 
                    from b_c 
                        join c on b_c.c_id = c.id 
                        join a on c.id = a.c_id 
                        join a_au on a.id = a_au.id 
                        join revi on a_au.RE = revi.RE 
                        join ( 
                            select a.id as ID, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, max(a_au.RE) as MAX_RE 
                                from b_c 
                                join c on b_c.c_id = c.id 
                                join a on c.id = a.c_id 
                                join a_au on a.id = a_au.id 
                                join revi on a_au.RE = revi.RE 
                            where b_c.b_id = 30 GROUP BY ID, date
                        ) x 
                    on x.id = a.id and x.date = date and x.MAX_RE = a_au.RE 
                    where a_au.stat != 20 
                    group by ID, x.date
            ) AS SubTable 
        where date between '2010-07-01' and '2010-07-10' group by date) AS theData 
    ON `DT`.`ddDate` = `theData`.`date` where `DT`.`ddDate` between '2010-07-01' and '2010-07-15';
+2
source share
3 answers

See my answer to the following question:

, 0

, :)

( , !);


SELECT COALESCE(`theData`.`opt`, 0), `DT`.`myDate`
FROM `dateTable` AS DT
LEFT JOIN (
        ... INSERT YOUR QUERY HERE ...
) AS theData
ON `DT`.`myDate` = `theData`.`date`

DATE_FORMAT(date, '%d.%m.%Y') AS "day" date .

select COALESCE(sum(DUR), 0) AS "r", 0 AS "opt", `date` from

@OMG Ponies, dateTable !

- , SQL- , ? , ...

+1

​​ Where

Select Sum(day), day
From Table
Where day Between date1 and date2
Group By day

EDIT: ... ..

Having Sum(day) > 10
+1

, - DATETIME, -, , . IE:

  SELECT SUM(t.test),
         DATE_FORMAT(t.date, '%Y-%m-%d') AS day
    FROM TABLE t
   WHERE t.date BETWEEN @start AND @end
GROUP BY DATE_FORMAT(t.date, '%Y-%m-%d')

, . , .

, , MySQL . , :

DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

... :

INSERT INTO numbers (id) VALUES (NULL)

... DATE_ADD function:

  SELECT COALESCE(SUM(t.test), 0),
         x.the_date AS day
     FROM (SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL n.id-1 DAY), '%Y-%m-%d') AS the_date
             FROM NUMBERS n) x
LEFT JOIN your_table yt ON DATE_FORMAT(yt.date, '%Y-%m-%d') = x.the_date
    WHERE x.the_date BETWEEN @start AND @end
 GROUP BY x.the_date
+1

More articles:


All Articles