Request on how to add missing dates in sql

Question

need to know how to add a missing date with zero data in the corresponding field

**05/28/2012 NULL 05/29/2012 NULL 05/30/2012 NULL 05/30/2012 Break In 05/30/2012 Break Out 05/31/2012 NULL** 06/03/2012 NULL 06/03/2012 Break In 06/03/2012 Break Out 06/03/2012 In Duty 06/03/2012 Out Duty 06/04/2012 NULL 06/04/2012 In Duty 06/04/2012 Out Duty 06/05/2012 NULL 06/05/2012 Break In 06/05/2012 Break Out 06/06/2012 NULL 06/06/2012 Break In 06/06/2012 Break Out 06/06/2012 In Duty 06/06/2012 Out Duty 06/07/2012 NULL 06/07/2012 In Duty 06/07/2012 Out Duty 06/10/2012 NULL 06/10/2012 Break Out 06/10/2012 In Duty 06/10/2012 Out Duty 06/11/2012 NULL 06/11/2012 In Duty 06/11/2012 Out Duty 06/12/2012 NULL 06/13/2012 NULL 06/14/2012 NULL 

The result I need looks like this:

 05/28/2012 NULL 05/29/2012 NULL 05/30/2012 NULL 05/30/2012 Break In 05/30/2012 Break Out 05/31/2012 NULL 06/01/2012 null 06/02/2012 null 06/03/2012 NULL 06/03/2012 Break In 06/03/2012 Break Out 06/03/2012 In Duty 06/03/2012 Out Duty 06/04/2012 NULL 06/04/2012 In Duty 06/04/2012 Out Duty 06/05/2012 NULL 06/05/2012 Break In 06/05/2012 Break Out 06/06/2012 NULL 06/06/2012 Break In 06/06/2012 Break Out 06/06/2012 In Duty 06/06/2012 Out Duty 06/07/2012 NULL 06/07/2012 In Duty 06/07/2012 Out Duty 06/08/2012 null 06/09/2012 null 06/10/2012 NULL 06/10/2012 Break Out 06/10/2012 In Duty 06/10/2012 Out Duty 06/11/2012 NULL 06/11/2012 In Duty 06/11/2012 Out Duty 06/12/2012 NULL 06/13/2012 NULL 06/14/2012 NULL 

Answer 1

Create a calendar with a start and end date and left-connect to your table to get the desired result.

eg.

 DECLARE @t TABLE(Dt Datetime, Value VARCHAR(20) NULL) INSERT INTO @t VALUES ('05/28/2012',NULL), ('05/29/2012',NULL), ('05/30/2012',NULL),('05/30/2012','Break In'),('05/30/2012','Break Out'), ('05/31/2012',NULL), ('06/03/2012',NULL),('06/03/2012','Break In'),('06/03/2012','Break Out'),('06/03/2012','In Duty'),('06/03/2012','Out Duty'), ('06/04/2012',NULL),('06/04/2012','In Duty'),('06/04/2012','Out Duty'), ('06/05/2012',NULL),('06/05/2012','Break In'),('06/05/2012','Break Out'), ('06/06/2012',NULL),('06/06/2012','Break In'),('06/06/2012','Break Out'),('06/06/2012','In Duty'),('06/06/2012','Out Duty'), ('06/07/2012',NULL),('06/07/2012','In Duty'),('06/07/2012','Out Duty'), ('06/10/2012',NULL),('06/10/2012','Break Out'),('06/10/2012','In Duty'),('06/10/2012','Out Duty'), ('06/11/2012',NULL),('06/11/2012','In Duty'),('06/11/2012','Out Duty'), ('06/12/2012',NULL), ('06/13/2012',NULL), ('06/14/2012',NULL) DECLARE @startDate DATETIME, @endDate DATETIME SELECT @startDate = '2012-05-28', @endDate = '2012-06-14' --yyyy-mm-dd ;WITH Calender AS ( SELECT @startDate AS CalanderDate UNION ALL SELECT CalanderDate + 1 FROM Calender WHERE CalanderDate + 1 <= @endDate ) SELECT [Date] = Convert(VARCHAR(10),CalanderDate,101) ,Value FROM Calender c LEFT JOIN @tt ON t.Dt = c.CalanderDate 

Result

 Date Value 05/28/2012 NULL 05/29/2012 NULL 05/30/2012 NULL 05/30/2012 Break In 05/30/2012 Break Out 05/31/2012 NULL 06/01/2012 NULL 06/02/2012 NULL 06/03/2012 NULL 06/03/2012 Break In 06/03/2012 Break Out 06/03/2012 In Duty 06/03/2012 Out Duty 06/04/2012 NULL 06/04/2012 In Duty 06/04/2012 Out Duty 06/05/2012 NULL 06/05/2012 Break In 06/05/2012 Break Out 06/06/2012 NULL 06/06/2012 Break In 06/06/2012 Break Out 06/06/2012 In Duty 06/06/2012 Out Duty 06/07/2012 NULL 06/07/2012 In Duty 06/07/2012 Out Duty 06/08/2012 NULL 06/09/2012 NULL 06/10/2012 NULL 06/10/2012 Break Out 06/10/2012 In Duty 06/10/2012 Out Duty 06/11/2012 NULL 06/11/2012 In Duty 06/11/2012 Out Duty 06/12/2012 NULL 06/13/2012 NULL 06/14/2012 NULL 

Hope this helps

Answer 2

The best option is to save the calendar table, which contains all the dates for several years that you want to calculate, and then join this table

 select date,col1 from calender_table c left join your_table t on c.[date]=t.[date] 

You can easily create a calendar table. There are many scripts available online. click for example

Answer 3

 Declare @stDate datetime='05/28/2012' declare @eddate datetime='06/14/2012' select DATEADD(day,number,@stdate) from master..spt_values where type='P' and DATEADD(day,number,@stdate) <= @eddate 

Here, simply attach this result to the “Table” column in the “Date” column to get the missing dates.