TSQL Put same rank/row number on same column A but increment once column A changes

Question

I have this table and I want to put a rank/row number to the rows with the following condition

Same IO will have same rank/row number
Rank/row number will increment once IO in next row is different from previous row.

Table is sorted by AccessDate then AccessTime and grouped by UserID

I'm using this query but the output is not what I want it to be.

SELECT
    ATTCHECKINOUT.UserID,
    AccessDate = CAST(CheckTime as date),
    AccessTime = CAST(CheckTime as time),
    CheckType, 
    Ranking = DENSE_RANK() over(partition by ATTCHECKINOUT.UserID, CAST(CheckTime as date) order by CheckType)
FROM ATTCHECKINOUT INNER JOIN ATTUSER ON ATTCHECKINOUT.UserID=ATTUSER.UserID   
WHERE CheckTime BETWEEN '1/6/2017' AND '1/10/2017' AND ATTUSER.BadgeNumber = 10311
ORDER BY ATTCHECKINOUT.UserID, CheckTime

Output of query

UserID |AccessDate |AccessTime |IO |Ranking
8394   |2017-01-06 |11:24:10   |I  |1
8394   |2017-01-06 |11:24:15   |I  |1
8394   |2017-01-06 |13:05:13   |O  |2
8394   |2017-01-06 |13:05:18   |O  |2
8394   |2017-01-06 |13:34:17   |I  |1
8394   |2017-01-06 |13:34:20   |I  |1
8394   |2017-01-06 |20:35:01   |O  |2
8394   |2017-01-06 |20:35:05   |O  |2
8394   |2017-01-07 |10:59:49   |I  |1
8394   |2017-01-07 |10:59:52   |I  |1
8394   |2017-01-07 |17:02:54   |O  |2
8394   |2017-01-07 |17:29:39   |I  |1
8394   |2017-01-07 |17:29:43   |I  |1
8394   |2017-01-07 |20:13:02   |O  |2
8394   |2017-01-07 |20:13:05   |O  |2
8394   |2017-01-09 |08:32:38   |I  |1
8394   |2017-01-09 |08:32:45   |I  |1
8394   |2017-01-09 |20:02:52   |O  |2
8394   |2017-01-09 |20:02:55   |O  |2

I want the output to be like this

UserID |AccessDate |AccessTime |IO |Ranking
8394   |2017-01-06 |11:24:10   |I  |1
8394   |2017-01-06 |11:24:15   |I  |1
8394   |2017-01-06 |13:05:13   |O  |2
8394   |2017-01-06 |13:05:18   |O  |2
8394   |2017-01-06 |13:34:17   |I  |3
8394   |2017-01-06 |13:34:20   |I  |3
8394   |2017-01-06 |20:35:01   |O  |4
8394   |2017-01-06 |20:35:05   |O  |4
8394   |2017-01-07 |10:59:49   |I  |1
8394   |2017-01-07 |10:59:52   |I  |1
8394   |2017-01-07 |17:02:54   |O  |2
8394   |2017-01-07 |17:29:39   |I  |3
8394   |2017-01-07 |17:29:43   |I  |3
8394   |2017-01-07 |20:13:02   |O  |4
8394   |2017-01-07 |20:13:05   |O  |4
8394   |2017-01-09 |08:32:38   |I  |1
8394   |2017-01-09 |08:32:45   |I  |1
8394   |2017-01-09 |20:02:52   |O  |2
8394   |2017-01-09 |20:02:55   |O  |2

this link will help you http://stackoverflow.com/questions/41949621/dense-rank-without-duplication/41949896#41949896 — Abdul Rasheed, Feb 08 '17 at 06:30
there are multiple answers with/without lag function... did u chk the second answer (with a recursive cte) ???? — Abdul Rasheed, Feb 09 '17 at 05:51
It looks like `gaps-and-islands`... Do you want to restart ranking for each `UserID` and `AccessDate`? — Vladimir Baranov, Feb 09 '17 at 11:40

Tharunkumar Reddy · Answer 1 · 2017-02-08T06:55:38.650

0

I have used your output of query result set as input. And wrote query to get expected result set. You have to change your order by clause with in dense_rank function by seeing below query.

SELECT UserID,
       [AccessDate],
       [AccessTime],
       [IO],
       Dense_rank()
         OVER(
           partition BY UserID, [AccessDate]
           ORDER BY userid, [AccessDate], Datepart(hour, [AccessTime]), Datepart(minute, [AccessTime]),[IO]) Ranking
FROM   #table1 -- this contains result set of your output query

edited Feb 08 '17 at 06:55

answered Feb 08 '17 at 06:48

Tharunkumar Reddy

2,773
18
32

problem with this is when the hour and minute part is different for the same IO, the ranking will also be different making the output incorrect... – Dennis Lim Feb 08 '17 at 08:01

user1429080 · Answer 2 · 2017-02-09T11:05:09.540

Interesting problem. I've played around with it a bit, and this is what I came up with. Not the most elegant thing I ever produced, but seems to do the right thing. I'm using a table variable @t to hold the values from your ATTCHECKINOUT table:

-- prepare test data
-- note: added some additional rows to test correctness

declare @t table (
    UserID int not null,
    CheckTime datetime not null,
    CheckType nvarchar(1) not null
)

insert into @t (UserID, CheckTime, CheckType) 
values
    (8394   ,'2017-01-06 11:24:10'   ,'I'), (8394   ,'2017-01-06 11:24:15'   ,'I'), 
    (8394   ,'2017-01-06 13:05:13'   ,'O'), (8394   ,'2017-01-06 13:05:18'   ,'O'),
    (8394   ,'2017-01-06 13:34:17'   ,'I'), (8394   ,'2017-01-06 13:34:20'   ,'I'),
    (8394   ,'2017-01-06 20:35:01'   ,'O'), (8394   ,'2017-01-06 20:35:05'   ,'O'),
    (8394   ,'2017-01-07 10:59:49'   ,'I'), (8394   ,'2017-01-07 10:59:52'   ,'I'),
    (8394   ,'2017-01-07 17:02:54'   ,'O'), (8394   ,'2017-01-07 17:29:39'   ,'I'),
    (8394   ,'2017-01-07 17:29:43'   ,'I'), (8394   ,'2017-01-07 20:13:02'   ,'O'),
    (8394   ,'2017-01-07 20:13:05'   ,'O'), (8394   ,'2017-01-07 20:14:08'   ,'O'),
    (8394   ,'2017-01-07 23:14:08'   ,'O'), (8394   ,'2017-01-09 08:32:38'   ,'I'),
    (8394   ,'2017-01-09 08:32:45'   ,'I'), (8394   ,'2017-01-09 20:02:52'   ,'O'),
    (8394   ,'2017-01-09 20:02:55'   ,'O'), (8395   ,'2017-01-06 11:24:10'   ,'I'),
    (8395   ,'2017-01-06 11:24:15'   ,'I'), (8395   ,'2017-01-06 13:05:13'   ,'O'),
    (8395   ,'2017-01-06 13:05:18'   ,'O'), (8395   ,'2017-01-06 13:09:32'   ,'O'),
    (8395   ,'2017-01-06 13:34:17'   ,'I'), (8395   ,'2017-01-06 13:34:20'   ,'I')

-- produce output

select UserID,
    cast(CheckTime as date) as AccessDate,
    cast(CheckTime as time) as AccessTime,
    CheckType,
    dense_rank() over (order by groupKey) as [rank]
from (
    select UserID, CheckTime, CheckType,
        max(newGroup) over (order by UserId, CheckTime, CheckType ROWS UNBOUNDED PRECEDING ) as groupKey
    from (
        select UserId,
            CheckTime,
            CheckType,
            case 
                when CheckType = lag(CheckType, 1, '') over (order by UserId, CheckTime) 
            then 
                null 
            else 
                row_number() over (order by UserId, CheckTime, CheckType) 
            end as newGroup
        from @t
        where CheckTime >= '2017-01-06 13:05:17.000' -- note where clause here
            and UserID = 8394                        -- note where clause here
    ) as innerSub
) as outerSub
order by UserId, CheckTime, CheckType

Output:

+--------+------------+------------------+-----------+------+
| UserID | AccessDate |    AccessTime    | CheckType | rank |
+--------+------------+------------------+-----------+------+
|   8394 | 2017-01-06 | 13:05:18.0000000 | O         |    1 |
|   8394 | 2017-01-06 | 13:34:17.0000000 | I         |    2 |
|   8394 | 2017-01-06 | 13:34:20.0000000 | I         |    2 |
|   8394 | 2017-01-06 | 20:35:01.0000000 | O         |    3 |
|   8394 | 2017-01-06 | 20:35:05.0000000 | O         |    3 |
|   8394 | 2017-01-07 | 10:59:49.0000000 | I         |    4 |
|   8394 | 2017-01-07 | 10:59:52.0000000 | I         |    4 |
|   8394 | 2017-01-07 | 17:02:54.0000000 | O         |    5 |
|   8394 | 2017-01-07 | 17:29:39.0000000 | I         |    6 |
|   8394 | 2017-01-07 | 17:29:43.0000000 | I         |    6 |
|   8394 | 2017-01-07 | 20:13:02.0000000 | O         |    7 |
|   8394 | 2017-01-07 | 20:13:05.0000000 | O         |    7 |
|   8394 | 2017-01-07 | 20:14:08.0000000 | O         |    7 |
|   8394 | 2017-01-07 | 23:14:08.0000000 | O         |    7 |
|   8394 | 2017-01-09 | 08:32:38.0000000 | I         |    8 |
|   8394 | 2017-01-09 | 08:32:45.0000000 | I         |    8 |
|   8394 | 2017-01-09 | 20:02:52.0000000 | O         |    9 |
|   8394 | 2017-01-09 | 20:02:55.0000000 | O         |    9 |
+--------+------------+------------------+-----------+------+

EDIT: Here is a new version that works on SQL Server 2008. The prepare test data is the same as before.

;with cte as (
    select UserID, CheckTime, CheckType,
        (case when sub.CheckType != sub.PrevCheckType then sub.RowNum else null end) as Indicator
    from (
        select UserID, CheckTime, CheckType, 
            ISNULL((
                select top 1 CheckType 
                from @t as innerT
                where t.UserID = innerT.UserID 
                    and t.CheckTime > innerT.CheckTime 
                order by UserID, CheckTime desc), '') as PrevCheckType
            , row_number() over (order by UserID, CheckTime, CheckType) as RowNum
        from @t as t
        where CheckTime >= '2017-01-06 13:05:17.000' -- note where clause here
            and UserID = 8394                        -- note where clause here
    ) sub
)
select UserID,
    cast(CheckTime as date) as AccessDate, 
    cast(CheckTime as time) as AccessTime, 
    CheckType,
    dense_rank() over (partition by UserID order by UserID, ISNULL(Indicator, (select top 1 Indicator from cte innerCte where outerCte.UserID = innerCte.UserID and outerCte.CheckTime > innerCte.CheckTime and Indicator is not null order by UserID, CheckTime desc))) as [Rank]
from cte outerCte
order by UserID, CheckTime, CheckType

This version can easily be modified to work also on Sql Server 2005. Just fix the prepare test data part and don't use date and time casts in the output.

thanks for the reply but unfortunately only using sql server 2008, no lag function for this version of sql — Dennis Lim, Feb 09 '17 at 01:36
@DennisLim Did you note that I have added a separate sample for Sql Server 2008? — user1429080, Feb 10 '17 at 10:09

TSQL Put same rank/row number on same column A but increment once column A changes

2 Answers2