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:

    | 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 |

Open in new window


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
jisoo411Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John_VidmarCommented:
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
				WHEN	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
		WHEN	status_dt < DATEADD(hh, -48, current_timestamp)
	) b
ORDER
BY	b.order_num
,	b.status_code
,	b.ranked
,	b.status_dt

Open in new window

0
jisoo411Author Commented:
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:

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

Open in new window


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 |

Open in new window


It looks like the union somehow overwrites all ranked values as 3 regardless of position.

Thanks,
Glen
0
PortletPaulfreelancerCommented:
| 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 |

Open in new window

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

Open in new window

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jisoo411Author Commented:
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
0
jisoo411Author Commented:
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:

| 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 |

Open in new window


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 |

Open in new window


Not sure if it's possible but it would great if it was :)

Thanks,
Glen
0
PortletPaulfreelancerCommented:
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?
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
;

Open in new window

| 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

Open in new window

0
PortletPaulfreelancerCommented:
re: "I want 3 instead of 2" thing:
well, perhaps it wasn't that hard.
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
            , COUNT(*) OVER (PARTITION BY order_num, status_code) as sample_size
      FROM YourTable
   )

SELECT
      sq.order_num
    , sq.status_code
    , sq.status_dt
    , case when sample_size = 2 and ranked = 2 then 3
           else
               ROW_NUMBER() OVER (PARTITION BY sq.order_num, sq.status_code ORDER BY sq.status_dt ASC)
      end 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
;

Open in new window

| ORDER_NUM | STATUS_CODE |                   STATUS_DT | RANKED |
|-----------|-------------|-----------------------------|--------|
|         1 |           A | June, 24 2014 10:00:00+0000 |      1 |
|         1 |           A | June, 27 2014 02:24:11+0000 |      3 |
|         2 |           A | June, 24 2014 10:00:00+0000 |      1 |
|         2 |           A | June, 24 2014 12:00:00+0000 |      2 |
|         2 |           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/cf86a/2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.