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