?
Solved

Rank by timestamp but include first 2 rows within 48 hours, then all others after 48?

Posted on 2014-07-22
7
Medium Priority
?
55 Views
Last Modified: 2015-04-20
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
0
Comment
Question by:jisoo411
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40212915
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
 

Author Comment

by:jisoo411
ID: 40213049
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40213527
| 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:jisoo411
ID: 40215004
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
 

Author Comment

by:jisoo411
ID: 40215319
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40215471
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40215506
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question