I have one table "Payment", which includes columns
1. payment_date (specified day in case we have payment)
2. invoice_date (creation date of invoice)
3. invoice_code (unique code of invoice)
4. balance (balance of invoice of each day)
5. total_payment_daily (total payment by day)
in this table, we have each payment day's data, but we miss data starting from creation invoice day and all days we do not have payment till today. so what I should do is to create query to add rows to this table to fill all day's balance_invoice with correct data till today, could you please provide me some query or idea for this, thanks in advance.
there is attachment of word file to present what table is.
--create an auxilliary table containing numbers
CREATE TABLE dbo.nums (num SMALLINT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
GO
--fill in 32K records (this can cover up to 87 years)
INSERT INTO dbo.nums DEFAULT VALUES
GO 32000
--get the missing payment dates per invoice_no
SELECT start_dates.invoice_code, DATEADD(dd, nums.num, starting_date) AS missing_payment_date
FROM
(--subquery returning the starting date of each invoice
SELECT invoice_code, MIN(invoice_date) AS starting_date, DATEDIFF(dd, MIN(invoice_date), GETDATE()) AS days_till2day
FROM Payment
GROUP BY invoice_code
) AS start_dates
CROSS APPLY dbo.nums AS nums
LEFT JOIN Payment AS p ON start_dates.invoice_code = p.invoice_code AND DATEADD(dd, nums.num, starting_date) = p.payment_date
WHERE nums.num <= days_till2day
AND p.invoice_code IS NULL --find missing dates per invoice number