Unfolding | Set Based Approach | Generate Rows Dynamically – Episode 6

In this video, we are going to learn;

1. How we can Generate rows dynamically for each record of a table in set based approach, and
2. How to do calculations on each row of a table generated dynamically

You can download the script used in this example from below the video.

--Use SetBasedApproach database
USE SetBasedApproach
GO
--Create the table EmployeeLoanDetail
CREATE TABLE dbo.EmployeeLoanDetail
(
EmpID INT NOT NULL,
EmpName VARCHAR(100) NOT NULL,
[Address] VARCHAR(256) NOT NULL,
LoanAmount NUMERIC(9, 2),
RepaymentTenureInMonth INT,
RepaymentStartDate DATE
)
GO
--Insert few records in the table
INSERT INTO dbo.EmployeeLoanDetail
(EmpID, EmpName, [Address], LoanAmount, RepaymentTenureInMonth, RepaymentStartDate)
SELECT 1 AS EmpID, 'Adam' AS EmpName, 'New Delhi' AS [Address],
100000 AS LoanAmount, 3 AS RepaymentTenureInMonth, '1-Feb-2016' AS RepaymentStartDate
UNION ALL SELECT 2, 'Joy', 'New Delhi', 150000, 5, '1-Mar-2016'
UNION ALL SELECT 3, 'Roy', 'New Delhi', 120000, 6, '1-Feb-2016'
UNION ALL SELECT 4, 'Mac', 'New Delhi', 160000, 8, '1-Dec-2016'
GO
--Query to get the output
SELECT
EmpID,
EmpName,
[Address],
CASE WHEN N = RepaymentTenureInMonth THEN (LoanAmount - FLOOR (LoanAmount / RepaymentTenureInMonth) * (N - 1) )
ELSE (FLOOR (LoanAmount / RepaymentTenureInMonth) ) END AS [Installment Amount],
CONVERT(VARCHAR(20), DATEADD(MONTH, (N -1), RepaymentStartDate), 106) AS [Installment Date],
CASE WHEN N = RepaymentTenureInMonth THEN 0 ELSE (LoanAmount - (FLOOR (LoanAmount / RepaymentTenureInMonth)) * N)
END AS [Remaining Balance]
FROM dbo.EmployeeLoanDetail
INNER JOIN dbo.Tally ON Tally.N <= EmployeeLoanDetail.RepaymentTenureInMonth
GO