Databases
StackOverflow2013
Postsdbo
POHow does one summarise data grouped by a column and date, accounting for dates with missing data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow does one summarise data grouped by a column and date, accounting for dates with missing data
    primarykey
    data
    text
    <p>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).</p> <p>I'm using SQL Server 2008.</p> <p>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:</p> <pre><code>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 </code></pre> <p>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. </p> <h2><strong>Schema</strong></h2> <p>The Tags table look as follows:</p> <pre><code>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] </code></pre> <p>And Events table look as follows:</p> <pre><code>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] ) </code></pre> <p>Where TagId is a foreign key to the Tags table.</p> <h2><strong>Sample Data</strong></h2> <p>The Events table has the following data</p> <pre><code>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 </code></pre> <p>And the tags table has the following data:</p> <pre><code>Id Name === =========================== 3 Sample 4 Test1 5 Test2 6 Test3 1 xxx Sample xxx 2 yyy Sample yyy </code></pre> <h2><strong>What I've tried</strong></h2> <p>So, I joined it with a table getting the following:</p> <pre><code>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) </code></pre> <p>which returned</p> <pre><code>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 </code></pre> <p>I searched for ways to generated rows for days for which there is no data. I found an entry <a href="https://stackoverflow.com/questions/5899829/sql-server-how-to-select-all-days-in-a-date-range-even-if-no-data-exists-for-so">SQL Server: How to select all days in a date range even if no data exists for some days</a> but was unable to get it to work.</p> <p>To verify I got the right days, I ran the following query:</p> <pre><code>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]) &lt; CONVERT(date, GETDATE()) ) select DateTable.DATE FROM DateTable </code></pre> <p>Which returned:</p> <pre><code>2013-07-07 2013-07-08 2013-07-09 2013-07-10 2013-07-11 2013-07-12 2013-07-13 </code></pre> <p>My first attempt was to get it to work without specifying the <code>LIKE '%sample%'</code> in the where clause. </p> <pre><code>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]) &lt; 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] </code></pre> <p>I get the following result:</p> <pre><code>Name Date Count =================== =============== ================ xxx Sample xxx 2013-07-10 3 yyy Sample yyy 2013-07-10 1 Sample 2013-07-12 1 </code></pre> <p>I have tried other things, like changing the <code>RIGHT JOIN</code> to a <code>LEFT JOIN</code>, but I'm unable to get the desired result. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USbloudraak
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow does one summarise data grouped by a column and date, accounting for dates with missing data
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow does one summarise data grouped by a column and date, accounting for dates with missing data
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI 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....
      singulars
      1. POHow does one summarise data grouped by a column and date, accounting for dates with missing data
      1. USSquid
    2. COYes. Then I had a number of `NULL` Tag Names for the dates where the event didn't occur.
      singulars
      1. POHow does one summarise data grouped by a column and date, accounting for dates with missing data
      1. USbloudraak
    3. COYou 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`
      singulars
      1. POHow does one summarise data grouped by a column and date, accounting for dates with missing data
      1. USSquid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload