How to increment a column like 1, 1, 2, 2, 3, 3 in SQL Server-CodePudding

I have data like this in a table:

https://i.stack.imgur.com/JYZjz.png

I want to group first two events (1, 10) as F1, than second two events (1,10) as F2

Running this SQL query:

SELECT 
    Id, Date, Event,
    CASE 
        WHEN Event = 1 AND LEAD(Event) OVER (ORDER BY ID) = 10 
            THEN 'F1'
        WHEN Event = 10 AND LAG(Event) OVER (ORDER BY ID) = 1 
            THEN 'F1'
        ELSE NULL
    END AS Flow
FROM 
    lxfr.SystemEventsDiary
WHERE 
    Event IN (1, 10)

I get this result as output:

ID	Date	Event	Flow
4	2022-02-07 00:00:00.000	1	F1
44	2022-02-08 00:00:00.000	10	F1
47	2022-02-09 00:00:00.000	1	F1
72	2022-02-10 00:00:00.000	10	F1
75	2022-02-10 00:00:00.000	1	F1
97	2022-02-11 00:00:00.000	10	F1
100	2022-02-11 00:00:00.000	1	NULL
113	2022-02-28 00:00:00.000	1	F1
217	2022-03-04 00:00:00.000	10	F1
235	2022-03-22 00:00:00.000	10	NULL
241	2022-03-22 00:00:00.000	1	F1
270	2022-03-24 00:00:00.000	10	F1
273	2022-03-24 00:00:00.000	1	F1
308	2022-03-25 00:00:00.000	10	F1

But I need a result like this:

ID	Date	Event	Flow
4	2022-02-07 00:00:00.000	1	F1
44	2022-02-08 00:00:00.000	10	F1
47	2022-02-09 00:00:00.000	1	F2
72	2022-02-10 00:00:00.000	10	F2
75	2022-02-10 00:00:00.000	1	F3
97	2022-02-11 00:00:00.000	10	F3
100	2022-02-11 00:00:00.000	1	NULL
113	2022-02-28 00:00:00.000	1	F4
217	2022-03-04 00:00:00.000	10	F4
235	2022-03-22 00:00:00.000	10	NULL
241	2022-03-22 00:00:00.000	1	F5
270	2022-03-24 00:00:00.000	10	F5
273	2022-03-24 00:00:00.000	1	F6
308	2022-03-25 00:00:00.000	10	F6

I tried with a CTE, but I'm getting errors...

Thanks

CodePudding user response：

This seems to work. I've copied your desired output as my input sample data just to show that my new column matches your Desired one, and also a couple of other renames for keywords:

declare @t table (ID int, DateOf datetime, EventNo int, DesiredFlow varchar(7))
insert into @t(ID,DateOf,EventNo,DesiredFlow) values
(4  ,'2022-02-07T00:00:00.000',1    ,'F1'),
(44 ,'2022-02-08T00:00:00.000',10   ,'F1'),
(47 ,'2022-02-09T00:00:00.000',1    ,'F2'),
(72 ,'2022-02-10T00:00:00.000',10   ,'F2'),
(75 ,'2022-02-10T00:00:00.000',1    ,'F3'),
(97 ,'2022-02-11T00:00:00.000',10   ,'F3'),
(100    ,'2022-02-11T00:00:00.000',1    ,NULL),
(113    ,'2022-02-28T00:00:00.000',1    ,'F4'),
(217    ,'2022-03-04T00:00:00.000',10   ,'F4'),
(235    ,'2022-03-22T00:00:00.000',10   ,NULL),
(241    ,'2022-03-22T00:00:00.000',1    ,'F5'),
(270    ,'2022-03-24T00:00:00.000',10   ,'F5'),
(273    ,'2022-03-24T00:00:00.000',1    ,'F6'),
(308    ,'2022-03-25T00:00:00.000',10   ,'F6')

;With Neighbours as (
    select
        *,
        LEAD(EventNo,1,NULL) OVER (ORDER BY ID) as Successor,
        LAG(EventNo,1,NULL) OVER (ORDER BY ID) as Predecessor
    from
        @t t
), NoStragglers as (
    select
        *
    from
        Neighbours
    where
        (EventNo = 1 and Successor = 10) or
        (EventNo = 10 and Predecessor = 1)
), Ordered as (
    select
        *, ROW_NUMBER() OVER (PARTITION BY EventNo ORDER BY ID) as rn
    from NoStragglers
)
select
    t.*,'F'   (CONVERT(varchar(10),o.rn)) as ActualFlow
from
    @t t
        left join
    Ordered o
        on
            t.Id = o.Id

Hopefully you can see how each CTE builds from the previous one (either by inspection or changing the final select to pull one one of the earlier CTEs instead).

Result:

ID          DateOf                  EventNo     DesiredFlow ActualFlow
----------- ----------------------- ----------- ----------- -----------
4           2022-02-07 00:00:00.000 1           F1          F1
44          2022-02-08 00:00:00.000 10          F1          F1
47          2022-02-09 00:00:00.000 1           F2          F2
72          2022-02-10 00:00:00.000 10          F2          F2
75          2022-02-10 00:00:00.000 1           F3          F3
97          2022-02-11 00:00:00.000 10          F3          F3
100         2022-02-11 00:00:00.000 1           NULL        NULL
113         2022-02-28 00:00:00.000 1           F4          F4
217         2022-03-04 00:00:00.000 10          F4          F4
235         2022-03-22 00:00:00.000 10          NULL        NULL
241         2022-03-22 00:00:00.000 1           F5          F5
270         2022-03-24 00:00:00.000 10          F5          F5
273         2022-03-24 00:00:00.000 1           F6          F6
308         2022-03-25 00:00:00.000 10          F6          F6

Which seems to match.

CodePudding user response：

Check this,

create table #temp(id int,dates datetime,Events int)

insert into #temp(id,dates,events) values
(4      ,'2022-02-07 00:00:00.000', 1   )
,(44    ,'2022-02-08 00:00:00.000', 10  )
,(47    ,'2022-02-09 00:00:00.000', 1   )
,(72    ,'2022-02-10 00:00:00.000', 10  )
,(75    ,'2022-02-10 00:00:00.000', 1   )
,(97    ,'2022-02-11 00:00:00.000', 10  )
,(100   ,'2022-02-11 00:00:00.000', 1   )
,(113   ,'2022-02-28 00:00:00.000', 1   )
,(217   ,'2022-03-04 00:00:00.000', 10  )
,(235   ,'2022-03-22 00:00:00.000', 10  )
,(241   ,'2022-03-22 00:00:00.000', 1   )
,(270   ,'2022-03-24 00:00:00.000', 10  )
,(273   ,'2022-03-24 00:00:00.000', 1   )
,(308   ,'2022-03-25 00:00:00.000', 10  )

WITH CTE
     AS (SELECT Id, 
                Dates, 
                Events,
                CASE
                    WHEN Events = 1
                         AND LEAD(Events) OVER(
                             ORDER BY ID) = 10
                    THEN id
                    WHEN Events = 10
                         AND LAG(Events) OVER(
                             ORDER BY ID) = 1
                    THEN LAG(id) OVER(
                ORDER BY ID)
                    ELSE NULL
                END AS Flow
         FROM #temp),
     CtE1
     AS (SELECT *, 
                DENSE_RANK() OVER(
                ORDER BY flow) rn
         FROM CTe
         WHERE flow IS NOT NULL
         UNION ALL
         SELECT *, 
                NULL
         FROM CTE
         WHERE flow IS NULL)
     SELECT id, 
            dates, 
            events,
            CASE
                WHEN rn IS NOT NULL
                THEN concat('F', rn)
                ELSE NULL
            END Flow
     FROM cte1
     ORDER BY id;

drop table #temp

CodePudding user response：

You can use cascading subqueries to assign a ROW_NUMBER() based on Flow values that are NOT NULL:

SELECT d.Id, 
       d.Date, 
       d.Event,
       CASE WHEN b.rn IS NULL THEN NULL 
            ELSE CONCAT('F', b.rn) 
       END AS Flow
FROM (SELECT 
        Id, 
        Date, 
        Event, 
        Flow,
        ROW_NUMBER() OVER (PARTITION BY Event ORDER BY Id ASC) as rn
   FROM (SELECT 
           Id, 
           Date, 
           Event,
           CASE WHEN Event = 1 AND LEAD(Event) OVER (ORDER BY Id) = 10 THEN 1
                WHEN Event = 10 AND LAG(Event) OVER (ORDER BY Id) = 1 THEN 1
                ELSE 0
            END as Flow
         FROM SystemEventsDiary WHERE Event IN (1, 10)
         ) a WHERE Flow = 1) b 
RIGHT JOIN SystemEventsDiary d ON b.id = d.Id
ORDER BY d.Id ASC

Result:

Id	Date	Event	Flow
4	2022-02-07 00:00:00.000	1	F1
44	2022-02-08 00:00:00.000	10	F1
47	2022-02-09 00:00:00.000	1	F2
72	2022-02-10 00:00:00.000	10	F2
75	2022-02-10 00:00:00.000	1	F3
97	2022-02-11 00:00:00.000	10	F3
100	2022-02-11 00:00:00.000	1	NULL
113	2022-02-28 00:00:00.000	1	F4
217	2022-03-04 00:00:00.000	10	F4
235	2022-03-22 00:00:00.000	10	NULL
241	2022-03-22 00:00:00.000	1	F5
270	2022-03-24 00:00:00.000	10	F5
273	2022-03-24 00:00:00.000	1	F6
308	2022-03-25 00:00:00.000	10	F6

db<>fiddle here.