Блог

У меня есть таблица в SQL Server, как показано ниже:

 DECLARE @ProposalTemp AS TABLE (Flag1 BIT, Flag2 BIT, Eff_From DATE)

INSERT INTO @ProposalTemp VALUES
(0, 0,  '2019-03-01'), 
(1, 0,  '2019-03-01') ,
(0, 0, '2019-03-03') ,
(1, 1,  '2019-03-05') ,
(1, 0, '2019-03-07') ,
(1, 1, '2019-03-09'); 
  

Пример: из таблицы Flag1 — это «1» от 05 / Mar / 2019 для подопечных. Флаг2 равен «1» с 09 / Mar / 2019 для подопечных.

Я хотел бы получить даты, когда они были изменены на active (1) и не изменились на ‘0’ обратно для обоих столбцов (flag1 , flag2)

Желаемый результат

Flag1_Efff_Date Flag2_Efff_Date 
 '2019-03-05' '2019-03-09' 

Вот еще один пример

 INSERT INTO @ProposalTemp VALUES 
(0, 0, '2019-03-01'), 
(1, 0, '2019-03-01') , 
(0, 0, '2019-03-03') , 
(1, 1, '2019-03-05') , 
(1, 1, '2019-03-07') , 
(1, 1, '2019-03-09'); 
  

И желаемый результат будет

    Flag1_Efff_Date    Flag2_Efff_Date
   '2019-03-05'         '2019-03-05' 
  

Я немного попробовал.. Все еще не знаю, как написать эту логику.

 SELECT 
    Flag1, Flag2, Eff_From
FROM   
    (SELECT  
         Flag1, Flag2, Eff_From,
         ROW_NUMBER() OVER (PARTITION BY Flag1, Flag2 ORDER BY Eff_From) rn
     FROM  
         @ProposalTemp) t
WHERE  
    rn = 1
  

Есть идеи?

Просто дикое предположение, поскольку вы не предоставили требуемую логику.

Судя по описанию и ожидаемому результату, похоже, что вы хотели использовать 2-й экземпляр с флагом 1

 SELECT Flag1Date    = max(case when Flag1 = 1 and r1 = 2 then Eff_From end),
       Flag2Date    = max(case when Flag2 = 1 and r2 = 2 then Eff_From end)
FROM   
(      SELECT   Flag1,Flag2,Eff_From,
                Row_number() OVER(Partition BY Flag1 ORDER BY Eff_From ) r1,
                Row_number() OVER(Partition BY Flag2 ORDER BY Eff_From ) r2
       FROM     @ProposalTemp
) t
  

РЕДАКТИРОВАТЬ: на основе вашего обновленного вопроса

 ; with cte as
(
    SELECT  Flag1,Flag2,Eff_From,
            Row_number() OVER(ORDER BY Eff_From ) rn
    FROM    @ProposalTemp
),
f1 as
(
    select  min(Eff_From) as Flag1Date
    from    cte f1
    where   f1.Flag1    = 1
    and not exists (select * from cte x where x.Flag1 <> 1 and x.rn > f1.rn)
),
f2 as
(
    select  min(Eff_From) as Flag2Date
    from    cte f2
    where   f2.Flag2    = 1
    and not exists (select * from cte x where x.Flag2 <> 1 and x.rn > f2.rn)
)
select  *
from    f1 cross join f2
  

Вы можете попробовать это.

     GO
    ;with cte as (
    select row_number() over (order by eff_from) as Slno, * from @ProposalTemp )
    , ctflg1 as (
    select * from cte where slno = (select max(slno) 1 from cte where Flag1=0))
    , ctflg2 as (
    select * from cte where slno = (select max(slno) 1 from cte where Flag2=0))
    select Flag1, Flag2, Eff_From from ctflg1
    union
    select Flag1, Flag2, Eff_From from ctflg2
    GO
  

Сначала узнайте индивидуальное значение для flag1 и flag2 с помощью cte затем используйте его в соответствии с вашими потребностями.

Ниже используются функции LAG, LEAD для получения желаемых результатов.

 DECLARE @ProposalTemp AS TABLE (Flag1 BIT, Flag2 BIT, Eff_From DATE)

INSERT INTO @ProposalTemp VALUES
(0, 0,  '2019-03-01'), 
(1, 0,  '2019-03-01') ,
(0, 0, '2019-03-03') ,
(1, 1,  '2019-03-05') ,
(1, 0, '2019-03-07') ,
(1, 1, '2019-03-09'); 

; WITH CTE_flag1flow as
(
select lag(flag1,1,null) over(order by Eff_From desc) as nex, flag1 as curr, lead(flag1,1,null) over(order by Eff_From desc) as prev, Eff_From
FROM @ProposalTemp),
CTE_flag2flow as
(
select lag(flag2,1,null) over(order by Eff_From desc) as nex, flag2 as curr, lead(flag2,1,null) over(order by Eff_From desc) as prev, Eff_From
FROM @ProposalTemp)
select * 
from 
(SELECT max(Eff_from) AS Eff_Flag1
FROM CTE_flag1Flow
WHERE curr = 1 AND prev = 0 ) AS c1
CROSS JOIN
(SELECT max(Eff_from) AS Eff_Flag2
FROM CTE_flag2Flow
WHERE curr = 1 AND prev = 0 ) AS C2