Forum Discussion

Martin_Kevin's avatar
Martin_Kevin
Copper Contributor
Aug 11, 2024

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

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Aug 12, 2024

    Martin_Kevin 

    1. Find 'Active' row as dataset CA;

    2. Find ’Max‘ row as dataset CM;

    3. Join CA and CM, calculate columns you need.

    SELECT CA.RuleID, CA.ActivePeriod Ruleperiod,
  CONVERT(DATE, CA.ActiveDate) RuleDate_of_Active,
  CONVERT(DATE, CM.MaxDate) RuleDate_Max,
  CM.MaxPeriod - CA.ActivePeriod Ruleperiod_Left, 
  DATEDIFF(month, CA.ActiveDate, CM.MaxDate) RuleDate_Left_Round_Month 
FROM
  (
    SELECT RuleID, MAX(Ruleperiod) ActivePeriod, MAX(RuleDate) ActiveDate 
    FROM #Classic 
    WHERE Rulestatus = 'Active' 
    GROUP BY RuleID
  ) CA
  INNER JOIN
  (
    SELECT RuleID, MAX(Ruleperiod) MaxPeriod, MAX(RuleDate) MaxDate  
    FROM #Classic
    GROUP BY RuleID
  ) CM
  ON 
    CA.RuleID=CM.RuleID

Resources