CODEWITHSUNDEEP
sqlgaps-and-islands
zz1433
zz1433

Reputation: 3586

SQL Date rows for inactivity days

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.

Upvotes: 1

Views: 420

Answers (4)

Steve K
Steve K

Reputation: 19586

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

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

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

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 51000

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838876

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.

Upvotes: 1

Related Questions