How to add dates in two dates weekwise?

问题:

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:

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?

回答1:

Use a tally table

• either from a physically stored numbers table, see code here
• or create one on-the-fly with a CTE.

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...

回答2:

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

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