[SQL] Need help getting time between rows-VBForums
Results 1 to 3 of 3

Thread: [SQL] Need help getting time between rows

  1. #1

    Thread Starter
    Hyperactive Member half flung pie's Avatar
    Join Date
    Jun 2005
    Location
    South Carolina, USA
    Posts
    315

    [SQL] Need help getting time between rows

    I've almost got what I need already, except for one minor (yet important) detail.

    I have a table
    Code:
    SELECT [ID_NUM]
          ,[HIST_STAGE]
          ,[JOB_TIME]
      FROM [STAGE_HISTORY_TRAN]
      WHERE [ID_NUM] = '460408'
      ORDER BY [JOB_TIME]
    Which returns
    460408 04 2008-02-15 15:58:12.433
    460408 10 2008-08-28 08:54:58.953
    460408 200 2008-09-29 23:18:13.243
    460408 22 2009-03-27 11:40:41.160
    460408 25 2009-09-11 14:05:31.867

    I've written a view which gives me the time spent in each stage, but it doesn't quite finish.

    Code:
    WITH rows AS
    	(
    	SELECT *, ROW_NUMBER() OVER (ORDER BY [JOB_TIME]) as rn
    	FROM [STAGE_HISTORY_TRAN] as sh
    	WHERE ID_NUM = '460408'
    	)
    SELECT 
    	mc.[ID_NUM], 
    	mc.[HIST_STAGE] as [StageCode], 
    	mc.[JOB_TIME] as [EnteredStage], 
    	mp.[JOB_TIME] as [ExitedStage], 
    	DATEDIFF(second, mc.[JOB_TIME], mp.[JOB_TIME]) as [SecondsInStage]
    FROM rows as mc
    JOIN rows as mp
    ON mc.rn = mp.rn-1
    Returns
    460408 04 2008-02-15 15:58:12.433 2008-08-28 08:54:58.953 16822606
    460408 10 2008-08-28 08:54:58.953 2008-09-29 23:18:13.243 2816595
    460408 200 2008-09-29 23:18:13.243 2009-03-27 11:40:41.160 15423748
    460408 22 2009-03-27 11:40:41.160 2009-09-11 14:05:31.867 14523890

    Which looks good, except notice the final stage isn't being output by the view. I realize this final stage wouldn't have an ExitedStage entry or SecondsInStage, so they can either be NULL or show for GETDATE(), it doesn't really matter to me, as long as there is a row for that stage.

    I tried to simply UNION a select of that table to the end of these results, selecting only one entry and ordering by desc, but apparently you can't 'order by' inside of a union piece.

    Here's what I tried.
    Code:
    UNION (
    SELECT TOP 1
    	sht.[ID_NUM], 
    	sht.[HIST_STAGE] as [StageCode], 
    	sht.[JOB_TIME] as [EnteredStage], 
    	GETDATE() as [ExitedStage], 
    	DATEDIFF(second, sht.[JOB_TIME], GETDATE()) as [SecondsInStage]
    FROM [STAGE_HISTORY_TRAN] as sht
    WHERE sht.[ID_NUM] = '460408'
    /*ORDER BY sht.[JOB_TIME] DESC*/
    )
    But since the ORDER BY fails, it appends the first row in sht to my results from above. That's, of course, not what I want.

    Aside from simply creating another view with the order by statement built in, and then unioning that to my first statement, does anyone have any ideas on how to accomplish my goal?

    To recap, this is what I want returned:
    460408 04 2008-02-15 15:58:12.433 2008-08-28 08:54:58.953 16822606
    460408 10 2008-08-28 08:54:58.953 2008-09-29 23:18:13.243 2816595
    460408 200 2008-09-29 23:18:13.243 2009-03-27 11:40:41.160 15423748
    460408 22 2009-03-27 11:40:41.160 2009-09-11 14:05:31.867 14523890
    460408 25 2009-09-11 14:05:31.867 NULL NULL

    Base 2
    Fcnncu"Nqxgu"Lguug##

  2. #2

    Thread Starter
    Hyperactive Member half flung pie's Avatar
    Join Date
    Jun 2005
    Location
    South Carolina, USA
    Posts
    315

    Re: [SQL] Need help getting time between rows

    Kind of forgot I was supposed to be making a view... so I had to restructure things. Eventually figured it out though.
    Code:
    ALTER VIEW [StageLengths]
    AS
    WITH rows AS
    	(
    	SELECT *, ROW_NUMBER() OVER (ORDER BY [ID_NUM], [JOB_TIME]) as rn
    	FROM [STAGE_HISTORY_TRAN] as sh
    	)
    SELECT 
    	mc.[ID_NUM], 
    	mc.[HIST_STAGE] as [StageCode], 
    	mc.[JOB_TIME] as [EnteredStage], 
    	[ExitedStage] =
    	CASE 
    		WHEN mp.[JOB_TIME] < mc.[JOB_TIME] THEN NULL
    		ELSE mp.[JOB_TIME]
    	END,
    	[SecondsInStage] = 
    	CASE
    		WHEN mp.[JOB_TIME] < mc.[JOB_TIME] THEN NULL
    		ELSE DATEDIFF(second, mc.[JOB_TIME], mp.[JOB_TIME])
    	END
    FROM rows as mc
    JOIN rows as mp
    ON mc.rn = mp.rn-1

    Base 2
    Fcnncu"Nqxgu"Lguug##

  3. #3
    PowerPoster
    Join Date
    May 2002
    Posts
    24,684

    Re: [SQL] Need help getting time between rows

    IT should have been a left join rather than an inner join

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.