How To Count SQL Based On A DateTime And An Other Variable?

I have a SQL table with failed testresults: Run Test DateTime 1 20 2020-01-01 00:01 1 20 2020-01-01 00:

Solution 1:

Basically, you want row_number() and a window count():

select t.*, 
    row_number() over(partition by run, test order by datetime) rn,
    count(*) over(partition by run, test) cnt
from mytable t

From there on, you can generate the "status" with string functions:

select t.*, 
    replicate('x', rn - 1) + 'X' + replicate('x', cnt - rn) status
from (
    select t.*, 
        row_number() over(partition by run, test order by datetime) rn,
        count(*) over(partition by run, test) cnt
    from mytable t
) t

Demo on DB Fiddle:

Run | Test | DateTime         | rn | cnt | status
--: | ---: | :--------------- | -: | --: | :-----
  1 |   20 | 2020-01-01 00:00 |  1 |   3 | Xxx   
  1 |   20 | 2020-01-01 00:01 |  2 |   3 | xXx   
  1 |   20 | 2020-01-01 00:04 |  3 |   3 | xxX   
  2 |   21 | 2020-01-01 00:03 |  1 |   2 | Xx    
  2 |   21 | 2020-01-01 00:10 |  2 |   2 | xX    

Solution 2:

This seems like a weird requirement, but I think this does what you want:

select stuff(replicate('x', count(*) over (partition by run, test)),
             row_number() over (partition by run, test order by starttime), 1, 'X'
            ) as status
from t;

This uses string operations to construct the status string.