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?
 
PortletPaulConnect With a Mentor Commented:
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PortletPaulCommented:
| 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
 
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
 
PortletPaulCommented:
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
All Courses

From novice to tech pro — start learning today.