none
Help writing a query that selects changing group of data

    Question

  • I need help with a query, that shows my data grouped based on when the data in my location fields change.

    My data fields are

    table name - dl_cycle_log

    fields:

    unique_id int (not null, primary key)

    machine_id - int

    start_time_stamp - datetime

    end_time_stamp - datetime

    pit_name - varchar(200)

    ramp_name - varchar(200)

    pit_name and ramp_name are my location fields, they are just text.

    Second table - mw_machine

    fields:

    machine_id int (not null, primary key)

    machine_name - varchar(200)

    My data looks like this, hopefully the formatting behaves:

    machine_id machine_name start_time_stamp end_time_stamp pit_name ramp_name
    406 Dragline 1006 1/09/2013 6:00:32 1/09/2013 6:01:17 den r12
    406 Dragline 1006 1/09/2013 6:01:17 1/09/2013 6:02:59 den r12
    406 Dragline 1006 1/09/2013 6:02:59 1/09/2013 6:04:02 den r12
    406 Dragline 1006 1/09/2013 6:04:02 1/09/2013 6:04:47 den r11
    406 Dragline 1006 1/09/2013 6:04:47 1/09/2013 6:06:02 den r11
    406 Dragline 1006 1/09/2013 6:06:02 1/09/2013 6:07:02 den r11
    406 Dragline 1006 1/09/2013 6:07:02 1/09/2013 6:07:44 den r11
    406 Dragline 1006 1/09/2013 6:07:44 1/09/2013 6:09:12 den r12
    406 Dragline 1006 1/09/2013 6:09:12 1/09/2013 6:10:14 den r12
    406 Dragline 1006 1/09/2013 6:10:14 1/09/2013 6:11:13 den r12
    
    

    I want my results to be the date ranges for when the data in these location fields change.

    It should look like this:

    machine_id machine_name start_time_stamp end_time_stamp pit_name ramp_name
    406 Dragline 1006 1/09/2013 6:00:32 1/09/2013 6:04:02 den r12
    406 Dragline 1006 1/09/2013 6:04:02 1/09/2013 6:07:44 den r11
    406 Dragline 1006 1/09/2013 6:07:44 1/09/2013 6:11:13 den

    r12

    I have a stored proc query right now that isnt working, here:

    SELECT
      dbo.dl_cycle_log.machine_id
      ,dbo.mw_machine.machine_name
      ,Min(start_time_stamp) AS start_time_stamp
      ,Max(end_time_stamp) AS end_time_stamp
      ,pit_name
      ,ramp_name
     
    FROM
      dbo.dl_cycle_log INNER JOIN dbo.mw_machine ON dbo.dl_cycle_log.machine_id = dbo.mw_machine.machine_id
      
     WHERE 
      dbo.dl_cycle_log.machine_id = @mach_id
      AND 
     (dbo.dl_cycle_log.end_time_stamp > @dtStartDate AND dbo.dl_cycle_log.end_time_stamp <= @dtEndDate)
     
    GROUP BY 
     dbo.dl_cycle_log.machine_id
      ,dbo.mw_machine.machine_name
      ,dbo.dl_cycle_log.pit_name
      ,dbo.dl_cycle_log.ramp_name
     ORDER BY 
     dbo.dl_cycle_log.machine_id
      ,Min(dbo.dl_cycle_log.start_time_stamp)

    The results it gives me are here, with overlapping rows:

    machine_id machine_name start_time_stamp end_time_stamp pit_name ramp_name
    406 Dragline 1006 1/09/2013 6:00:32 1/09/2013 6:11:13 den r12
    406 Dragline 1006 1/09/2013 6:04:02 1/09/2013 6:07:44 den r11

    I know why my query isnt working, but i'm not sure how to get the data in the format that i need.

    If anyone could help get me started in the right direction it would be much appreciated.

    I should mention the data structures, tables, fields etc are not able to be changed by me. I am trying to get data out for a report.



    Wednesday, September 11, 2013 11:20 PM

Answers

  • I think i have a query that works

    select distinct pit_name
    ,ramp_name
    ,start_time_stamp = min(start_time_stamp), end_time_stamp = max(end_time_stamp)
    from
    (
        select 
    		start_time_stamp
    		,end_time_stamp
    		,pit_name
    		,ramp_name
            ,rn1 = row_number() over(order by start_time_stamp)
            ,rn2 = row_number() over(partition by pit_name, ramp_name order by start_time_stamp desc) 
        FROM
    	dbo.dl_cycle_log INNER JOIN dbo.mw_machine ON dbo.dl_cycle_log.machine_id = dbo.mw_machine.machine_id 	
    	WHERE 
    	dbo.dl_cycle_log.machine_id = @mach_id
    AND (dbo.dl_cycle_log.end_time_stamp > @dtStartDate AND dbo.dl_cycle_log.end_time_stamp <= @dtEndDate) ) t group by pit_name, ramp_name, rn1 + rn2 order by start_time_stamp

    • Marked as answer by ReportCreator Thursday, September 12, 2013 2:14 AM
    Thursday, September 12, 2013 2:14 AM

All replies

  • I think i have a query that works

    select distinct pit_name
    ,ramp_name
    ,start_time_stamp = min(start_time_stamp), end_time_stamp = max(end_time_stamp)
    from
    (
        select 
    		start_time_stamp
    		,end_time_stamp
    		,pit_name
    		,ramp_name
            ,rn1 = row_number() over(order by start_time_stamp)
            ,rn2 = row_number() over(partition by pit_name, ramp_name order by start_time_stamp desc) 
        FROM
    	dbo.dl_cycle_log INNER JOIN dbo.mw_machine ON dbo.dl_cycle_log.machine_id = dbo.mw_machine.machine_id 	
    	WHERE 
    	dbo.dl_cycle_log.machine_id = @mach_id
    AND (dbo.dl_cycle_log.end_time_stamp > @dtStartDate AND dbo.dl_cycle_log.end_time_stamp <= @dtEndDate) ) t group by pit_name, ramp_name, rn1 + rn2 order by start_time_stamp

    • Marked as answer by ReportCreator Thursday, September 12, 2013 2:14 AM
    Thursday, September 12, 2013 2:14 AM
  • Hi ReportCreator,

    Thanks for your post and sharing the solution. Your post will help others encounter the same issue. Thanks for your effort again.


    Allen Li
    TechNet Community Support

    Friday, September 13, 2013 1:20 AM
    Moderator