为两个日期之间的字段添加缺失值的SQL查询?

我在这里只包含参数来说明您需要在哪里指定最早的日期和最晚的日期。如果需要的话,这两个值可以从你的数据中推断出来。

这里的技巧是将源数据转换为具有开始和结束日期的范围。然后,我们生成一个包含所需时间段的连续日期的日历表,并连接到我们的范围以确定结果。

Declare @MinDate datetime;
Declare @MaxDate datetime;

Set @MinDate = '2009-10-12';
Set @MaxDate = '2009-10-30';

With BioUser As
    (
    Select 1 As UserId, 178 As Weight, '2009-10-12' As DateAdded
    Union All Select 1, 175, '2009-10-18'   
    Union All Select 1, 172, '2009-10-27'   
    )
    , Calendar As
    (
    Select @MinDate As [Date]
    Union All
    Select DateAdd(d,1,[Date])
    From Calendar
    Where [Date] < @MaxDate
    )
    , BioUserDateRanges As
    (
    Select B1.UserId, B1.Weight, B1.DateAdded As StartDate, Coalesce(Min(B2.DateAdded),@MaxDate) As EndDate
    From BioUser As B1
        Left Join BioUser As B2
            On B2.UserId = B1.UserId
                And B2.DateAdded > B1.DateAdded
    Group By B1.UserId, B1.Weight, B1.DateAdded
    )
Select BR.Weight, C.[Date]
From Calendar As C
    Join BioUserDateRanges As BR
        On BR.StartDate <= C.[Date]
            And BR.EndDate >= C.[Date]
Option (MaxRecursion 0);

WITH Dimdate  As
(
SELECT DATEADD(DAY,-number,CAST('2009-12-31' AS DATETIME)) AS [Date]
from master.dbo.spt_values where type='p'
),
BioUser  AS
(SELECT 1 AS [UserId], 178 AS [Weight], CAST('20091012' AS DATETIME) AS DateAdded
UNION ALL
SELECT 1 AS [UserId], 175 AS [Weight], CAST('20091018' AS DATETIME)
UNION ALL
SELECT 1 AS [UserId], 172 AS [Weight], CAST('20091027' AS DATETIME)
),
NumberedT AS
(
SELECT [UserId],[Weight],DateAdded, 
       ROW_NUMBER() OVER (PARTITION BY [UserId] ORDER BY DateAdded) AS RN
FROM BioUser 
)
SELECT  
     ISNULL(T1.[UserId], T2.[UserId]) [UserId], 
     ISNULL(T1.Weight, T2.Weight) [Weight], 
     Dimdate.[Date]
 FROM NumberedT T1
FULL OUTER JOIN NumberedT T2 ON T2.RN = T1.RN+1 AND T2.[UserId]= T1.[UserId]
INNER JOIN Dimdate ON 
    (Dimdate.[Date] >= ISNULL(T1.DateAdded, T2.DateAdded) 
        AND Dimdate.[Date]< T2.DateAdded)
OR
    (T2.DateAdded IS NULL AND Dimdate.[Date]=T1.DateAdded)
ORDER BY Dimdate.[Date]

你应该从 DimDate 表和 JOIN 这个 BioUser 关于这一点:

SELECT u.Weight, u.DateAdded
FROM DimDate d
    LEFT OUTER JOIN d.Date = u.DateAdded

NULL 对于未填充在 生物使用者 桌子。

Select Weight, Date 
from Dimdate d left outer join BioUser b
on b.DateAdded= d.Date

如果当天没有权重值,则应该为权重返回null。

HTH公司

您需要从dimDate表中提取日期,而不是bioUser,后者可能为空

SELECT    
        (SELECT TOP (1) Weight
         FROM BioUser AS b
         WHERE (CAST(DateTested AS Date) <= k.Date) AND (UserId= @UserId)
         ORDER BY DateTested DESC) AS Weight, Date AS DateTested
FROM     DimDate AS k
WHERE     (Date BETWEEN @StartDate AND @EndDate)