Showing a value periodicaly in a new column, based on another column value

Question

I would like to have a query that based on the field "Periodicity", would repeat every N rows the value True, grouped by the field "Type". I think I can explain better with an example:

By having the next table...

id  type periodicity
1   1    3
2   1    3
3   1    3
4   1    3
5   1    3
6   1    3
7   1    3
8   1    3
9   1    3
10  2    2
11  2    2
12  2    2
13  2    2
14  2    2
15  2    2
16  2    2

... I would like to have a query that would return something like this...

id  type periodicity    execute_operation
1   1    3              TRUE
2   1    3              FALSE
3   1    3              FALSE
4   1    3              TRUE
5   1    3              FALSE
6   1    3              FALSE
7   1    3              TRUE
8   1    3              FALSE
9   1    3              FALSE
10  2    2              TRUE
11  2    2              FALSE
12  2    2              TRUE
13  2    2              FALSE
14  2    2              TRUE
15  2    2              FALSE
16  2    2              TRUE

For the rows where the field "type" is 1, the field "execute_operation" would be marked as True every 3 rows (the value defined in the field "periodicity").

For the rows having the "type"is 2, the filed "execute_operation" would be marked as True every 2 rows.

How can I achieve this?

score 2 · Accepted Answer · answered Nov 25 '16 at 12:54

You could combine the windowed function ROW_NUMBER with the modulo operator.

Row number sequences each group (1, 2, 3, ...). Modulo returns the position within the sequence by calculating the remainder. Example:

1 Mod 3 = 1.

2 Mod 3 = 2.

3 Mod 3 = 0.

When the mod of row number over periodicity is 1 then true is returned.

WITH [Sample] AS 
    (
        -- CTE provides sample data.
        SELECT
            *
        FROM
            (
                VALUES
                    (1,  1, 3),
                    (2,  1, 3),
                    (3,  1, 3),
                    (4,  1, 3),
                    (5,  1, 3),
                    (6,  1, 3),
                    (7,  1, 3),
                    (8,  1, 3),
                    (9,  1, 3),
                    (10, 2, 2),
                    (11, 2, 2),
                    (12, 2, 2),
                    (13, 2, 2),
                    (14, 2, 2),
                    (15, 2, 2),
                    (16, 2, 2)
            ) AS x(id, [type], periodicity)
    )
SELECT
    s.id,
    s.[type],
    s.periodicity,
    CASE ROW_NUMBER() OVER (PARTITION BY  s.[type] ORDER BY s.id) % periodicity
        WHEN 1 THEN 1
        ELSE 0
    END AS execute_operation
FROM
    [Sample] AS s
ORDER BY
    s.id
;

Tharunkumar Reddy · Answer 2 · 2016-11-25T13:10:57.050

I have done like below to achieve required result Instead of '%' (mod) you can keep periodicity. And i have assumed your id column will have sequentiall numbers so i have used it because of eliminating row_number

    SELECT ID,
       TYPE,
       CASE
         WHEN RNO = 1
              AND TYPE = 1 THEN 'TRUE'
         WHEN RNO = 2
              AND TYPE = 2 THEN 'TRUE'
         ELSE 'FALSE'
       END STATUS
FROM   (SELECT ID,
               TYPE,
               CASE
                 WHEN TYPE = 1 THEN Replace(ID%3, 0, 3)
                 ELSE Replace(ID%2, 0, 2)
               END RNO
        FROM   #TABLE1)A

score 0 · Answer 3 · answered Nov 25 '16 at 13:13

You may try something like below, please replace with the appropriate column names,

update tbl_Sample set Operation = 0 --default to false

;WITH CTE
AS
(
select *
,ROW_NUMBER() over(Partition by Periodicity order by ID) 'R'
 from tbl_Sample

 )

 UPDATE CTE
 SET Operation = 1
 WHERE R%Periodicity = 1

Showing a value periodicaly in a new column, based on another column value

3 Answers3