CODEWITHSUNDEEP
t-sqlrank
J.Pyz
J.Pyz

Reputation: 233

SQL Grouping Data by Intervals

All,

I am looking for a scale-able way to "bucket" these staff IDs into either a "Left" or "Right" dimension for reporting purposes. I need to group the first 3 distinct IDs into the left group, the next 3 into the right group and so on, next 3 into the left group, and so on. The actual data set contains hundreds of IDs.

Thanks

Raw data:

Rank    Faculty_Staff_ID
----------------------------
1       zcrm_315216
1       zcrm_315216
1       zcrm_315216
2       zcrm_315217
2       zcrm_315217
2       zcrm_315217
3       zcrm_315218
4       zcrm_315219
4       zcrm_315219
4       zcrm_315219
5       zcrm_319795
5       zcrm_319795
6       zcrm_315220
6       zcrm_315220
7       zcrm_315221
8       zcrm_315222
9       zcrm_315223
9       zcrm_315223
9       zcrm_315223

Desired output:

L_or_R    Rank    Faculty_Staff_ID
----------------------------------
L         1       zcrm_315216
L         1       zcrm_315216
L         1       zcrm_315216
L         2       zcrm_315217
L         2       zcrm_315217
L         2       zcrm_315217
L         3       zcrm_315218
R         4       zcrm_315219
R         4       zcrm_315219
R         4       zcrm_315219
R         5       zcrm_319795
R         5       zcrm_319795
R         6       zcrm_315220
R         6       zcrm_315220
L         7       zcrm_315221
L         8       zcrm_315222
L         9       zcrm_315223
L         9       zcrm_315223
L         9       zcrm_315223

Upvotes: 2

Views: 46

Answers (2)

Andrei Odegov
Andrei Odegov

Reputation: 3439

To solve this problem, there is enough the DENSE_RANK function and a bit of arithmetic.

CREATE TABLE T(
    Rank INT,
    Faculty_Staff_ID VARCHAR(50)
);

INSERT INTO T VALUES
  (1,'zcrm_315216'),
  (1,'zcrm_315216'),
  (1,'zcrm_315216'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (2,'zcrm_315217'),
  (3,'zcrm_315218'),
  (4,'zcrm_315219'),
  (4,'zcrm_315219'),
  (4,'zcrm_315219'),
  (5,'zcrm_319795'),
  (5,'zcrm_319795'),
  (6,'zcrm_315220'),
  (6,'zcrm_315220'),
  (7,'zcrm_315221'),
  (8,'zcrm_315222'),
  (10,'zcrm_315223'),
  (21,'zcrm_315223'),
  (23,'zcrm_315223'),
  (25,'zcrm_315223'),
  (25,'zcrm_315223'),
  (27,'zcrm_315223');

SELECT *,
  IIF(((DENSE_RANK() OVER (ORDER BY Rank) - 1) / 3) % 2 = 0, 'L', 'R') L_or_R
FROM T
ORDER BY Rank;

Welcome here to check.

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

You can follow

  1. Make a RowNumber on Rank column number.

  2. Let Rank group by 3 using CASE WHEN on the subquery.

  3. Use CASE WHEN on main query grp % 2 = 0 to split L and R

You can try this query.

SELECT t.*,(CASE WHEN grp % 2 = 0 then 'R' ELSE 'L' END) 'L_or_R'
FROM T t
INNER JOIN (
    SELECT rnk,SUM(CASE WHEN (rn -1)% 3 = 0 THEN 1 ELSE 0 END) OVER(ORDER BY rn) grp
    FROM (
       SELECT rnk,ROW_NUMBER() OVER(ORDER BY rnk) rn 
       FROM 
       (
          SELECT DISTINCT Rank rnk
          FROM T
        )t
    ) t
) t1 on t.Rank = t1.rnk

sqlfiddle:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=615c015a856b57511a2dcf0323f0d4a5

Upvotes: 2

Related Questions