<p>If I select from a table group by the month, day, year, 
it only returns rows with records and leaves out combinations without any records, making it appear at a glance that every day or month has activity, you have to look at the date column actively for gaps. How can I get a row for every day/month/year, even when no data is present, in T-SQL?</p>

[{'answer_id': 40462, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 3, 'selected': False, 'text': '<p>Create a calendar table and outer join on that table</p>\n'}, {'answer_id': 40481, 'author': 'Tom Mayfield', 'author_id': 2314, 'author_profile': 'https://Stackoverflow.com/users/2314', 'pm_score': 0, 'selected': False, 'text': '<p>Look into using a <a href="http://andre-silva-cardoso.blogspot.com/2007/11/sql-trickspatterns-1-numbers-table.html" rel="nofollow noreferrer">numbers table</a>.  While it can be hackish, it\'s the best method I\'ve come by to quickly query missing data, or show all dates, or anything where you want to examine values within a range, regardless of whether all values in that range are used.</p>\n'}, {'answer_id': 40502, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 0, 'selected': False, 'text': '<p>Building on what SQLMenace said, you can use a CROSS JOIN to quickly populate the table or efficiently create it in memory.<br>\n<a href="http://www.sitepoint.com/forums/showthread.php?t=562806" rel="nofollow noreferrer">http://www.sitepoint.com/forums/showthread.php?t=562806</a></p>\n'}, {'answer_id': 40619, 'author': 'ryw', 'author_id': 2477, 'author_profile': 'https://Stackoverflow.com/users/2477', 'pm_score': 2, 'selected': True, 'text': '<p><a href="https://stackoverflow.com/users/51836/clark">My developer</a> got back to me with this code, underscores converted to dashes because StackOverflow was mangling underscores -- no numbers table required. Our example is complicated a bit by a join to another table, but maybe the code example will help someone someday.</p>\n\n<pre><code>declare @career-fair-id int \nselect @career-fair-id = 125\n\ncreate table #data ([date] datetime null, [cumulative] int null) \n\ndeclare @event-date datetime, @current-process-date datetime, @day-count int \nselect @event-date = (select careerfairdate from tbl-career-fair where careerfairid = @career-fair-id) \nselect @current-process-date = dateadd(day, -90, @event-date) \n\n    while @event-date &lt;&gt; @current-process-date \n    begin \n    select @current-process-date = dateadd(day, 1, @current-process-date) \n    select @day-count = (select count(*) from tbl-career-fair-junction where attendanceregister &lt;= @current-process-date and careerfairid = @career-fair-id) \n        if @current-process-date &lt;= getdate() \n        insert into #data ([date], [cumulative]) values(@current-process-date, @day-count) \n    end \n\n    select * from #data \n    drop table #data \n</code></pre>\n'}, {'answer_id': 274057, 'author': '6eorge Jetson', 'author_id': 23422, 'author_profile': 'https://Stackoverflow.com/users/23422', 'pm_score': 0, 'selected': False, 'text': '<p>The task calls for a complete set of dates to be left-joined onto your data, such as</p>\n\n<p><code>\n<br/>DECLARE    @StartInt   int\n<br/>DECLARE    @Increment  int\n<br/>DECLARE    @Iterations int\n<br/>\n<br/>SET    @StartInt   = 0\n<br/>SET    @Increment = 1\n<br/>SET    @Iterations = 365\n<br/>\n<br/>\n<br/>SELECT\n<br/>&nbsp; &nbsp;   tCompleteDateSet.[Date]\n<br/>&nbsp;     ,AggregatedMeasure = SUM(ISNULL(t.Data, 0))\n<br/>FROM\n<br/>&nbsp; &nbsp; &nbsp; &nbsp;                (<br>\n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                  SELECT \n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                        [Date] = dateadd(dd,GeneratedInt, @StartDate)\n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                  FROM \n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                        [dbo].[tvfUtilGenerateIntegerList] (\n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                                   @StartInt, \n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                                  ,@Increment, \n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                                  ,@Iterations\n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;                              )\n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;              )       tCompleteDateSet\n<br/>&nbsp; &nbsp;  LEFT JOIN           tblData t\n<br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;         ON      (t.[Date] = tCompleteDateSet.[Date])\n<br/>GROUP BY\n<br/>    tCompleteDateSet.[Date]\n</code></p>\n\n<p>where the table-valued function tvfUtilGenerateIntegerList is defined as</p>\n\n<p><code>\n<br/>-- Example Inputs\n<br/>\n<br/>-- DECLARE @StartInt   int \n<br/>-- DECLARE @Increment  int\n<br/>-- DECLARE @Iterations int\n<br/>-- SET @StartInt   = 56200\n<br/>-- SET @Increment = 1\n<br/>-- SET @Iterations = 400\n<br/>-- DECLARE @tblResults TABLE \n<br/>--  (\n<br/>--  &nbsp; &nbsp;  IterationId int identity(1,1),\n<br/>-- &nbsp; &nbsp;   GeneratedInt    int\n<br/>-- )\n<br/>\n<br/>\n<br/>-- =============================================\n<br/>-- Author:     6eorge Jetson\n<br/>-- Create date: 11/22/3333\n<br/>-- Description:    Generates and returns the desired list of integers as a table\n<br/>-- =============================================\n<br/>CREATE FUNCTION [dbo].[tvfUtilGenerateIntegerList]\n<br/>(\n<br/>&nbsp; &nbsp;  @StartInt   int, \n<br/>&nbsp; &nbsp;  @Increment  int,\n<br/>&nbsp;     @Iterations int\n<br/>) \n<br/>RETURNS \n<br/>@tblResults TABLE \n<br/>(\n<br/>&nbsp; &nbsp;  IterationId int identity(1,1),\n<br/>&nbsp; &nbsp;  GeneratedInt    int\n<br/>)\n<br/>AS\n<br/>BEGIN\n<br/>\n<br/>&nbsp;     DECLARE @counter int \n<br/>&nbsp;     SET @counter= 0\n<br/>&nbsp;     WHILE (@counter &lt; @Iterations)\n<br/>&nbsp; &nbsp;      BEGIN\n<br/>&nbsp; &nbsp;      INSERT @tblResults(GeneratedInt) VALUES(@StartInt + @counter*@Increment)\n<br/>&nbsp; &nbsp;      SET @counter = @counter + 1\n<br/>&nbsp; &nbsp;      END\n<br/><br>\n<br/>&nbsp;     RETURN \n<br/>END\n<br/>--Debug\n<br/>--SELECT * FROM @tblResults</p>\n\n<p></code></p>\n'}]
['https://Stackoverflow.com/questions/40456', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2477/']