Solved

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

Posted on 2014-07-22
7
39 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
  • 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now