Help in grouping function desired output
Every RuleID has different Ruleperiod, RuleDate and Rulestatus. I want the following desired output as per below calculation.
I want to display those row only which has Rulestatus-‘Active’. Every group/RuleID has only one row of Rulestatus-‘Active’.
Calculation of RuleDate_Max : This will be the max(RuleDate).
Calculation of Ruleperiod_Left: Max(Ruleperiod)-Ruleperiod (which has Rulestatus-‘Active’). Example for RuleID-AA: 14-11=3
Calculation of RuleDate_Left_Round_Month: Max(RuleDate)-RuleDate (which has Rulestatus-‘Active’). Example for RuleID-AA: ‘2023-03-06’-‘2022-11-03’=4 months in round. Month number should be in round month.
Create table #Classic (RuleID char(10), Ruleperiod int, RuleDate datetime, Rulestatus char(10))
Insert into #Classic values (‘AA’,10, ‘2022-10-01′,’UnActive’)
Insert into #Classic values (‘AA’,11, ‘2022-11-03′,’Active’)
Insert into #Classic values (‘AA’,12, ‘2022-12-04′,’UnActive’)
Insert into #Classic values (‘AA’,13, ‘2023-01-02′,’UnActive’)
Insert into #Classic values (‘AA’,14, ‘2023-03-06′,’UnActive’)
Insert into #Classic values (‘CC’,22, ‘2023-10-02′,’UnActive’)
Insert into #Classic values (‘CC’,23, ‘2023-11-02′,’Active’)
Insert into #Classic values (‘CC’,24, ‘2023-12-03′,’UnActive’)
Insert into #Classic values (‘CC’,25, ‘2024-01-04′,’UnActive’)
Insert into #Classic values (‘DD’,22, ‘2023-10-02′,’UnActive’)
Insert into #Classic values (‘DD’,23, ‘2023-10-20′,’UnActive’)
Insert into #Classic values (‘DD’,24, ‘2023-11-01′,’Active’)
Insert into #Classic values (‘DD’,25, ‘2023-11-15′,’UnActive’)
Insert into #Classic values (‘DD’,26, ‘2023-11-28′,’UnActive’)
Desired output:
—————–
RuleID Ruleperiod RuleDate_of_Active RuleDate_Max Ruleperiod_Left RuleDate_Left_Round_Month
AA 11 2022-11-03 2023-03-06 3 4
CC 23 2023-11-02 2024-01-04 2 2
DD 24 2023-11-01 2023-11-28 2 0
Every RuleID has different Ruleperiod, RuleDate and Rulestatus. I want the following desired output as per below calculation.I want to display those row only which has Rulestatus-‘Active’. Every group/RuleID has only one row of Rulestatus-‘Active’.Calculation of RuleDate_Max : This will be the max(RuleDate).Calculation of Ruleperiod_Left: Max(Ruleperiod)-Ruleperiod (which has Rulestatus-‘Active’). Example for RuleID-AA: 14-11=3Calculation of RuleDate_Left_Round_Month: Max(RuleDate)-RuleDate (which has Rulestatus-‘Active’). Example for RuleID-AA: ‘2023-03-06’-‘2022-11-03’=4 months in round. Month number should be in round month.Create table #Classic (RuleID char(10), Ruleperiod int, RuleDate datetime, Rulestatus char(10))Insert into #Classic values (‘AA’,10, ‘2022-10-01′,’UnActive’)Insert into #Classic values (‘AA’,11, ‘2022-11-03′,’Active’)Insert into #Classic values (‘AA’,12, ‘2022-12-04′,’UnActive’)Insert into #Classic values (‘AA’,13, ‘2023-01-02′,’UnActive’)Insert into #Classic values (‘AA’,14, ‘2023-03-06′,’UnActive’) Insert into #Classic values (‘CC’,22, ‘2023-10-02′,’UnActive’)Insert into #Classic values (‘CC’,23, ‘2023-11-02′,’Active’)Insert into #Classic values (‘CC’,24, ‘2023-12-03′,’UnActive’)Insert into #Classic values (‘CC’,25, ‘2024-01-04′,’UnActive’) Insert into #Classic values (‘DD’,22, ‘2023-10-02′,’UnActive’)Insert into #Classic values (‘DD’,23, ‘2023-10-20′,’UnActive’)Insert into #Classic values (‘DD’,24, ‘2023-11-01′,’Active’)Insert into #Classic values (‘DD’,25, ‘2023-11-15′,’UnActive’)Insert into #Classic values (‘DD’,26, ‘2023-11-28′,’UnActive’)Desired output:—————–RuleID Ruleperiod RuleDate_of_Active RuleDate_Max Ruleperiod_Left RuleDate_Left_Round_MonthAA 11 2022-11-03 2023-03-06 3 4CC 23 2023-11-02 2024-01-04 2 2DD 24 2023-11-01 2023-11-28 2 0 Read More
