With SQL, I'm trying to count the changes in assigned group on a ticket- once by the certain group, once by a SUbgroup, once by one group to another.
Current data:
| Number | Group | Subgroup |
|---|---|---|
| 1 | A | A.1 |
| 1 | A | A.2 |
| 1 | B | B.1 |
| 1 | C | C.1 |
| 1 | C | C.2 |
| 1 | A | A.1 |
| 2 | A | A.1 |
| 2 | B | B.1 |
| 2 | C | C.1 |
| 2 | A | A.2 |
| 2 | B | B.2 |
Needed result 1, Number "1": Count changes by Group- 3 (A to B, B to C, C to A); Number 2 - 3
Needed result 3: Number 1: Count changes by subgroup- 5
Needed result 2: Count changes only from group A to group B - 1; Number 2= 2
How would I write this?
Tried to use this suggested query for the first result, but it doesn't work as expected:
WITH Groups_Lag AS
(
SELECT [Number], [Group], LAG([Group], 1) OVER(ORDER BY Id) AS lag_kpi
from [test].[dbo].[test2]
)
,
final as (
SELECT [Number], [Group],lag_kpi, (Case when [Group] = lag_kpi then 0 else 1 end) as check_col
FROM Groups_Lag
GROUP by [Number], [Group], lag_kpi)
select sum(check_col) -1
from final