Tuesday, February 21, 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 [dbo].[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 Quarters 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 ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog