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
Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.