CODEWITHSUNDEEP
sqlsql-server
Maddy
Maddy

Reputation: 15

SQL - returns a incorrect result

in the below table i'm trying to enter the number of times the same status repeated after another status. if the status remains the same and the event status order is sequential then the count shouldn't increase. if the status change and enters the same status again ,the count should increase

   sid status   order   num_times_status_entered_again  Expected result
    1   A       1                     1                               1
    1   A       2                     2                               1 
    1   B       3                     1                               1
    1   C       4                     1                               1
    1   A       5                     3                               2
    1   B       7                     3                               2
    1   B       8                     4                               2
    1   B       9                     5                               2
    1   C       10                    2                               2
    1   A       11                    4                               3
    1   B       13                    7                               3
    1   B       14                    8                               3
    1   B       15                    9                               3
    1   D       16                    1                               1
    1   E       17                    1                               1

    2   A       1                     1                               1
    2   A       2                     2                               1
    2   B       3                     1                               1
    2   B       4                     2                               1
    2   A       5                     3                               2

this is the query I'm using

update t
set num_times_status_entered_again  = t1.Ncount
from #tableZ t,(select status
count(1) OVER (Partition by status order by status) as Ncount
from #sub_tableZ
where status is not null
group by status, date
) t1 GROUP BY sid

Upvotes: 0

Views: 104

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

Here's a query which generates the required result, I think:

Fiddle for SQL Server

same_stat and seq_ord are derived in the xrows CTE term using CASE ... END expressions.

LAG(status) OVER (ORDER BY sid, ord) provides the status value in the previous row based on sid, ord ordering.

LAG(ord ) OVER (ORDER BY sid, ord) provides the ord value in the previous row based on sid, ord ordering, to decide if the last order value and the current order value have a gap between them.

For SQL Server:

WITH xrows AS (
        SELECT t.*
             , CASE WHEN status = LAG(status, 1, 'NA') OVER (ORDER BY sid, ord) THEN 1 ELSE 0 END AS same_stat
             , CASE WHEN ord-1  = LAG(ord   , 1, ord ) OVER (ORDER BY sid, ord) THEN 1 ELSE 0 END AS seq_ord
          FROM test AS t
     )
SELECT t.*
     , SUM(CASE WHEN same_stat = 1 AND seq_ord = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY sid, status ORDER BY ord) AS xseq
  FROM xrows AS t
 ORDER BY sid, ord
;

For MySQL:

WITH xrows AS (
        SELECT t.*
             , status = COALESCE(LAG(status) OVER (ORDER BY sid, ord) , 'NA')       AS same_stat
             , ord-1  = COALESCE(LAG(ord   ) OVER (ORDER BY sid, ord) ,  ord)       AS seq_ord
          FROM test AS t
     )
SELECT t.*
     , SUM(CASE WHEN same_stat AND seq_ord THEN 0 ELSE 1 END) OVER (PARTITION BY sid, status ORDER BY ord) AS xseq
  FROM xrows AS t
 ORDER BY sid, ord
;

The result:

+------+--------+------+-----------+---------+------+
| sid  | status | ord  | same_stat | seq_ord | xseq |
+------+--------+------+-----------+---------+------+
|    1 | A      |    1 |         0 |       0 |    1 |
|    1 | A      |    2 |         1 |       1 |    1 |
|    1 | B      |    3 |         0 |       1 |    1 |
|    1 | C      |    4 |         0 |       1 |    1 |
|    1 | A      |    5 |         0 |       1 |    2 |
|    1 | B      |    7 |         0 |       0 |    2 |
|    1 | B      |    8 |         1 |       1 |    2 |
|    1 | B      |    9 |         1 |       1 |    2 |
|    1 | C      |   10 |         0 |       1 |    2 |
|    1 | A      |   11 |         0 |       1 |    3 |
|    1 | B      |   13 |         0 |       0 |    3 |
|    1 | B      |   14 |         1 |       1 |    3 |
|    1 | B      |   15 |         1 |       1 |    3 |
|    1 | D      |   16 |         0 |       1 |    1 |
|    1 | E      |   17 |         0 |       1 |    1 |
|    2 | A      |    1 |         0 |       0 |    1 |
|    2 | A      |    2 |         1 |       1 |    1 |
|    2 | B      |    3 |         0 |       1 |    1 |
|    2 | B      |    4 |         1 |       1 |    1 |
|    2 | A      |    5 |         0 |       1 |    2 |
+------+--------+------+-----------+---------+------+

The setup:

CREATE TABLE test (
    sid         int
  , status      varchar(5)
  , ord         int
);


INSERT INTO test VALUES
  (   1 ,'A'     ,1   )
, (   1 ,'A'     ,2   )
, (   1 ,'B'     ,3   )
, (   1 ,'C'     ,4   )
, (   1 ,'A'     ,5   )
, (   1 ,'B'     ,7   )
, (   1 ,'B'     ,8   )
, (   1 ,'B'     ,9   )
, (   1 ,'C'     ,10  )
, (   1 ,'A'     ,11  )
, (   1 ,'B'     ,13  )
, (   1 ,'B'     ,14  )
, (   1 ,'B'     ,15  )
, (   1 ,'D'     ,16  )
, (   1 ,'E'     ,17  )
 
, (   2 ,'A'     ,1   )
, (   2 ,'A'     ,2   )
, (   2 ,'B'     ,3   )
, (   2 ,'B'     ,4   )
, (   2 ,'A'     ,5   )
;

Upvotes: 1

Related Questions