How does one summarise data grouped by a column and date, accounting for dates with missing data

Question

I'm creating a report where I'd like to get the number of events grouped by day and tag, filtered by specific tags (like '%sample%') and for a specific period (e.g. past week).

I'm using SQL Server 2008.

There are days however when no events with a specific tag occurred. The problem I have is how to generate rows for days for which no rows exist and give them a value of zero. Something similar to the following:

Tag                 Date            Count
=================== =============== ====================
Sample              2013-07-07      0
Sample              2013-07-08      0
Sample              2013-07-09      0
Sample              2013-07-10      0
Sample              2013-07-11      0
Sample              2013-07-12      1
Sample              2013-07-13      0
xxx Sample xxx      2013-07-07      0
xxx Sample xxx      2013-07-08      0
xxx Sample xxx      2013-07-09      0
xxx Sample xxx      2013-07-10      3
xxx Sample xxx      2013-07-11      0
xxx Sample xxx      2013-07-12      0
xxx Sample xxx      2013-07-13      0
yyy Sample yyy      2013-07-07      0
yyy Sample yyy      2013-07-08      0
yyy Sample yyy      2013-07-09      0
yyy Sample yyy      2013-07-10      1
yyy Sample yyy      2013-07-11      0
yyy Sample yyy      2013-07-12      0
yyy Sample yyy      2013-07-13      0

The zero days are important in order to render the data in graphs, where each "tag" is its own graph, where the time is the X-axis or count is the Y-axis.

Schema

The Tags table look as follows:

CREATE TABLE Tags
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](64) NOT NULL,
    CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED
    (
          [Id] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

And Events table look as follows:

CREATE TABLE [dbo].[Events](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Message] [varchar](128) NULL,
      [TagId] [int] NOT NULL,
      [CreatedAt] [datetime] NULL,
    CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
    (
      [Id] ASC
    ) ON [PRIMARY]
)

Where TagId is a foreign key to the Tags table.

Sample Data

The Events table has the following data

Id  Message     TagId   CreatedAt
=== =========== ======= =========================
1   Message 1   1       2013-07-10 18:46:04.967
2   Message 2   2       2013-07-14 18:46:10.547
3   Message 3   3       2013-07-12 18:46:15.190
4   Message 4   4       2013-07-14 18:46:20.673
5   Message 5   2       2013-07-14 18:46:28.133
8   Message 6   1       2013-07-10 14:46:04.967
9   Message 7   1       2013-07-10 12:46:04.967
10  Message 6   2       2013-07-10 14:46:04.967

And the tags table has the following data:

Id  Name
=== ===========================
3   Sample
4   Test1
5   Test2
6   Test3
1   xxx Sample xxx
2   yyy Sample yyy

What I've tried

So, I joined it with a table getting the following:

SELECT Tags.Name, CONVERT(date, Events.CreatedAt) AS Date,COUNT(*) AS Count
FROM
Events
INNER JOIN Tags ON Events.TagId = Tags.Id
where tags.Name like '%sample%'
GROUP BY Tags.Name, CONVERT(date, Events.CreatedAt)
ORDER BY Tags.Name, CONVERT(date, Events.CreatedAt)

which returned

Name                Date            Count
=================== =============== ================
Sample              2013-07-12      1
xxx Sample xxx      2013-07-10      3
yyy Sample yyy      2013-07-10      1
yyy Sample yyy      2013-07-14      2

I searched for ways to generated rows for days for which there is no data. I found an entry SQL Server: How to select all days in a date range even if no data exists for some days but was unable to get it to work.

To verify I got the right days, I ran the following query:

WITH DateTable
AS
(
    SELECT CONVERT(date, DateAdd(WEEK, -1, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < CONVERT(date, GETDATE())
)
select DateTable.DATE
FROM DateTable

Which returned:

My first attempt was to get it to work without specifying the LIKE '%sample%' in the where clause.

WITH DateTable
AS
(
    SELECT CONVERT(date, DateAdd(WEEK, -1, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < CONVERT(date, GETDATE())
)
SELECT Tags.Name, dt.[DATE] as Date, COUNT(Events.ID) as Count
FROM
      Events
      INNER JOIN Tags ON Tags.Id = Events.TagId
RIGHT JOIN [DateTable] dt ON dt.[DATE] = CONVERT(date, Events.[CreatedAt])
WHERE TagId IS NOT NULL
GROUP BY Tags.Name, dt.[DATE]

I get the following result:

Name                Date            Count
=================== =============== ================
xxx Sample xxx      2013-07-10      3
yyy Sample yyy      2013-07-10      1
Sample              2013-07-12      1

I have tried other things, like changing the RIGHT JOIN to a LEFT JOIN, but I'm unable to get the desired result.

I would say the problem with the last attempt is the inner join between Events and Tags. Your right join will return a null event record with a date from [DataTable], but then an inner join to Tags will exclude all records. If you did a right join there you would see all dates, but not all dates for all tags.... — Squid, Jul 15 '13 at 04:59
Yes. Then I had a number of `NULL` Tag Names for the dates where the event didn't occur. — bloudraak, Jul 15 '13 at 05:02
You could use a Cartesian join between events and tags, although not something I would generally recommend, it does seem to fit your requirements. So something like `from Events, Tags right join DateTable ... where Tags.Id = Events.TagId or Events.TagId = null` — Squid, Jul 15 '13 at 05:05

Praveen Prasannan · Accepted Answer · 2013-07-15T05:32:45.170

2

WITH DateTable
AS
(
    SELECT CONVERT(date, DateAdd(WEEK, -1, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < CONVERT(date, GETDATE())
)
SELECT Tags.Name, dt.DATE as Date,COUNT(Events.ID) as Count
FROM DateTable dt
CROSS JOIN Tags Tags
LEFT JOIN Events ON dt.[DATE] = CONVERT(date, Events.[CreatedAt])
and Tags.Id = Events.TagId
where tags.Name like '%sample%'
GROUP BY Tags.Name, Date
ORDER BY Tags.Name, Date;

fiddle

edited Jul 15 '13 at 05:32

answered Jul 15 '13 at 05:20

Praveen Prasannan

7,093
10
50
70

Perfect. Would you mind to explain how you got to this answer? Such information will be invaluable to other folks reading the question and then the answer. – bloudraak Jul 15 '13 at 05:26
The first idea is to make a result set of all the tags and dates. as here: http://www.sqlfiddle.com/#!3/e6ffb/22 .... So applied a cross join. Then left join events table to get the count. – Praveen Prasannan Jul 15 '13 at 05:32
@Praveen: don't execute such rCTE on large result sets (say, 50 years) or you'll get poor performance. There surely are faster solutions for tally tables. – OzrenTkalcecKrznaric Jul 15 '13 at 06:28
Yup.. I didnt know about tally tables. Thanks for the tip – Praveen Prasannan Jul 15 '13 at 06:56
How would a tally table make it better? – bloudraak Jul 18 '13 at 07:42

How does one summarise data grouped by a column and date, accounting for dates with missing data

Schema

Sample Data

What I've tried

1 Answers1