Solved

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

Posted on 2014-07-22
7
48 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 48

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

752 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