Synopsis: How to write the SQL code to insert one record from a flat-file into separate records to be inserted into a SQL Server table.
Specifics: I have a table to record lease payments. For each rental agreement the due date is always the first day of the month. There is an entry in the table for each month for the duration of the lease. For simplicity’s sake let’s say that there is a lease contract that runs from September 1, 2019 to August 31, 2020. The table would look like this:
+------------------------------------------------------+
| CONTRACT_ID | DUE_DATE_DT | INVOICE_ID | RENTAL_AMT |
+-------------+-------------+------------+-------------+
| 12345 | 9/1/2019 | | |
+-------------+-------------+------------+-------------+
| 12345 | 10/1/2019 | | |
+-------------+-------------+------------+-------------+
...
+-------------+-------------+------------+-------------+
| 12345 | 8/1/2020 | | |
+-------------+-------------+------------+-------------+
The invoices are normally for one calendar month, but sometimes there can be two or three months because the vendor is playing catch up. The data is received in a flat file, a sample would look like:
Contract Rental Period From Date Rental Period To Date Invoice Number Amount
31125 9/1/2019 9/30/2019 6378 400.00 (standard scenario)
12345 9/1/2019 11/30/2019 789 150.00 (multi-month scenario)
The data from the flat file needs to be stored in three separate records in the table:
+------------------------------------------------------+
| CONTRACT_ID | DUE_DATE_DT | INVOICE_ID | RENTAL_AMT |
+-------------+-------------+------------+-------------+
| 12345 | 9/1/2019 | 789 | 50.00 |
+-------------+-------------+------------+-------------+
| 12345 | 10/1/2019 | 789 | 50.00 |
+-------------+-------------+------------+-------------+
| 12345 | 11/1/2019 | 789 | 50.00 |
+-------------+-------------+------------+-------------+
Thank you in advance.
You can try to use a recursive CTE. Consider this:
CREATE TABLE #RawData(Contract int, Rental_Period_From Date, Rental_Period_To Date, Invoice_Number int, Amount Decimal(10,2))
INSERT INTO #RawData VALUES(31225, '9/1/2019','9/30/2019', 6378,400.00)
INSERT INTO #RawData VALUES(12345, '9/1/2019','11/30/2019', 789,150.00)
;WITH CTE
AS
(
select Contract,Rental_Period_From DueDate,Invoice_Number,Amount, 0 MonthCounter, Rental_Period_To from #RawData
UNION ALL
SELECT Contract, DATEADD(month,MonthCounter+1,DueDate),Invoice_Number,Amount, MonthCounter,Rental_Period_To from CTE
WHERE DATEADD(month,MonthCounter+1,DueDate) <= Rental_Period_To
)
SELECT CONTRACT, DueDate,Invoice_Number, CAST(Amount / (SELECT COUNT(*)
FROM CTE t1 WHERE t1.Contract = t2.Contract ) as decimal(10,2)) Amount
FROM CTE t2