jisoo411
asked on
Rank based on datetime but skip 12 hours?
Hello Everyone,
I have a rather peculiar requirement. I have a basic rowset with data like below:
I arrived at that rowset by doing a simple row_number(), partitioning by order_num and status_code, then ordering by status_dt. But I have a requirement that needs to skip the first 12 hours after the first row's status_dt and start considering rows of the same status_code after that:
Obviously a plain row_number() function couldn't do this and the concept would apply to all status_codes. I'd appreciate any suggestions as I'm out of ideas...
Thanks,
Glen
I have a rather peculiar requirement. I have a basic rowset with data like below:
order_num, status_code, status_dt, rank
1, 'A', '2014-06-24 00:00:00', 1
1, 'A', '2014-06-24 02:00:00', 2
1, 'A', '2014-06-25 16:24:11', 3
I arrived at that rowset by doing a simple row_number(), partitioning by order_num and status_code, then ordering by status_dt. But I have a requirement that needs to skip the first 12 hours after the first row's status_dt and start considering rows of the same status_code after that:
order_num, status_code, status_dt, rank
1, 'A', '2014-06-24 00:00:00', 1
1, 'A', '2014-06-24 02:00:00', null(?)
1, 'A', '2014-06-25 16:24:11', 2
Obviously a plain row_number() function couldn't do this and the concept would apply to all status_codes. I'd appreciate any suggestions as I'm out of ideas...
Thanks,
Glen
ASKER
Hi John,
Thanks for replying. The CTE solution almost gets me there, when trying it out it seems to exclude the first row of each status (rank number 1)? I see the other rows (2 to the end) though. How would I have that earliest row included in this rowset?
Thanks!
Glen
Thanks for replying. The CTE solution almost gets me there, when trying it out it seems to exclude the first row of each status (rank number 1)? I see the other rows (2 to the end) though. How would I have that earliest row included in this rowset?
Thanks!
Glen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window