How to fill data for all dates in SQL Server

Question

I need to create table that will hold data for all dates. Balance table for every date. Idea is that if I don't have for particular date data, to get data from the previous day...

I have table like this

2018-09-01 111    1000.00
2018-09-01 222    2000.00
2018-09-02 NULL  NULL
2018-09-03 111    2000.00
2018-09-03 222    2000.00
2018-09-04 NULL  NULL
2018-09-05 111    NULL
2018-09-05 222    NULL
2018-09-06 NULL  NULL
2018-09-07 111    3000.00
2018-09-07 222    10000.00
2018-09-08 NULL  NULL
2018-09-09 NULL  NULL
2018-09-10 NULL  NULL

Output for this should be:

2018-09-01  111 1000.00
2018-09-01  222 2000.00
2018-09-02  111 1000.00
2018-09-02  222 2000.00
2018-09-03  111 2000.00
2018-09-03  222 2000.00
2018-09-04  111 2000.00
2018-09-04  222 2000.00
2018-09-05  111 2000.00
2018-09-05  222 2000.00
2018-09-06  111 2000.00
2018-09-06  222 2000.00
2018-09-07  111 3000.00
2018-09-07  222 10000.00
2018-09-08  111 3000.00
2018-09-08  222 10000.00
2018-09-09  111 3000.00
2018-09-09  222 10000.00
2018-09-10  111 3000.00
2018-09-10  222 10000.00

Can someone help me with this ? I cannot use Max(id)over( or something like this (or I'm wrong :)), because there can be multiple rows for one day.

Thanks in advance...

create table #t 
(
    date datetime, 
    userid int, 
    balance decimal(18,2)
)

insert into #t 
select '2018-09-01', 111, 1000
union 
select '2018-09-01', 222, 2000
union
select '2018-09-03', 111, 2000
union 
select '2018-09-03', 222, 2000
union 
select '2018-09-05', 111, NULL
union 
select '2018-09-05', 222, NULL
union 
select '2018-09-07', 111, 3000
union 
select '2018-09-07', 222, 10000


create table #dates
(
    date datetime
)

insert into #dates
select '2018-09-01'
union
select '2018-09-02'
union
select '2018-09-03'
union
select '2018-09-04'
union
select '2018-09-05'
union
select '2018-09-06'
union
select '2018-09-07'
union
select '2018-09-08'
union
select '2018-09-09'
union
select '2018-09-10'

Answer 1

Assuming that the column names are date, id, balance:

select 
  t.date, 
  t.id, 
  (
    select 
      tt.balance 
    from 
      tablename tt
    where
      tt.id = t.id
      and
      tt.balance is not null
      and
      tt.date = (
        select max(tablename.date) from tablename 
        where 
        tablename.balance is not null
        and 
        tablename.id = t.id
        and
        tablename.date <= t.date
      )
  ) as balance
from (
  select date, id 
  from (select distinct date from tablename) as d, 
  (select distinct id from tablename where id is not null) as v
) as t 
order by t.date, t.id

See the demo

Answer 2

Here's an option to explore.

You're basically going to want to build out a "UserId Calendar Table" and then you can use a cross apply back to your source data with a top 1 to fill in your balance.

There are several ways you can create calendar tables depending on the specifics of what you are trying to solve. A calendar table or some might refer to them as Date Dimension tables is basically giving you a table will every date(day) that is in a data set. No Gaps. Could be a year, could be derived off your data set, etc...

When I say "UserId Calendar Table", that builds on the concept mentioned above to include the UserId from your data set as well. You want a table will all dates for all UserIds and then you can join back to the source data to fill in your balance.

What's unclear is:

• Is there gaps in your source data as far as dates?
• You mention multiple dates? But is there only multiples because of multiple UserIds?

I'll make the assumption there could potentially be gaps with the dates and I'll also assume your source only has multiple dates because of multiple UserIds, but there would never be data for the same UserId on the same day.

Have a look at this, using table variables to show the example, you can update as needed for your situation:

--Source data table
DECLARE @TestData TABLE
    (
        [date] DATETIME
      , [userid] INT
      , [balance] DECIMAL(18, 2)
    );

--Load Test Data
INSERT INTO @TestData (
                          [date]
                        , [userid]
                        , [balance]
                      )
VALUES ( '2018-09-01', 111, 1000.00 )
     , ( '2018-09-01', 222, 2000.00 )
     , ( '2018-09-02', NULL, NULL )
     , ( '2018-09-03', 111, 2000.00 )
     , ( '2018-09-03', 222, 2000.00 )
     , ( '2018-09-04', NULL, NULL )
     , ( '2018-09-05', 111, NULL )
     , ( '2018-09-05', 222, NULL )
     , ( '2018-09-06', NULL, NULL )
     , ( '2018-09-07', 111, 3000.00 )
     , ( '2018-09-07', 222, 10000.00 )
     , ( '2018-09-08', NULL, NULL )
     , ( '2018-09-09', NULL, NULL )
     , ( '2018-09-10', NULL, NULL );

--Define our "User Id Calendar" Table
DECLARE @UserIdCalendar TABLE
    (
        [Date] DATETIME
      , [UserId] INT
    );

--Was not clear if you already had a calendar table, this example derives one based on your data set.
--Based on your data, get the minimum and maximum dates and use a recursive CTE to fill in all dates the middle.

WITH [DateCTE]
AS ( SELECT MIN([date]) AS [StartDate]
          , MIN([date]) AS [BalanceDate]
          , MAX([date]) AS [EndDate]
     FROM   @TestData
     UNION ALL
     SELECT [DateCTE].[StartDate]
          , DATEADD(DAY, 1, [DateCTE].[BalanceDate]) AS [BalanceDate]
          , [DateCTE].[EndDate]
     FROM   [DateCTE]
     WHERE  [DateCTE].[BalanceDate] < [DateCTE].[EndDate] )
--Inserting into our UserIdCalendar table
--Join here on a distint list of UserIds from our source data
INSERT INTO @UserIdCalendar (
                                [Date]
                              , [UserId]
                            )
            SELECT     [dt].[BalanceDate]
                     , [ud].[UserId]
            FROM       [DateCTE] [dt]
            INNER JOIN (
                           SELECT DISTINCT [userid]
                           FROM   @TestData
                           WHERE  [userid] IS NOT NULL
                       ) [ud]
                ON 1 = 1;

--If you do not have gaps in your date data you can skip the CTE and use the code below.
--Just comment out the above code and uncomment below as this will pull a distinct list of dates and join to a distinct list of UserIds.
--INSERT INTO @UserIdCalendar (
--                                [Date]
--                              , [UserId]
--                            )
--            SELECT     [dt].[date]
--                     , [ud].[UserId]
--            FROM       (
--                           SELECT DISTINCT [date]
--                           FROM   @TestData
--                       ) [dt]
--            INNER JOIN (
--                           SELECT DISTINCT [userid]
--                           FROM   @TestData
--                           WHERE  [userid] IS NOT NULL
--                       ) [ud]
--                ON 1 = 1;

--Now that we have our calendar table, cross apply to get the balance.
SELECT      *
FROM        @UserIdCalendar [a]
CROSS APPLY (
                SELECT   TOP 1 [b].[balance] --give me the top 1
                FROM     @TestData [b]
                WHERE    [b].[userid] = [a].[userid] --for the Specific UserId
                         AND [b].[balance] IS NOT NULL --balance is not null 
                         AND [b].[date] <= [a].[date] --Date is less than or equal.  This is where it will get either the balance on that date or the previous balance.
                ORDER BY [b].[date] DESC --Order to make sure our top 1 give us the correct record.
            ) AS [bl];

Answer 3

Left Join from your Calendar table, and if a particular row is NULL due to missing fact data, COALESCE that NULL with a sub-select that gets the TOP 1 fact table row, ordered by date, where the date is less than the date with the missing data

Answer 4

You might need something like this....But it only lags to level 1.

SELECT [Date] ,userid ,balance , CASE WHEN userid IS NULL THEN LAG (userid,1) OVER (ORDER BY [Date]) END new_userid , CASE WHEN balance IS NULL THEN LAG (balance,1) OVER (ORDER BY [Date]) END new_balance FROM #t