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 has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has 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 *

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