Adding Missing Days in Data -SQL

Ok, you're going to need the CalTable() function from Bernd's answer. We're going to use it to create a list of all calendar dates between the MIN(BalDate) and the MAX(BalDate) in tblID. We're also going to CROSS JOIN that with the list of DISTINCT IDNbr values, which I assume is the PK of tblID.

Let's create some sample data.

CREATE TABLE #tblID (ID VARCHAR(3), IDNbr INT, Balance INT, BalDate DATE)

INSERT INTO #tblID
(
    ID
    ,IDNbr
    ,Balance
    ,BalDate
)
VALUES
('001',91,529,'1/5/2018'),
('002',87,654,'1/5/2018'),
('003',45,258,'1/5/2018'),

('004',91,611,'1/8/2018'),
('005',87,753,'1/8/2018'),
('006',45,357,'1/8/2018')

Next, we're going to INSERT new records into #tblID for the missing days. The magic here is in the LAG() function, which can looks at a previous row's data. We give it an expression for the offset value, based on the difference between missing date and the last date with data.

;WITH IDs AS
(
    SELECT DISTINCT
        IDNbr 
    FROM #tblID
)
,IDDates AS
(
    SELECT 
        BalDate = c.[Date]
        ,i.IDNbr
    FROM [CalTable]((SELECT MIN(BalDate) FROM #tblID), (SELECT MAX(BalDate) FROM #tblID)) c
    CROSS APPLY IDs i
)
,FullResults AS
(
    SELECT 
        i.BalDate
        ,i.IDNbr 
        ,Balance = CASE WHEN t.Balance IS NOT NULL THEN t.Balance 
                    ELSE LAG(t.Balance,
                                            DATEDIFF(
                                                    DAY
                                                    ,(SELECT MAX(t1.BalDate) FROM #tblID t1 WHERE t1.IDNbr = i.IDNbr AND t1.BalDate <= i.BalDate GROUP BY t1.IDNbr)
                                                    ,i.BalDate
                                                )
                    ) OVER (PARTITION BY i.IDNbr ORDER BY i.BalDate ASC) 
                    END 
    FROM IDDates i
    LEFT JOIN #tblID t ON t.BalDate = i.BalDate AND t.IDNbr = i.IDNbr
)
INSERT INTO #tblID
(
    IDNbr
    ,Balance
    ,BalDate
)
SELECT 
    f.IDNbr
    ,f.Balance
    ,f.BalDate
FROM FullResults f
LEFT JOIN #tblID t ON t.IDNbr = f.IDNbr AND t.BalDate = f.BalDate
WHERE t.IDNbr IS NULL

At this point, if we didn't care about the ID field, which appears to be a 3-character string representation of the row number, we'd be good. However, while I don't think it's a good practice to use a string in this manner, I'm also not one to comment on someone else's business requirements that I am not privy to.

So let's assume we have to update the ID field to match the expected output. We can do that like this:

;WITH IDUpdate AS
(
    SELECT 
        ID = RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY BalDate ASC, IDNbr DESC) AS VARCHAR), 3)
       ,t.IDNbr
       ,t.Balance
       ,t.BalDate 
    FROM #tblID t
)
UPDATE t
SET t.ID = i.ID
FROM #tblID t
INNER JOIN IDUpdate i ON i.IDNbr = t.IDNbr AND i.BalDate = t.BalDate

Now if you query your updated table, you'll get the following:

SELECT 
    ID
    ,IDNbr
    ,Balance
    ,BalDate 
FROM #tblID
ORDER BY BalDate ASC, IDNbr DESC

Output:

ID  | IDNbr | Balance | BalDate
------------------------------
001 | 91    | 529     | 2018-01-05
002 | 87    | 654     | 2018-01-05
003 | 45    | 258     | 2018-01-05
004 | 91    | 529     | 2018-01-06
005 | 87    | 654     | 2018-01-06
006 | 45    | 258     | 2018-01-06
007 | 91    | 529     | 2018-01-07
008 | 87    | 654     | 2018-01-07
009 | 45    | 258     | 2018-01-07
010 | 91    | 611     | 2018-01-08
011 | 87    | 753     | 2018-01-08
012 | 45    | 357     | 2018-01-08