In this video, we are going to learn;
- How we can Generate rows dynamically for each record of a table in set based approach, and
- 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
[Total: 0 Average: 0/5]