Link to home
Start Free TrialLog in
Avatar of jisoo411
jisoo411

asked on

Rank based on datetime but skip 12 hours?

Hello Everyone,

I have a rather peculiar requirement.  I have a basic rowset with data like below:

order_num, status_code, status_dt, rank
1, 'A', '2014-06-24 00:00:00', 1
1, 'A', '2014-06-24 02:00:00', 2
1, 'A', '2014-06-25 16:24:11', 3

Open in new window


I arrived at that rowset by doing a simple row_number(), partitioning by order_num and status_code, then ordering by status_dt.  But I have a requirement that needs to skip the first 12 hours after the first row's status_dt and start considering rows of the same status_code after that:

order_num, status_code, status_dt, rank
1, 'A', '2014-06-24 00:00:00', 1
1, 'A', '2014-06-24 02:00:00', null(?)
1, 'A', '2014-06-25 16:24:11', 2

Open in new window


Obviously a plain row_number() function couldn't do this and the concept would apply to all status_codes.  I'd appreciate any suggestions as I'm out of ideas...

Thanks,
Glen
Avatar of John_Vidmar
John_Vidmar
Flag of Canada image

Place your query in a Common Table Expression (CTE), assumes you named your row_number field rn:
WITH CTE
AS
(
	-- place your query here
)
SELECT	a.*
FROM	CTE	a
JOIN	(	SELECT	order_num
		,	status_code
		,	status_dt = DATEADD(hh, 12, status_dt)
		,	rank
		FROM	CTE
		WHERE	rn = 1
	)	b	ON	a.order_num   = b.order_num
			AND	a.status_code = b.status_code
			AND	a.status_dt   > b.status_dt

Open in new window

Avatar of jisoo411
jisoo411

ASKER

Hi John,

Thanks for replying.  The CTE solution almost gets me there, when trying it out it seems to exclude the first row of each status (rank number 1)?  I see the other rows (2 to the end) though.  How would I have that earliest row included in this rowset?

Thanks!
Glen
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial