Add empty date row to query result

Question

There are 6 kind of ticket types. User will enter starting date and ending date. Then I have to show how many each type of tickets are on each days between those 2 date.

ticket table: id (int), type_id (int), created_at (date), and more fields

Input: date_begin, date_end
Output: rows > dates. columns > date, total_amount, and each types

My current query:

SELECT
    DATE(ticket.created_date) as date,
    SUM(IF(ticket.created_date, 1, 0)) as total,
    SUM(IF(ticket.type_id is Null, 1, 0)) as total0,
    SUM(IF(ticket.type_id = 1, 1, 0)) as total1,
    SUM(IF(ticket.type_id = 2, 1, 0)) as total2,
    SUM(IF(ticket.type_id = 3, 1, 0)) as total3,
    SUM(IF(ticket.type_id = 4, 1, 0)) as total4,
    SUM(IF(ticket.type_id = 5, 1, 0)) as total5
FROM
    ticket
JOIN 
    ticket_type ON ticket_type.id = ticket.type_id
WHERE
    DATE(ticket.created_date) BETWEEN '2015-06-12' AND '2015-06-22'
GROUP BY
    DATE(ticket.created_date)

Current output:

enter image description here

Desired output:
enter image description here

As you see today is 2015-06-19. So my database currently have data only until right now (today). But if user's end_date is in future, i want get rows until that day, and datas will be 0.

Handle issues of data display (including missing dates) at the application level (e.g. a bit of PHP) — Strawberry, Jun 19 '15 at 06:44
The problem you have there is that you are recovering all the tickets from the table, and after that, grouping them by date. If there are no ticket on a determinate date, you can't group anything. You can have the same problem between days. You can have a summary for the days: 31/01/2015 and 02/02/2015, but not for 01/02/2015. First of all try to create a select which returns you all days between 2 dates (you can use a function for this), after that, do a "left join" between the result of this select and your ticket table. — Rumpelstinsk, Jun 19 '15 at 06:52
If I create date from table itself, problem will be persistent. For example, there is no ticked on 2015-06-01. So my result skipping that day too. If I collect days on application level, say you created list with 7 days, you have to run 7 queries. I need more complex query only solution. — Gereltod, Jun 19 '15 at 06:56
What I tried to say is that you need to create the function described on this post: http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function After that run this query: SELECT ..... FROM dbo.ExplodeDates('20090401','20090531') as d LEFT JOIN ticket t on d.date = t.date GROUP BY.... If this solution is not ok for you, i can't imagine how to achive it directly from a query, sorry :( — Rumpelstinsk, Jun 19 '15 at 07:03
I forget to specify that you will need to get the date from "d.date", not from "t.date" on the select statement.Otherwiser you will get the same result. — Rumpelstinsk, Jun 19 '15 at 07:09
@Gereltod you must define this function. You have an example of definition on my previous link. However, review my extended answer. That code works fine on SqlServer 2008. — Rumpelstinsk, Jun 19 '15 at 09:56

score 1 · Accepted Answer · answered Jun 19 '15 at 09:54

First of all you must create the funcion "explodeDates". You have an example of definition on the link on my previous coment. However, if you are using SqlServer, you can use this code:

CREATE FUNCTION explodeDates(@firstDate DATE, @secondDate DATE)
RETURNS 
@mytable TABLE 
(
    mydate DATE
)
AS
BEGIN
    WHILE @firstDate < @secondDate 
    BEGIN
        INSERT INTO @mytable(mydate) VALUES (@firstDate);

        SET @firstDate = DATEADD(day, 1, @firstdate);
    END

    RETURN 
END
GO

When you had defined that function, you can call it like this:

SELECT * FROM dbo.explodeDates('01/01/2015', '31/01/2015')

After that you, only have to define your initial query like this:

SELECT
    d.mydate as date,
    SUM(IF(ticket.created_date, 1, 0)) as total,
    SUM(IF(ticket.type_id is Null, 1, 0)) as total0,
    SUM(IF(ticket.type_id = 1, 1, 0)) as total1,
    SUM(IF(ticket.type_id = 2, 1, 0)) as total2,
    SUM(IF(ticket.type_id = 3, 1, 0)) as total3,
    SUM(IF(ticket.type_id = 4, 1, 0)) as total4,
    SUM(IF(ticket.type_id = 5, 1, 0)) as total5
FROM
    dbo.explodeDates('12/06/2015', '22/06/2015') d
    LEFT JOIN ticket t ON t.created_date = d.mydate
    LEFT JOIN ticket_type ON ticket_type.id = ticket.type_id

GROUP BY
    d.mydate

score 0 · Answer 2 · answered Jun 19 '15 at 06:55

0

Firstly, This has to be validated at the fronted. Also, if ever such a query comes, I think you output is valid. How can you show tickets of future date (Which are not created). I assume these are not movie tickets.. :P

answered Jun 19 '15 at 06:55

unbesiegbar

471
2
7
19

Add empty date row to query result

2 Answers2