SQL Server: How to select all days in a date range even if no data exists for some days

by

You can use a recursive CTE to build your list of 30 days, then join that to your data

--test
select cast('05 jan 2011' as datetime) as DT, 1 as val into #t
union all select CAST('05 jan 2011' as datetime), 1 
union all select CAST('29 jan 2011' as datetime), 1 

declare @start datetime="01 jan 2011"
declare @end   datetime = dateadd(day, 29, @start)

;with amonth(day) as
(
    select @start as day
        union all
    select day + 1
        from amonth
        where day < @end
)
select amonth.day, count(val)
    from amonth 
    left join #t on #t.DT = amonth.day
group by amonth.day


>>

2011-01-04 00:00:00.000 0
2011-01-05 00:00:00.000 2
2011-01-06 00:00:00.000 0
2011-01-07 00:00:00.000 0
2011-01-08 00:00:00.000 0
2011-01-09 00:00:00.000 0
...

More Related Contents:

  1. How do I create a unique constraint that also allows nulls?
  2. How do I get list of all tables in a database using TSQL?
  3. Get first day of week in SQL Server
  4. What is the significance of 1/1/1753 in SQL Server?
  5. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  6. Escape a string in SQL Server so that it is safe to use in LIKE expression
  7. Using a if condition in an insert SQL Server
  8. Insert Update trigger how to determine if insert or update
  9. SQL time difference between two dates result in hh:mm:ss
  10. What is the best way to truncate a date in SQL Server?
  11. Dynamic SQL (passing table name as parameter)
  12. SQL Server: Difference between PARTITION BY and GROUP BY
  13. How do you truncate all tables in a database using TSQL?
  14. Understanding SQL Server LOCKS on SELECT queries
  15. TSQL Email Validation (without regex)
  16. Dynamic SQL results into temp table in SQL Stored procedure
  17. SQL Server Output Clause into a scalar variable
  18. TSQL: Create a view that accesses multiple databases
  19. TABLOCK vs TABLOCKX
  20. How to convert number of minutes to hh:mm format in TSQL?
  21. How do I select last 5 rows in a table without sorting?
  22. T-SQL incrementing counter based on change in a column value
  23. SELECT INTO a table variable in T-SQL
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. Include missing months in Group By query
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment