How to apply rank when fetching data from table using sql query?
-
Hi Everyone,
I need your help to get the required results. I want to get a machine status every 5 minutes. here how to apply rank as the sequence number for given resultset. the Rank should be a mentioned snapshot.
Thanks in advance for your support
Sample Script
select Starttime,Endtime,Status as machineStatus,datediff(minute,starttime,Endtime)as diff ,DENSE_RANK() OVER( ORDER BY starttime asc) as Sort from
( SELECT 'Run' AS Status, '11/7/2019 7:05:00 AM' AS Starttime, '11/7/2019 7:10:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:10:00 AM' AS Starttime, '11/7/2019 7:15:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:15:00 AM' AS Starttime, '11/7/2019 7:20:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:20:00 AM' AS Starttime, '11/7/2019 7:25:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:25:00 AM' AS Starttime, '11/7/2019 7:30:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:30:00 AM' AS Starttime, '11/7/2019 7:35:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:35:00 AM' AS Starttime, '11/7/2019 7:40:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 7:40:00 AM' AS Starttime, '11/7/2019 7:45:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 7:50:00 AM' AS Starttime, '11/7/2019 7:55:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 7:55:00 AM' AS Starttime, '11/7/2019 8:00:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:00:00 AM' AS Starttime, '11/7/2019 8:05:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:05:00 AM' AS Starttime, '11/7/2019 8:10:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:10:00 AM' AS Starttime, '11/7/2019 8:15:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:15:00 AM' AS Starttime, '11/7/2019 8:20:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:20:00 AM' AS Starttime, '11/7/2019 8:25:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:25:00 AM' AS Starttime, '11/7/2019 8:30:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:30:00 AM' AS Starttime, '11/7/2019 8:35:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:35:00 AM' AS Starttime, '11/7/2019 8:40:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 8:40:00 AM' AS Starttime, '11/7/2019 8:45:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 8:45:00 AM' AS Starttime, '11/7/2019 8:50:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 8:50:00 AM' AS Starttime, '11/7/2019 8:55:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 8:55:00 AM' AS Starttime, '11/7/2019 9:00:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 9:00:00 AM' AS Starttime, '11/7/2019 9:05:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 9:05:00 AM' AS Starttime, '11/7/2019 9:10:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:10:00 AM' AS Starttime, '11/7/2019 9:15:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:15:00 AM' AS Starttime, '11/7/2019 9:20:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:20:00 AM' AS Starttime, '11/7/2019 9:25:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:25:00 AM' AS Starttime, '11/7/2019 9:30:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 9:30:00 AM' AS Starttime, '11/7/2019 9:35:00 AM' AS Endtime
) abc -
What you are showing in your example isn't a true rank. Essentially it is just grouping similar status codes, incrementing an integer each time the status code changes. You can get the results you illustrate with the following code, but I don't know of a function in SQL to do it elegantly.
-- populate source table (in real life, it is assumed to already exist)
with cte as
( SELECT 'Run' AS Status, '11/7/2019 7:05:00 AM' AS Starttime, '11/7/2019 7:10:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:10:00 AM' AS Starttime, '11/7/2019 7:15:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:15:00 AM' AS Starttime, '11/7/2019 7:20:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:20:00 AM' AS Starttime, '11/7/2019 7:25:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:25:00 AM' AS Starttime, '11/7/2019 7:30:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:30:00 AM' AS Starttime, '11/7/2019 7:35:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 7:35:00 AM' AS Starttime, '11/7/2019 7:40:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 7:40:00 AM' AS Starttime, '11/7/2019 7:45:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 7:50:00 AM' AS Starttime, '11/7/2019 7:55:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 7:55:00 AM' AS Starttime, '11/7/2019 8:00:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:00:00 AM' AS Starttime, '11/7/2019 8:05:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:05:00 AM' AS Starttime, '11/7/2019 8:10:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:10:00 AM' AS Starttime, '11/7/2019 8:15:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:15:00 AM' AS Starttime, '11/7/2019 8:20:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:20:00 AM' AS Starttime, '11/7/2019 8:25:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:25:00 AM' AS Starttime, '11/7/2019 8:30:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:30:00 AM' AS Starttime, '11/7/2019 8:35:00 AM' AS Endtime
UNION ALL
SELECT 'Idle' AS Status, '11/7/2019 8:35:00 AM' AS Starttime, '11/7/2019 8:40:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 8:40:00 AM' AS Starttime, '11/7/2019 8:45:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 8:45:00 AM' AS Starttime, '11/7/2019 8:50:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 8:50:00 AM' AS Starttime, '11/7/2019 8:55:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 8:55:00 AM' AS Starttime, '11/7/2019 9:00:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 9:00:00 AM' AS Starttime, '11/7/2019 9:05:00 AM' AS Endtime
UNION ALL
SELECT 'Stop' AS Status, '11/7/2019 9:05:00 AM' AS Starttime, '11/7/2019 9:10:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:10:00 AM' AS Starttime, '11/7/2019 9:15:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:15:00 AM' AS Starttime, '11/7/2019 9:20:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:20:00 AM' AS Starttime, '11/7/2019 9:25:00 AM' AS Endtime
UNION ALL
SELECT 'Error' AS Status, '11/7/2019 9:25:00 AM' AS Starttime, '11/7/2019 9:30:00 AM' AS Endtime
UNION ALL
SELECT 'Run' AS Status, '11/7/2019 9:30:00 AM' AS Starttime, '11/7/2019 9:35:00 AM' AS Endtime
)
select Starttime,Endtime, [Status]
into #source
from cte
create clustered index clix_#source on #source(StartTime)
-- identify group boundaries
;with cte2 as (
select Starttime,Endtime, [Status] as MachineStatus
,datediff(minute,starttime,Endtime)as diff
,DENSE_RANK() OVER( ORDER BY starttime asc) as Sort
,Case when isnull([Status],-1) <> isnull(lag([Status]) over(Order by StartTime),0) then 1
end as Breakpoint
from #source)
select identity(int,1,1) as GroupID, *
into #groups
from cte2
where Breakpoint = 1
create clustered index clix_#groups on #groups(GroupID desc);
select * from #groups
-- retrieve appropriate groupID ("rank") based on what range the StartTime falls into
select s.*
,datediff(minute,starttime,Endtime)as diff
,DENSE_RANK() OVER( ORDER BY starttime asc) as Sort
,GroupID as [Rank]
from #source s
cross apply (select top (1) GroupID from #Groups g where g.Starttime <= s.StartTime order by StartTime desc) ca__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
This gives the same results but doesn't require creating a second temp table.
WITH status_ranges AS
(
SELECT
s.Starttime
,s.Endtime
,s.Status
,CASE WHEN s.Status = LAG(s.Status) OVER(ORDER BY s.Starttime) THEN 0 ELSE 1 END AS is_change
FROM #source AS s
)
SELECT
sr.Starttime
,sr.Endtime
,sr.Status
,DATEDIFF(MINUTE, sr.Starttime, sr.Endtime) AS diff
,SUM(sr.is_change) OVER(ORDER BY sr.Starttime ROWS UNBOUNDED PRECEDING) AS status_rank
FROM status_ranges sr;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
You were written a script suitable for my requirements. Thank you so much Drew for a quick solution.
Thank you Dixie for your support and written script for my query
-
You're welcome. And I admire Drew's solution. Just couldn't dredge one as elegant up.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply