SQL Query for adding missing values for a field between two dates?

Question

Ok, So I have these two tables -

BioUser- UserId,Weight,DateAdded
DimDate-Date // It has basically all the dates for any period..its basically a table with all dates till 2050

Now the BioUser table has entries for weight of a user but not for everyday but whenever they enter their weight. So I want to basically build a list of values for date and weight for all those missing dates in the BioUser. To explain myself better here is an example -

BioUser -

UserId Weight  DateAdded
 1      178    10/12/2009
 1      175    10/18/2009
 1      172    10/27/2009

So when I am trying to build a list between two dates say 10/12/2009 to 10/30/2009. It should display a list like -

Weight Date

178  10/12/2009
178  10/13/2009
178  10/14/2009
178  10/15/2009
178  10/16/2009
178  10/17/2009
175  10/18/2009
175  10/19/2009
175  10/20/2009
175  10/21/2009
175  10/22/2009
175  10/23/2009
175  10/24/2009
175  10/25/2009
175  10/26/2009
172  10/27/2009
172  10/28/2009
172  10/29/2009
172  10/30/2009

I have query something like this -

Select Weight,DateAdded from BioUser join Dimdate on BioUser.DateAdded=Dimdate.Date

But the above does not work somehow I want to get the most recent entry on weight from BioUser and only one entry also as the BioUser table can have multiple entries for a day.Can anyone please help me out ..

Answer 1

You should start with the DimDate table and JOIN the BioUser on that:

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

That will then display NULL for any date that was not populated in the BioUser table.

Answer 2

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

should return nulls for weight if there's no weight value for that day.

HTH

you need to pull the date from the dimDate table, not bioUser, which may be null

Answer 3

I only included parameters here to illustrate where you need to specify the earliest date and the latest date. These two values can be deduced from your data if necessary.

The trick here is to convert your source data into ranges with a start and end date. We then generate a calendar table of sequential dates covering the time period desired and join to our ranges to determine the results.

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

Answer 4

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]

Answer 5

Thanks to everyone for their answers this is how I did it -

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)