How to apply rank when fetching data from table using sql query?

  • manavairajan

    SSChasing Mays

    Points: 624

    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

    Output

     

    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
  • The Dixie Flatline

    SSC Guru

    Points: 53231

    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

  • drew.allen

    SSC Guru

    Points: 76658

    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

    • This reply was modified 3 weeks, 5 days ago by  drew.allen. Reason: Forgot to include the ROWS UNBOUNDED PRECEDING
    • This reply was modified 3 weeks, 5 days ago by  drew.allen.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • manavairajan

    SSChasing Mays

    Points: 624

    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

     

  • The Dixie Flatline

    SSC Guru

    Points: 53231

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply