Domo BeastMode Que

M_A

I basically want, if an employee's attendance is marked PR PH PO PN HL HLWP HCL HEL consecutive 7 days then it should be highlighted as 1, if it is marked on the 8th day again then it should show 2 if it is marked again on the 9th day then it should give 3 and so on.

I have data like

Date	Emp ID	Attendance Status
01-05-2024	909750599	PR
01-05-2024	909777952	NM
02-05-2024	909750599	PH
02-05-2024	909777952	WO
03-05-2024	909750599	PN
03-05-2024	909777952	PR
04-05-2024	909750599	PR
04-05-2024	909777952	WO
05-05-2024	909750599	PR
05-05-2024	909777952	CI
06-05-2024	909750599	PR
06-05-2024	909777952	PR
07-05-2024	909750599	PH
07-05-2024	909777952	PH
08-05-2024	909750599	PR
08-05-2024	909777952	PR
09-05-2024	909750599	PH
09-05-2024	909777952	PH
10-05-2024	909750599	PO
10-05-2024	909777952	PO
11-05-2024	909750599	PH
11-05-2024	909777952	PH
12-05-2024	909750599	PH
12-05-2024	909777952	PH
13-05-2024	909750599	WO
13-05-2024	909777952	WO
14-05-2024	909750599	PR
14-05-2024	909777952	PR
15-05-2024	909750599	PR
15-05-2024	909777952	PR
16-05-2024	909750599	PR
16-05-2024	909777952	PR

ArborRose

Using SQL, we can build the table using

CREATE TABLE attendance (

attendance_date DATE,

employee_id INT,

attendance_status VARCHAR(10)

);
INSERT INTO attendance (attendance_date, employee_id, attendance_status) VALUES

('2024-05-01', 909750599, 'PR'),

('2024-05-01', 909777952, 'NM'),

('2024-05-02', 909750599, 'PH'),

('2024-05-02', 909777952, 'WO'),

('2024-05-03', 909750599, 'PN'),

('2024-05-03', 909777952, 'PR'),

('2024-05-04', 909750599, 'PR'),

('2024-05-04', 909777952, 'WO'),

('2024-05-05', 909750599, 'PR'),

('2024-05-05', 909777952, 'CI'),

('2024-05-06', 909750599, 'PR'),

('2024-05-06', 909777952, 'PR'),

('2024-05-07', 909750599, 'PH'),

('2024-05-07', 909777952, 'PH'),

('2024-05-08', 909750599, 'PR'),

('2024-05-08', 909777952, 'PR'),

('2024-05-09', 909750599, 'PH'),

('2024-05-09', 909777952, 'PH'),

('2024-05-10', 909750599, 'PO'),

('2024-05-10', 909777952, 'PO'),

('2024-05-11', 909750599, 'PH'),

('2024-05-11', 909777952, 'PH'),

('2024-05-12', 909750599, 'PH'),

('2024-05-12', 909777952, 'PH'),

('2024-05-13', 909750599, 'WO'),

('2024-05-13', 909777952, 'WO'),

('2024-05-14', 909750599, 'PR'),

('2024-05-14', 909777952, 'PR'),

('2024-05-15', 909750599, 'PR'),

('2024-05-15', 909777952, 'PR'),

('2024-05-16', 909750599, 'PR'),

('2024-05-16', 909777952, 'PR');

Then we can create a query the count consecutive sequences and use that to identify the indicator.

WITH ranked_data AS (

SELECT

attendance_date,

employee_id,

attendance_status,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attendance_date) AS seq

FROM

attendance

WHERE

attendance_status IN ('PR', 'PH', 'PO', 'PN', 'HL', 'HLWP', 'HCL', 'HEL')

),

consecutive_days AS (

SELECT

attendance_date,

employee_id,

attendance_status,

seq,

DATEADD(DAY, -seq, attendance_date) AS grp

FROM

ranked_data

),

highlight_counts AS (

SELECT

employee_id,

attendance_date,

attendance_status,

seq,

grp,

COUNT(*) OVER (PARTITION BY employee_id, grp ORDER BY attendance_date) AS consecutive_count

FROM

consecutive_days

)

SELECT

attendance_date,

employee_id,

attendance_status,consecutive_count,

CASE

WHEN consecutive_count >= 7 THEN (consecutive_count - 1) / 7 + 1

ELSE 0

END AS highlight_indicator

FROM

highlight_counts

ORDER BY

employee_id,

attendance_date;

ArborRose

Using SQL, we can build the table using

CREATE TABLE attendance (

attendance_date DATE,

employee_id INT,

attendance_status VARCHAR(10)

);
INSERT INTO attendance (attendance_date, employee_id, attendance_status) VALUES

('2024-05-01', 909750599, 'PR'),

('2024-05-01', 909777952, 'NM'),

('2024-05-02', 909750599, 'PH'),

('2024-05-02', 909777952, 'WO'),

('2024-05-03', 909750599, 'PN'),

('2024-05-03', 909777952, 'PR'),

('2024-05-04', 909750599, 'PR'),

('2024-05-04', 909777952, 'WO'),

('2024-05-05', 909750599, 'PR'),

('2024-05-05', 909777952, 'CI'),

('2024-05-06', 909750599, 'PR'),

('2024-05-06', 909777952, 'PR'),

('2024-05-07', 909750599, 'PH'),

('2024-05-07', 909777952, 'PH'),

('2024-05-08', 909750599, 'PR'),

('2024-05-08', 909777952, 'PR'),

('2024-05-09', 909750599, 'PH'),

('2024-05-09', 909777952, 'PH'),

('2024-05-10', 909750599, 'PO'),

('2024-05-10', 909777952, 'PO'),

('2024-05-11', 909750599, 'PH'),

('2024-05-11', 909777952, 'PH'),

('2024-05-12', 909750599, 'PH'),

('2024-05-12', 909777952, 'PH'),

('2024-05-13', 909750599, 'WO'),

('2024-05-13', 909777952, 'WO'),

('2024-05-14', 909750599, 'PR'),

('2024-05-14', 909777952, 'PR'),

('2024-05-15', 909750599, 'PR'),

('2024-05-15', 909777952, 'PR'),

('2024-05-16', 909750599, 'PR'),

('2024-05-16', 909777952, 'PR');

Then we can create a query the count consecutive sequences and use that to identify the indicator.

WITH ranked_data AS (

SELECT

attendance_date,

employee_id,

attendance_status,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attendance_date) AS seq

FROM

attendance

WHERE

attendance_status IN ('PR', 'PH', 'PO', 'PN', 'HL', 'HLWP', 'HCL', 'HEL')

),

consecutive_days AS (

SELECT

attendance_date,

employee_id,

attendance_status,

seq,

DATEADD(DAY, -seq, attendance_date) AS grp

FROM

ranked_data

),

highlight_counts AS (

SELECT

employee_id,

attendance_date,

attendance_status,

seq,

grp,

COUNT(*) OVER (PARTITION BY employee_id, grp ORDER BY attendance_date) AS consecutive_count

FROM

consecutive_days

)

SELECT

attendance_date,

employee_id,

attendance_status,consecutive_count,

CASE

WHEN consecutive_count >= 7 THEN (consecutive_count - 1) / 7 + 1

ELSE 0

END AS highlight_indicator

FROM

highlight_counts

ORDER BY

employee_id,

attendance_date;