如何在每周的两个日期中添加日期? [英] 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:

从图像中可以清楚地看到，在星期几2016-08-08和2016-09-05之间存在间隙，因为星期几为'2016-08-15'，'2016-08-22'，'2016-08-29'在'2016-09-05'之前不存在.

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

那么，如何用所有这些日期的行填充此空白，而其余两列用null填充?

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

推荐答案

使用计数表

• 从物理存储的数字表中，请参见此处的代码
• 或使用 CTE 动态创建一个 .

• 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

您可以使用 @start 和 @end 指定生成的行数，在您的情况下， @step 应该为7.这会将0、7、14、21，...添加到给定的日期(在您的情况下应该是星期一).

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

现在使用 LEFT JOIN 将其与表格数据结合起来.这将导致所有星期一的无间隙列表以及值-如果有的话...

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

这篇关于如何在每周的两个日期中添加日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！