Sql missing rows when grouping by day, month, year

Question

If I select from a group of tables by month, day, year, it only returns rows with records and excludes combinations without any records, forcing him to immediately see that every day or month has activity, you need to carefully look at the date column for spaces. How can I get a row for every day / month / year, even if there is no data in T-SQL?

Answer 1

My developer came back to me with this code, emphasizing the conversion to dashes, because StackOverflow distorts underscores - no numbers table is required. Our example is a little complicated by joining another table, but perhaps the sample code will help someone someday.

declare @career-fair-id int select @career-fair-id = 125 create table #data ([date] datetime null, [cumulative] int null) declare @event-date datetime, @current-process-date datetime, @day-count int select @event-date = (select careerfairdate from tbl-career-fair where careerfairid = @career-fair-id) select @current-process-date = dateadd(day, -90, @event-date) while @event-date <> @current-process-date begin select @current-process-date = dateadd(day, 1, @current-process-date) select @day-count = (select count(*) from tbl-career-fair-junction where attendanceregister <= @current-process-date and careerfairid = @career-fair-id) if @current-process-date <= getdate() insert into #data ([date], [cumulative]) values(@current-process-date, @day-count) end select * from #data drop table #data 

Answer 2

Create a calendar table and outer join in this table

Answer 3

Look at the numbers table. While this may be hacky, this is the best method I've used to quickly query for missing data or display all dates or anything where you want to check values ​​within a range, regardless of whether all values ​​in that range are used.

Answer 4

Based on what SQLMenace said, you can use CROSS JOIN to quickly populate a table or efficiently create it in memory.
http://www.sitepoint.com/forums/showthread.php?t=562806

Answer 5

The task invokes a complete set of dates that should be combined to the left of your data, for example


DECLARE @StartInt int
DECLARE @Increment int
DECLARE @Iterations int

SET @StartInt = 0
SET @Increment = 1
SET @Iterations = 365


SELECT
tCompleteDateSet.[Date]
,AggregatedMeasure = SUM(ISNULL(t.Data, 0))
FROM
(

SELECT
[Date] = dateadd(dd,GeneratedInt, @StartDate)
FROM
[dbo].[tvfUtilGenerateIntegerList] (
@StartInt,
,@Increment,
,@Iterations
)
) tCompleteDateSet
LEFT JOIN tblData t
ON (t.[Date] = tCompleteDateSet.[Date])
GROUP BY
tCompleteDateSet.[Date]

where the tvfUtilGenerateIntegerList table function is defined as


-- Example Inputs

-- DECLARE @StartInt int
-- DECLARE @Increment int
-- DECLARE @Iterations int
-- SET @StartInt = 56200
-- SET @Increment = 1
-- SET @Iterations = 400
-- DECLARE @tblResults TABLE
-- (
-- IterationId int identity(1,1),
-- GeneratedInt int
-- )


-- =============================================
-- Author: 6eorge Jetson
-- Create date: 11/22/3333
-- Description: Generates and returns the desired list of integers as a table
-- =============================================
CREATE FUNCTION [dbo].[tvfUtilGenerateIntegerList]
(
@StartInt int,
@Increment int,
@Iterations int
)
RETURNS
@tblResults TABLE
(
IterationId int identity(1,1),
GeneratedInt int
)
AS
BEGIN

DECLARE @counter int
SET @counter= 0
WHILE (@counter < @Iterations)
BEGIN
INSERT @tblResults(GeneratedInt) VALUES(@StartInt + @counter*@Increment)
SET @counter = @counter + 1
END


RETURN
END
--Debug
--SELECT * FROM @tblResults