当前位置: 动力学知识库 > 问答 > 编程问答 >

jasper reports - MySQL Query - Include dates without records

问题描述:

I have a report that displays a graph. The X axis uses the date from the below query. Where the query returns no date, I am getting gaps and would prefer to return a value. Is there any way to force a date where there are no records?

SELECT

DATE(instime),

CASE

WHEN direction = 1 AND duration > 0 THEN 'Incoming'

WHEN direction = 2 THEN 'Outgoing'

WHEN direction = 1 AND duration = 0 THEN 'Missed'

END AS type,

COUNT(*)

FROM taxticketitem

GROUP BY

DATE(instime),

CASE

WHEN direction = 1 AND duration > 0 THEN 'Incoming'

WHEN direction = 2 THEN 'Outgoing'

WHEN direction = 1 AND duration = 0 THEN 'Missed'

END

ORDER BY DATE(instime)

网友答案:

One possible way is to create a table of dates and LEFT JOIN your table with them. The table could look something like this:

CREATE TABLE `datelist` (
 `date` DATE NOT NULL,
 PRIMARY KEY (`date`)
);

and filled with all dates between, say Jan-01-2000 through Dec-31-2050 (here is my Date Generator script).

Next, write your query like this:

SELECT datelist.date, COUNT(taxticketitem.id) AS c
FROM datelist
LEFT JOIN taxticketitem ON datelist.date = DATE(taxticketitem.instime)
WHERE datelist.date BETWEEN `2012-01-01` AND `2012-12-31`
GROUP BY datelist.date
ORDER BY datelist.date

LEFT JOIN and counting not null values from right table's ensures that the count is correct (0 if no row exists for a given date).

网友答案:

You would need to have a set of dates to LEFT JOIN your table to it. Unfortunately, MySQL lacks a way to generate it on the fly.

You would need to prepare a table with, say, 100000 consecutive integers from 0 to 99999 (or how long you think your maximum report range would be):

CREATE TABLE series (number INT NOT NULL PRIMARY KEY);

and use it like this:

SELECT  DATE(instime) AS r_date, CASE ... END AS type, COUNT(instime)
FROM    series s
LEFT JOIN
        taxticketitems ti
ON      ti.instime >= '2013-01-01' + INTERVAL number DAY
        AND ti.instime < '2013-01-01' + INTERVAL number + 1 DAY
WHERE   s.number <= DATEDIFF('2013-02-01', '2013-01-01')
GROUP BY
        r_date, type
网友答案:

Had to do something similar before.

You need to have a subselect to generate a range of dates. All the dates you want. Easiest with a start date added to a number:-

SELECT DATE_ADD(SomeStartDate, INTERVAL (a.I + b.1 * 10) DAY)
FROM integers a, integers b

Given a table called integers with a single column called i with 10 rows containing 0 to 9 that SQL will give you a range of 100 days starting at SomeStartDate

You can then left join your actual data against that to get the full range.

分享给朋友:
您可能感兴趣的文章:
随机阅读: