SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

by 
;with d(date) as (
  select cast('10/01/2012' as datetime)
  union all
  select date+1
  from d
  where date < '10/15/2012'
  )
select t.ID, d.date CDate, isnull(t.val, 0) val
from d
left join temp t
       on t.CDate = d.date
order by d.date
OPTION (MAXRECURSION 0) -- use this if your dates are >99 days apart

You need to make up the dates, so I’ve use a recursive common table expression here.
SQL Fiddle

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a nonnegative
integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is
not specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query
execution, the query is ended and an error is returned.

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Not equal != operator on NULL
  4. SET NOCOUNT ON usage
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. Subquery using Exists 1 or Exists *
  8. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  9. Select statement to find duplicates on certain fields
  10. Is it possible to specify condition in Count()?
  11. Getting only Month and Year from SQL DATE
  12. Update records in table from CTE
  13. WHERE Clause vs ON when using JOIN
  14. Selecting N rows in SQL Server
  15. how can I Update top 100 records in sql server
  16. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  17. How can I INSERT data into two tables simultaneously in SQL Server?
  18. How can a LEFT OUTER JOIN return more records than exist in the left table?
  19. Count Number of Consecutive Occurrence of values in Table
  20. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  21. Dynamic SQL to generate column names?
  22. Pivot data in T-SQL
  23. Column name or number of supplied values does not match table definition
  24. Split one column value into multiple column values
  25. How to find consecutive rows based on the value of a column?
  26. T-SQL – Aliasing using “=” versus “as” [closed]
  27. SQL Query – Concatenating Results into One String [duplicate]
  28. Know relationships between all the tables of database in SQL Server
  29. How to escape square brackets inside square brackets for field name
  30. T-SQL Skip Take Stored Procedure

Leave a Comment