Query for how to add the missing dates in sql

Question

need to know how to add the date missing with null 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 that I need is like:

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

What have you tried? please post a working query, does not matter is incorrect, at least is a starting point and maybe you are not far from the solution — Yaroslav
– Yaroslav, Commented Sep 26, 2012 at 8:02

Joe G Joseph · Accepted Answer · 2012-09-26 08:10:30Z

5

Best option is to keep a calender table which contains all the dates for some years that you want to calculate and then left join with that table

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

You could create a calender table very easily. There are lots of scripts available in the net. click for examples

edited Sep 26, 2012 at 8:10

answered Sep 26, 2012 at 8:03

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Niladri Biswas · Accepted Answer · 2012-09-26 08:21:02Z

Form a Date Calender with a start and end date range and perform a left join with your table to get the needed result.

e.g.

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 @t t 
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

Sir if there is one more coloum with name Event and with the case i was able to add the event but was unable to add for the newly added data

AnandPhadke · Accepted Answer · 2012-09-26 08:07:33Z

2

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 just join this result with your table on dates column to get the missing dates

answered Sep 26, 2012 at 8:07

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Collectives™ on Stack Overflow

Query for how to add the missing dates in sql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related