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

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions. I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.

Leave a comment

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