How to achieve chains in SQL

user_15461432 0
Viewed 77

I have an attendance sheet that appears as follows:

image here.

I am trying to chain together the attendance of people (coded as ABC, DEF, etc.). And I want the final result to appear like this: which shows the start and end dates of each segment/chain of attendance. If person A attends from 2021-01-01 to 2021-01-4, then it should show up as one row because it's one contiguous chain. I am not sure what to do or where to start. It's an odd way attendance data is saved.

final output

More info: I don't know if this matters, but I have up to 20 columns because our attendance max is 20 people. So it's up to Col20.

common-table-expression sql-server-2016 sql-server sql
1 Answers

For the record. It is best to supply sample data as text.

This is a classic Gaps-and-Islands with a little twist.

Here we dynamically unpivot your data via a bit of JSON (without actually using dynamic SQL).

Example or dbFiddle

;with cte as (
Select AttendanceDate
      ,B.*
      ,Grp = datediff(day,0,AttendanceDate) - dense_rank() over (partition by value order by AttendanceDate)
 From  YourTable A
 Cross Apply (
                Select [Key]
                      ,Value
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                 Where [Key] not in ('AttendanceDate')
             ) B
)
Select [Value]
      ,[Start]  = min(AttendanceDate)
      ,[End]    = max(AttendanceDate)
 From  cte
 Group By Value,Grp

Returns

Value   Start       End
ABC     2021-01-01  2021-01-04
DEF     2021-01-01  2021-01-03
GH      2021-01-02  2021-01-05
DEF     2021-01-07  2021-01-08
xx      2021-01-08  2021-01-10
zz      2021-01-08  2021-01-08
user_15700000
Related