Tuesday, 21 February 2017

How to keep only dynamic number of rolling Quarters data in a Table of SQL Server

T-SQL Query to Keep only 9 Rolling Quarters Data in a Table of 3 Years Data in SQL Server
Suppose we have Table called [Tbl_Transaction]  with Data for 3 consecutive Years of 2016,2017,2018...which are dynamic in nature, and the data is summarized by Week and Month at lower level and by Quarter and Year at higher level as follows :
2016Q1,2016Q2,2016Q3,2016Q4
2017Q1,2017Q2,2017Q3,2017Q4
2018Q1,2018Q2,2018Q3,2018Q4

USE [TAMATAM]
GO

SELECT DISTINCT [FY_QTR],[FY] FROM [dbo].[Tbl_Transaction] 
GO



Now out of this Dynamic 3 consecutive Years data, we need to Keep only latest 9 Rolling Quarters data .... ( 2016Q4,2017Q1,2017Q2,2017Q3,2017Q4,2018Q1,2018Q2,2018Q3,2018Q4..)
dynamically and need to delete the Historical Quarters which are not needed.
/*
Declaring the 2 Variables , one to store the Total no.of Quarters Count and another to store the Count of Quarters which are to Delete from the Source data as follows...
*/
DECLARE @@Total_Qtrs AS INT, @Qtrs_to_Delete as INT

SELECT @@Total_Qtrs=COUNT(DISTINCT FY_QTR) FROM [dbo].[Tbl_Transaction] 

PRINT(@@Total_Qtrs)
;

/ *
Checking whether the Source data has 9 Rolling Quarters data or Not.
If the Data has more than 9 Qurters the find the Quarters Count to delete
*/

IF(@@Total_Qtrs<9)
BEGIN
PRINT 'We Dont Have 9 Rolling Quarters Data in the Source' ;
END
ELSE
BEGIN
SET @Qtrs_to_Delete= (@@Total_Qtrs-9)
END

PRINT(@Qtrs_to_Delete)
;
--To Select the Historical Quarters Data that you want to Delete from Table
SELECT * from 
    (
        SELECT [FY_QTR],[FY], DENSE_RANK()OVER(ORDER BY FY_QTR) AS QTR_Rank             FROM [dbo].[Tbl_Transaction] 
    )S1
    WHERE S1.QTR_Rank <=@Qtrs_to_Delete
;

--Now we can Delete the Unwanted Quarters Data by Defining the above Query in CTE(Common Table Expressions)

WITH MyCTE AS
(
SELECT * from 
    (
SELECT [FY_QTR],[FY], DENSE_RANK()OVER(ORDER BY FY_QTR) AS QTR_Rank  FROM [dbo].[Tbl_Transaction] 
    )S1
WHERE S1.QTR_Rank <=@Qtrs_to_Delete
)
DELETE From MyCTE
SELECT DISTINCT [FY_QTR],[FY]  FROM [Tbl_Transaction] 
;
GO

Final Output :

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts