jisoo411
asked on
Rank by timestamp but include first 2 rows within 48 hours, then all others after 48?
Hello everyone,
I'm attempting to do a variation of a previous solution that excluded rows within 6 hours.
But in this case, I want to keep the first and second rows within a 48 hour period, exclude the rest in that period, then consider all rows after 48 hours. For example:
Partitioning by order_num and status_code then ordering by status_dt, the rank is null for anything beyond #2 within 48 hours of #1. Rank #3 appears for the first record outside of 48 hours from #1.
I've been tinkering around with the query in the 6 hour solution but haven't been able to make it work so far. Any assistance is very much appreciated.
Thanks!
Glen
I'm attempting to do a variation of a previous solution that excluded rows within 6 hours.
But in this case, I want to keep the first and second rows within a 48 hour period, exclude the rest in that period, then consider all rows after 48 hours. For example:
| ORDER_NUM | STATUS_CODE | STATUS_DT | RANKED |
|-----------|-------------|-----------------------------|--------|
| 1 | A | June, 24 2014 00:00:00+0000 | 1 |
| 1 | A | June, 24 2014 02:00:00+0000 | 2 |
| 1 | A | June, 26 2014 16:24:11+0000 | 3 |
| 11 | A | June, 24 2014 00:00:00+0000 | 1 |
| 11 | A | June, 24 2014 02:00:00+0000 | 2 |
| 11 | A | June, 24 2014 03:00:00+0000 | (null) |
| 11 | A | June, 24 2014 06:00:00+0000 | (null) |
| 11 | A | June, 24 2014 12:00:00+0000 | (null) |
| 11 | A | June, 26 2014 16:24:11+0000 | 3 |
Partitioning by order_num and status_code then ordering by status_dt, the rank is null for anything beyond #2 within 48 hours of #1. Rank #3 appears for the first record outside of 48 hours from #1.
I've been tinkering around with the query in the 6 hour solution but haven't been able to make it work so far. Any assistance is very much appreciated.
Thanks!
Glen
ASKER
Hi John,
Thanks for replying. I see you're using "when" in a couple places, I'm assuming you mean "where"? I have adjusted the code like this:
But the result gives me this:
It looks like the union somehow overwrites all ranked values as 3 regardless of position.
Thanks,
Glen
Thanks for replying. I see you're using "when" in a couple places, I'm assuming you mean "where"? I have adjusted the code like this:
SELECT b.*
FROM (
SELECT a.*
FROM ( SELECT order_num
, status_code
, status_dt
, ranked = ROW_NUMBER() OVER (PARTITION BY order_num, status_code ORDER BY status_dt ASC)
FROM YourTable
WHERE status_dt >= DATEADD(hh, -48, current_timestamp)
) a
WHERE a.ranked IN (1,2)
UNION ALL
SELECT order_num
, status_code
, status_dt
, ranked = 3
FROM YourTable
WHERE status_dt < DATEADD(hh, -48, current_timestamp)
) b
ORDER
BY b.order_num
, b.status_code
, b.ranked
, b.status_dt
But the result gives me this:
| ORDER_NUM | STATUS_CODE | STATUS_DT | RANKED |
|-----------|-------------|-----------------------------|--------|
| 1 | A | June, 24 2014 00:00:00+0000 | 3 |
| 1 | A | June, 24 2014 02:00:00+0000 | 3 |
| 1 | A | June, 26 2014 16:24:11+0000 | 3 |
| 11 | A | June, 24 2014 00:00:00+0000 | 3 |
| 11 | A | June, 24 2014 02:00:00+0000 | 3 |
| 11 | A | June, 24 2014 03:00:00+0000 | 3 |
| 11 | A | June, 24 2014 06:00:00+0000 | 3 |
| 11 | A | June, 24 2014 12:00:00+0000 | 3 |
| 11 | A | June, 26 2014 16:24:11+0000 | 3 |
It looks like the union somehow overwrites all ranked values as 3 regardless of position.
Thanks,
Glen
| ORDER_NUM | STATUS_CODE | STATUS_DT | RANKED |
|-----------|-------------|---------------------|--------|
| 1 | A | 2014-06-24 10:00:00 | 1 |
| 1 | A | 2014-06-24 12:00:00 | 2 |
| 1 | A | 2014-06-27 02:24:11 | 3 |
| 11 | A | 2014-06-24 10:00:00 | 1 |
| 11 | A | 2014-06-24 12:00:00 | 2 |
| 11 | A | 2014-06-27 02:24:11 | 3 |
Produced by:
DECLARE @hrs int
SET @hrs = 48
SELECT
order_num
, status_code
, convert(varchar,status_dt,120) status_dt
, ROW_NUMBER() OVER (PARTITION BY order_num, status_code ORDER BY status_dt ASC) AS ranked
FROM (
SELECT
order_num
, status_code
, status_dt
, CASE
WHEN status_dt < DATEADD(HOUR, @hrs, MIN(status_dt) OVER (PARTITION BY order_num, status_code))
THEN 1
ELSE 0
END AS in_hr_range
, ROW_NUMBER() OVER (PARTITION BY order_num, status_code ORDER BY status_dt ASC) AS ranked
FROM YourTable
) AS sq
WHERE (sq.in_hr_range = 1 and sq.ranked < 3)
OR in_hr_range = 0
I introduced @hrs to make it a bit more adaptable, if you don't want that just use 48 in the case expression.http://sqlfiddle.com/#!3/48ac5/2
ASKER
Hey PortletPaul,
Thanks for the reply, you seem to be pretty darn good at this :) Is it possible to keep all rows in the original recordset within this query? Meaning filling in null or something else in the ranked column. Unfortunately for me, I have to do some other calculations separate from this specific scenario but it would be nice for me to do both from the same set. If not, I could probably a left join with the original table somehow to get it.
Thanks!
Glen
Thanks for the reply, you seem to be pretty darn good at this :) Is it possible to keep all rows in the original recordset within this query? Meaning filling in null or something else in the ranked column. Unfortunately for me, I have to do some other calculations separate from this specific scenario but it would be nice for me to do both from the same set. If not, I could probably a left join with the original table somehow to get it.
Thanks!
Glen
ASKER
Just ran into something. When there's a situation where there is only the 1 row within the 48 hour period, the next row outside the 48 hour period gets a ranking of 2 instead of 3:
I'd like to have it represented like this:
Not sure if it's possible but it would great if it was :)
Thanks,
Glen
| ORDER_NUM | STATUS_CODE | STATUS_DT | RANKED |
|-----------|-------------|---------------------|--------|
| 1 | A | 2014-06-24 10:00:00 | 1 |
| 1 | A | 2014-06-24 12:00:00 | 2 |
| 1 | A | 2014-06-27 02:24:11 | 3 |
| 11 | A | 2014-06-24 10:00:00 | 1 |
| 11 | A | 2014-06-27 02:24:11 | 2 |
I'd like to have it represented like this:
| ORDER_NUM | STATUS_CODE | STATUS_DT | RANKED |
|-----------|-------------|---------------------|--------|
| 1 | A | 2014-06-24 10:00:00 | 1 |
| 1 | A | 2014-06-24 12:00:00 | 2 |
| 1 | A | 2014-06-27 02:24:11 | 3 |
| 11 | A | 2014-06-24 10:00:00 | 1 |
| 11 | A | 2014-06-27 02:24:11 | 3 |
Not sure if it's possible but it would great if it was :)
Thanks,
Glen
If we use a CTE to hold the initial calculations, we can reuse that via a 'union all' to include records with and NULL ranking.
But I don't believe I can do the "I want 3 instead of 2" thing easily - how important is that?
But I don't believe I can do the "I want 3 instead of 2" thing easily - how important is that?
DECLARE @hrs int
SET @hrs = 48
;WITH
SQ AS (
SELECT
order_num
, status_code
, status_dt
, CASE
WHEN status_dt < DATEADD(HOUR, @hrs, MIN(status_dt) OVER (PARTITION BY order_num, status_code))
THEN 1
ELSE 0
END AS in_hr_range
, ROW_NUMBER() OVER (PARTITION BY order_num, status_code ORDER BY status_dt ASC) AS ranked
FROM YourTable
)
SELECT
sq.order_num
, sq.status_code
, sq.status_dt
, ROW_NUMBER() OVER (PARTITION BY sq.order_num, sq.status_code ORDER BY sq.status_dt ASC) AS ranked
FROM sq
WHERE (sq.in_hr_range = 1 and sq.ranked < 3)
OR sq.in_hr_range = 0
UNION ALL
SELECT
sq.order_num
, sq.status_code
, sq.status_dt
, NULL AS ranked
FROM sq
WHERE NOT (
(sq.in_hr_range = 1 and sq.ranked < 3)
OR sq.in_hr_range = 0
)
ORDER BY
order_num
, status_code
, status_dt
;
| ORDER_NUM | STATUS_CODE | STATUS_DT | RANKED |
|-----------|-------------|-----------------------------|--------|
| 1 | A | June, 24 2014 10:00:00+0000 | 1 |
| 1 | A | June, 24 2014 12:00:00+0000 | 2 |
| 1 | A | June, 27 2014 02:24:11+0000 | 3 |
| 11 | A | June, 24 2014 10:00:00+0000 | 1 |
| 11 | A | June, 24 2014 12:00:00+0000 | 2 |
| 11 | A | June, 24 2014 13:00:00+0000 | (null) |
| 11 | A | June, 24 2014 16:00:00+0000 | (null) |
| 11 | A | June, 24 2014 22:00:00+0000 | (null) |
| 11 | A | June, 27 2014 02:24:11+0000 | 3 |
http://sqlfiddle.com/#!3/48ac5/7
ASKER CERTIFIED 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