Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11999

Re: Elapsed Time Between Records

Show all headers | View raw

Hi Croy,

Am 23.08.2016 um 18:59 schrieb croy:
> On Tue, 23 Aug 2016 12:57:43 +0200, Ulrich Möller
> <knobbi38@arcor.de>  wrote:
>
>> >I guess you want to calculate the difference between the date of the
>> >current record and the date of the previous record. In that case you can
>> >use something like this:
>> >
>> >SELECT t1.id,
>> >
>> >        t1.TIMESTAMP,
>> >        Datediff("d", (SELECT TOP 1 TIMESTAMP
>> >                       FROM   tblsurv AS t2
>> >                       WHERE  t2.TIMESTAMP < t1.TIMESTAMP
>> >                       ORDER  BY TIMESTAMP DESC,
>> >                                 id), t1.[timestamp]) AS Differenz
> >FROM   tblsurv AS t1;
>> >
>> >In this example the subselect addresses the previous record. It should
>> >be easy to adapt this to your specific problem.
> Thanks.
>
> I tried this:
>
> SELECT t1.CountSurvId AS Expr1, t1.SurveyDate+t1.Start AS
> Expr2, DateDiff("d",(SELECT TOP 1 SurveyDate+Start
>                         FROM   tblCountSurv AS t2
>                         WHERE  t2.SurveyDate+t2.Start <
> t1.SurveyDate+Start
>                         ORDER  BY SurveyDate+Start DESC,
> CountSurvId),[t1].[SurveyDate]+[Start]) AS Differenz
> FROM tblCountSurv AS t1;
>
> .... and got results like this:
>
> Expr1	Expr2			Differenz
> 4	11/4/2006 11:00:00 AM	2.00
> 5	11/9/2006 9:00:00 AM	4.00
> 6	11/10/2006 8:49:00 AM	1.00
> 7	11/11/2006 11:10:00 AM	1.00
> 8	11/13/2006 10:30:00 AM	1.00
> 9	11/20/2006 11:00:00 AM	1.00
> 10	11/26/2006 11:00:00 AM	1.00
> 11	12/6/2006 8:30:00 AM	3.00
> 12	12/9/2006 9:00:00 AM	3.00
> 13	12/10/2006 10:34:00 AM	1.00
> 14	11/2/2006 11:04:00 AM	
> 15	11/5/2006 9:00:00 AM	1.00
>
> .... which doesn't look right to me.  Maybe the number of
> days is rounded down?
No, the days will not be rounded. I think you pick up the wrong record 
in the subselect. Without consideration of the startpoint try this sql 
to make it clearer how it works:

SELECT t1.CountSurvId, t1.SurveyDate,
     (
       SELECT TOP 1 t2.SurveyDate
       FROM tblCountSurv as t2
       WHERE t2.SurveyDate < t1.SurveyDate
       ORDER BY t2.SurveyDate desc, t2.CountSurvId
     ) AS PrevSurveyDate,
     DateDiff("d",PrevSurveyDate,t1.SurveyDate) AS DiffDays

FROM tblCountSurv AS t1
ORDER BY t1.SurveyDate;

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-22 13:04 -0700
  Re: Elapsed Time Between Records Patrick Finucane <patrickfinucanetx@gmail.com> - 2016-08-22 13:49 -0700
  Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-23 12:57 +0200
    Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 09:59 -0700
      Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 14:14 +0200
        Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-24 10:52 -0700
          Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 23:38 +0200
  Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-23 05:36 -0700
    Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 09:43 -0700
      Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-23 10:51 -0700
        Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 12:21 -0700
          Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-24 02:14 -0700
        Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 11:25 +0200

csiph-web