SQL Date rows for inactivity days

Question

Let's say there is this table which stores the number of visitors for each day.

When I want to query the table and create a graph from it a problem arises.

The days without activity have no corresponding rows on the table.

For example

Day1 - 7
Day2 - 8
Day4 - 7

And the graph generated would not be correct. Since it needs a 0 for Day3.

Now, without using anything other than SQL is it possible to create those values for the inactivity days?

I thought of creating another table which would create all the dates for the 30days to come each time the scripts gets executed and the problem would have been fixed, but I'm wondering if there is a more practical solution.

Thanks in advance.

possible duplicate of http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-either - this question has been asked a lot on SO. Generally the answer is to join against a temporary table containing all your dates, or handle the missing values in your application logic. — zombat, Apr 22 '10 at 18:24
Another question that is similar to this one: http://stackoverflow.com/questions/2670183/displaying-zero-valued-months-with-sql — Mark Byers, Apr 22 '10 at 18:27
Thanks to everyone for their replies, actually I solved this in a different way, with a dictionary class, added all the values and then for each day checked if the value existed for that date, if not value was assumed 0. But this is not "only with sql" — zz1433, May 24 '10 at 11:32

score 1 · Accepted Answer · answered Apr 22 '10 at 18:26

Your solution of creating a table with the 30 days is a very simple and practical solution.

You can do it without an extra table if you really want to, but it's not pleasant. SQL is not really designed to allow you to select data that doesn't exist in your database. A much easier solution in general is to add the missing rows client-side rather than trying to write a complex SQL statement to do this.

score 1 · Answer 2 · answered Apr 22 '10 at 18:36

Using Sql Server 2005+ and CTE Recursive (Using Common Table Expressions) you could try

DECLARE @Table TABLE(
        DateVal DATETIME,
        Visists INT
)

INSERT INTO @Table SELECT '01 Jan 2010', 10
INSERT INTO @Table SELECT '03 Jan 2010', 1
INSERT INTO @Table SELECT '05 Jan 2010', 30
INSERT INTO @Table SELECT '10 Jan 2010', 50

;WITH MinMax AS (
        SELECT  MIN(DateVal) Startdate,
                MAX(DateVal) EndDate
        FROM    @Table
),
DateRange AS(
        SELECT  StartDate DateVal
        FROM    MinMax
        UNION ALL
        SELECT  DateRange.DateVal + 1
        FROM    DateRange,
                MinMax
        WHERE   DateRange.DateVal + 1 <= MinMax.EndDate
)
SELECT  DateRange.DateVal,
        ISNULL(t.Visists,0) TotalVisits
FROM    DateRange LEFT JOIN
        @Table t ON DateRange.DateVal = t.DateVal

With output as

DateVal                 TotalVisits
----------------------- -----------
2010-01-01 00:00:00.000 10
2010-01-02 00:00:00.000 0
2010-01-03 00:00:00.000 1
2010-01-04 00:00:00.000 0
2010-01-05 00:00:00.000 30
2010-01-06 00:00:00.000 0
2010-01-07 00:00:00.000 0
2010-01-08 00:00:00.000 0
2010-01-09 00:00:00.000 0
2010-01-10 00:00:00.000 50

score 1 · Answer 3 · answered Apr 22 '10 at 18:53

I wouldn't call this SQL only, since it uses a PostgreSQL specific function - but there may be something similar in whatever database your using.

PostgreSQL has a nice function: generate_series

You can use this function to create a series of 30 days.

select current_date + s.a as dates from generate_series(0,30) as s(a);

 dates    
------------
 2010-04-22
 2010-04-23
 2010-04-24
 (.. etc ..)

You can then use that in a query, something like:

select vpd.visits, temp.dates
  from (select current_date + s.a as dates from generate_series(0,30) as s(a)) as temp
 left outer join visits_per_day vpd on vpd.day = temp.dates

 visits |   dates    
--------+------------
     10 | 2010-04-22
        | 2010-04-23
     20 | 2010-04-24
        | 2010-04-25
        | 2010-04-26
     30 | 2010-04-27

score 0 · Answer 4 · answered Apr 22 '10 at 18:35

No, there is no standard way using only SQL to add an indeterminate number of missing rows into the result of an SQL query without first storing those rows in a table.

Either you can have a single table which contains all the dates over which your application will operate or you can have a table into which you put only the dates that your current query will use. If you choose the second solution, remember to plan for different users executing the same query with different date ranges at the same time — you'll want the table to be temporary and user-specific if your DBMS supports that.

SQL Date rows for inactivity days

4 Answers4

Linked