0

I have a table in which rows have dates as monday dates of the weeks. Some consecutive rows may not have consecutive weekdate and thus has gaps in between. This image will clear the situation: enter image description here

As clear from the image, there is a gap between weekdates 2016-08-08 and 2016-09-05 as rows with weekdates '2016-08-15','2016-08-22','2016-08-29' are not there before '2016-09-05'.

So, how can I fill this gap with rows for all these dates and null for rest two columns?

Hemant Sisodia
  • 488
  • 6
  • 23

2 Answers2

1

Use a tally table

You might try this code, which will generate a list of Mondays

DECLARE @start INT=0;
DECLARE @end INT=20;
DECLARE @step INT=7;

WITH x AS(SELECT 1 AS N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tbl(N))--10^1
,N3 AS (SELECT 1 AS N FROM x CROSS JOIN x AS N2 CROSS JOIN x N3) --10^3
,Tally AS(SELECT TOP(@end-@start +1) (ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) + @start -1) * @step AS Nr FROM N3 
          CROSS JOIN N3 N6 CROSS JOIN N3 AS N9)
SELECT DATEADD(DAY,Nr,{d'2016-08-01'}) AS Monday 
FROM Tally

You can specify the count of generated rows with @start and @end, the @step should be 7 in your case. This will add 0, 7, 14, 21, ... to a given date (which should be a Monday in your case).

Now use a LEFT JOIN to combine this with your table data. This should result in a gap-less list of all Mondays together with values - if there are any...

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

Try this:

SELECT DATEADD(week,1,[select query]) -- In select query write the query 
                                      -- to get the date to which you need to add 1 week

enter image description here

In the above query it will take the current date i.e 2016-09-08 15:19:06.950 and add 1 week to it and give the resultant date i.e 2016-09-15 15:19:40.657

Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38
  • Thanks for the reply, however I dont want 1 week added to any date, but I want to get all the weekddates which could fill gap between 2 dates like I shown in my example. – Hemant Sisodia Sep 08 '16 at 10:01