How to calculate the occurrence times of a kind of data through SQL statements

user_14040565 0
Viewed 42

I have a database with a column of status. I want to calculate the number of changes of the status between 0 and 1 through SQL statements. The data format of the database is similar to the following table:

ID    |    SourceName    |    Status    |    SourceType    |    TimeStamp
GUID_1       Jack               1            Manager            2022-01-09
GUID_2       Mark               0            Staff              2022-01-09
GUID_3       Judy               0            Teacher            2022-01-09
GUID_4       Michael            1            Student            2022-01-09
GUID_5       Judy               0            Teacher            2022-01-10
GUID_6       Judy               1            Teacher            2022-01-10
GUID_7       Mark               1            Staff              2022-01-10
GUID_8       Judy               0            Teacher            2022-01-11
GUID_9       Mark               1            Staff              2022-01-11
GUID_10      Jack               0            Manager            2022-01-11

ID column is a Random GUID. The result I expect is:

SourceName    |    Count
Jack               1
Judy               2
Mark               1
Michael            0

Can SQL statements be implemented? If I can, how can I achieve it?

sql-server sql
1 Answers

You can use LEAD in a subquery to determine the next value. You then filter out the rows to when the current status is different from the next status.

To get the count, you can use a conditional sum (IIF or CASE). If the next status isn't null it gets counted.

Sample:

SELECT x.SourceName
  , SUM(IIF(NextStatus IS NOT NULL, 1, 0)) cnt
FROM (
  SELECT SourceName
    , Status
    , LEAD(Status) OVER(PARTITION BY SourceName ORDER BY Timestamp)  
  NextStatus
  FROM States
 ) x
WHERE x.NextStatus IS NULL OR x.Status <> x.NextStatus
GROUP BY x.SourceName

SQLFiddle

user_6398290
Related