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

Groups > comp.databases.ms-access > #11985 > unrolled thread

Elapsed Time Between Records

Back to article view | Back to comp.databases.ms-access

Contents

  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

#11985 — Elapsed Time Between Records

I have a table, "tblSurv", that contains a Date/Time field.

I'd like to make a query that sorts by the Date/Time field,
and creates a calculated field for the number of hours and
minutes elapsed between each pair of records.

It seems so simple, and yet, I am totally without a
solution.

-- 
croy

[toc] | [next] | [standalone]

#11986

On Monday, August 22, 2016 at 4:05:03 PM UTC-4, croy wrote:
> I have a table, "tblSurv", that contains a Date/Time field.
> 
> I'd like to make a query that sorts by the Date/Time field,
> and creates a calculated field for the number of hours and
> minutes elapsed between each pair of records.
> 
> It seems so simple, and yet, I am totally without a
> solution.
> 
> -- 
> croy

Isn't there an built in Access function called DateDiff()

[toc] | [prev] | [next] | [standalone]

#11987

Am 22.08.2016 um 22:04 schrieb croy:
> I have a table, "tblSurv", that contains a Date/Time field.
>
> I'd like to make a query that sorts by the Date/Time field,
> and creates a calculated field for the number of hours and
> minutes elapsed between each pair of records.
>
> It seems so simple, and yet, I am totally without a
> solution.
>
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.

Ulrich

[toc] | [prev] | [next] | [standalone]

#11991

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?

-- 
croy

[toc] | [prev] | [next] | [standalone]

#11999

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

[toc] | [prev] | [next] | [standalone]

#12000

On Wed, 24 Aug 2016 14:14:02 +0200, Ulrich Möller
<knobbi38@arcor.de> wrote:


>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

Ah, that got it!  Thanks.

Is there a way to put DiffDays into d:h:n format?

-- 
croy

[toc] | [prev] | [next] | [standalone]

#12001

Am 24.08.2016 um 19:52 schrieb croy:
> On Wed, 24 Aug 2016 14:14:02 +0200, Ulrich Möller
> <knobbi38@arcor.de> wrote:
>
>
>> 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
> Ah, that got it!  Thanks.
>
> Is there a way to put DiffDays into d:h:n format?
>
The easiest way is to define a vba function in a module:

Public Function ElapsedTime(EndTime As Variant, StartTime As Variant) As 
Variant
   Dim dteInterval As Date

   If IsDate(StartTime) And IsDate(EndTime) Then
     dteInterval = EndTime - StartTime
     ElapsedTime = Int(CSng(dteInterval)) & " days " _
                 & Format$(dteInterval, "hh") & " Hours " _
                 & Format(dteInterval, "nn") & " Minutes " _
                 & Format(dteInterval, "ss") & " Seconds"
   Else
     ElapsedTime = Null
   End If
End Function

and call it in the sql like this:

...DateDiff("d",PrevSurveyDate,t1.SurveyDate) AS DiffDays, 
ElapsedTime([SurveyDate],[PrevSurveyDate]) AS Elapsed

adapted from https://msdn.microsoft.com/en-us//library/office/ff197413.aspx

Ulrich

[toc] | [prev] | [next] | [standalone]

#11988

On Monday, August 22, 2016 at 9:05:03 PM UTC+1, croy wrote:
> I have a table, "tblSurv", that contains a Date/Time field.
> 
> I'd like to make a query that sorts by the Date/Time field,
> and creates a calculated field for the number of hours and
> minutes elapsed between each pair of records.
> 
> It seems so simple, and yet, I am totally without a
> solution.
> 
> -- 
> croy

Hi croy,

Could you try this......?

SELECT T.MyDateField, (select top 1 int( datediff("n",T.mydatefield,tblSurv.mydatefield)/60) & ":" & format(datediff("n",T.mydatefield,tblSurv.mydatefield) mod 60,"00") from tblSurv where tblSurv.mydatefield > T.mydatefield order by mydatefield) AS HHMM
FROM tblSurv AS T
ORDER BY T.MyDateField;

Replace MyDateField with the name of your date field in tblSurv.
The HHMM column will give the difference between the date in that row and the date in the next row.

Mike P.

23/8/16

[toc] | [prev] | [next] | [standalone]

#11990

On Tue, 23 Aug 2016 05:36:31 -0700 (PDT), Mike P
<mikewpayne@tiscali.co.uk> wrote:

>Hi croy,
>
>Could you try this......?
>
>SELECT T.MyDateField, (select top 1 int( datediff("n",T.mydatefield,tblSurv.mydatefield)/60) & ":" & format(datediff("n",T.mydatefield,tblSurv.mydatefield) mod 60,"00") from tblSurv where tblSurv.mydatefield > T.mydatefield order by mydatefield) AS HHMM
>FROM tblSurv AS T
>ORDER BY T.MyDateField;
>
>Replace MyDateField with the name of your date field in tblSurv.
>The HHMM column will give the difference between the date in that row and the date in the next row.


Thanks.  With my try on that, I get an error message I
haven't seen before:

"At most one record can be returned by this subquery."

... and then

************************
SurveyDate	HHMM   *
#Name?		#Name? *
************************

Here is my rework of your offering:

SELECT T.SurveyDate, (select top 1
int(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)/60) &
":" &
format(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)
mod 60,"00") from tblCountSurv where tblCountSurv.SurveyDate
> T.SurveyDate order by SurveyDate) AS HHMM
FROM tblCountSurv AS T
ORDER BY T.SurveyDate;

-- 
croy

[toc] | [prev] | [next] | [standalone]

#11993

On Tuesday, August 23, 2016 at 5:43:18 PM UTC+1, croy wrote:
> On Tue, 23 Aug 2016 05:36:31 -0700 (PDT), Mike P

> 
> >Hi croy,
> >
> >Could you try this......?
> >
> >SELECT T.MyDateField, (select top 1 int( datediff("n",T.mydatefield,tblSurv.mydatefield)/60) & ":" & format(datediff("n",T.mydatefield,tblSurv.mydatefield) mod 60,"00") from tblSurv where tblSurv.mydatefield > T.mydatefield order by mydatefield) AS HHMM
> >FROM tblSurv AS T
> >ORDER BY T.MyDateField;
> >
> >Replace MyDateField with the name of your date field in tblSurv.
> >The HHMM column will give the difference between the date in that row and the date in the next row.
> 
> 
> Thanks.  With my try on that, I get an error message I
> haven't seen before:
> 
> "At most one record can be returned by this subquery."
> 
> ... and then
> 
> ************************
> SurveyDate	HHMM   *
> #Name?		#Name? *
> ************************
> 
> Here is my rework of your offering:
> 
> SELECT T.SurveyDate, (select top 1
> int(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)/60) &
> ":" &
> format(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)
> mod 60,"00") from tblCountSurv where tblCountSurv.SurveyDate
> > T.SurveyDate order by SurveyDate) AS HHMM
> FROM tblCountSurv AS T
> ORDER BY T.SurveyDate;
> 
> -- 
> croy

Hi croy,

Seems OK to me. It seems that the "top 1" is not working which would explain the error message.
Are there records in tblCountSurv that have identical values for SurveyDate?
Just to prove that that is the cause could you run the query on a copy of the table with records with unique values for Survey Date?
Perhaps you could supply the table definition for tblCountSurv to see if another field could be used for uniqueness.
 
I copied your sql and it ran OK in Access on my data.

Mike P.

23/8/16

[toc] | [prev] | [next] | [standalone]

#11994

On Tue, 23 Aug 2016 10:51:55 -0700 (PDT), Mike P
<mikewpayne@tiscali.co.uk> wrote:


>Hi croy,
>
>Seems OK to me. It seems that the "top 1" is not working which would explain the error message.
>Are there records in tblCountSurv that have identical values for SurveyDate?

Ugh!  Yes there are--many, in fact.  With a new table
containing unique dates, your SQL works fine.

>Just to prove that that is the cause could you run the query on a copy of the table with records with unique values for Survey Date?
>Perhaps you could supply the table definition for tblCountSurv to see if another field could be used for uniqueness.
> 
>I copied your sql and it ran OK in Access on my data.

Thanks for your help.  I know have some understanding as to
how to do this, which is what this exercise was all about.

-- 
croy

[toc] | [prev] | [next] | [standalone]

#11996

On Tuesday, August 23, 2016 at 8:21:28 PM UTC+1, croy wrote:
> On Tue, 23 Aug 2016 10:51:55 -0700 (PDT), Mike P
> 
> 
> >Hi croy,
> >
> >Seems OK to me. It seems that the "top 1" is not working which would explain the error message.
> >Are there records in tblCountSurv that have identical values for SurveyDate?
> 
> Ugh!  Yes there are--many, in fact.  With a new table
> containing unique dates, your SQL works fine.
> 
> >Just to prove that that is the cause could you run the query on a copy of the table with records with unique values for Survey Date?
> >Perhaps you could supply the table definition for tblCountSurv to see if another field could be used for uniqueness.
> > 
> >I copied your sql and it ran OK in Access on my data.
> 
> Thanks for your help.  I know have some understanding as to
> how to do this, which is what this exercise was all about.
> 
> -- 
> croy

Thanks for the update.

Mike P.

24/8/16

[toc] | [prev] | [next] | [standalone]

#11997

Am 23.08.2016 um 19:51 schrieb Mike P:
> On Tuesday, August 23, 2016 at 5:43:18 PM UTC+1, croy wrote:
>> On Tue, 23 Aug 2016 05:36:31 -0700 (PDT), Mike P
>>> Hi croy,
>>>
>>> Could you try this......?
>>>
>>> SELECT T.MyDateField, (select top 1 int( datediff("n",T.mydatefield,tblSurv.mydatefield)/60) & ":" & format(datediff("n",T.mydatefield,tblSurv.mydatefield) mod 60,"00") from tblSurv where tblSurv.mydatefield > T.mydatefield order by mydatefield) AS HHMM
>> >FROM tblSurv AS T
>>> ORDER BY T.MyDateField;
>>>
>>> Replace MyDateField with the name of your date field in tblSurv.
>>> The HHMM column will give the difference between the date in that row and the date in the next row.
>>
>> Thanks.  With my try on that, I get an error message I
>> haven't seen before:
>>
>> "At most one record can be returned by this subquery."
>>
>> ... and then
>>
>> ************************
>> SurveyDate	HHMM   *
>> #Name?		#Name? *
>> ************************
>>
>> Here is my rework of your offering:
>>
>> SELECT T.SurveyDate, (select top 1
>> int(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)/60) &
>> ":" &
>> format(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)
>> mod 60,"00") from tblCountSurv where tblCountSurv.SurveyDate
>>> T.SurveyDate order by SurveyDate) AS HHMM
>> FROM tblCountSurv AS T
>> ORDER BY T.SurveyDate;
>>
>> -- 
>> croy
> Hi croy,
>
> Seems OK to me. It seems that the "top 1" is not working which would explain the error message.
> Are there records in tblCountSurv that have identical values for SurveyDate?
> Just to prove that that is the cause could you run the query on a copy of the table with records with unique values for Survey Date?
> Perhaps you could supply the table definition for tblCountSurv to see if another field could be used for uniqueness.
>   
> I copied your sql and it ran OK in Access on my data.
>
> Mike P.
>
> 23/8/16
The top 1 clause sometimes returns more than one line as expected, 
especially when the selected fields are not unique. So insert an 
additional  unique field to the sort order to determine the order of the 
records and the top 1 expression will work correctly.

Ulrich

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web