James Delaney wrote:
> On a day when no items arrive no records are created, and this creates a gap in the results. I
> need the date to be included and to reflect a value of 0 for that missing day.
>
> At the moment the code I am using is this:
>
> SELECT
> DATE_FORMAT(items.DateArrived, '%d/%m') AS Arrived,
> COUNT(items.DateArrived) AS TotalArrived
> FROM `returns`
> WHERE
> (items.DateArrived <= '2005-07-25') AND
> (items.DateArrived >= '2005-07-05')
Are you confusing the `returns` table with the `items` table? You cannot refer to items.DateArrived when the `items` table is not in the FROM or a JOIN clause...
Anyway, you cannot show results that are not in the database. One way to solve your problem is to create a helper table (let's call it `ints`):
CREATE TABLE ints ( id INT NOT NULL PRIMARY KEY);
and fill it with integers starting from 0:
INSERT INTO ints VALUES (0), (1), (2), ... (N);
where N is at least the number of days between the largest interval you want to report on.
Then you can use:
SELECT
DATE_FORMAT('2005-07-05' + INTERVAL ints.id DAY, '%d/%m') AS Arrived,
COUNT(returns.DateArrived) AS TotalArrived
FROM ints
LEFT JOIN returns ON
returns.DateArrived = '2005-07-05' + INTERVAL ints.id DAY
WHERE
ints.id <= TO_DAYS('2005-07-25') - TO_DAYS('2005-07-05')
GROUP BY ints.id
ORDER BY ints.id ASC;
--
felix
Please use
BBCode to format your messages in this forum.