CODEWITHSUNDEEP
sqlvbams-access
user13851309
user13851309

Reputation: 79

Get the following record in query

If we have a table called Activity and has rows[ActivityCode and StartTime] for example

ActivityCode-----StartTime<BR>
Lunch------------1200<BR>
MathClass--------1300<BR>
EnglishClass-----1500<BR>
EndOfSchool------1700<BR>

And now I want to make one SQL Query to display as follow:

ActivityCode-----StartTime-----EndTime<BR>
Lunch------------1200----------1300<BR>
MathClass--------1300----------1500<BR>
EnglishClass-----1500----------1700<BR>
EndOfSchool------1700----------1700<BR>

I am not sure how to do it. I tried to follow How to get a value from previous result row of a SELECT statement?. But it didn't work as I expected. Any help is appreciated.

Upvotes: 1

Views: 154

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would use a subquery with aggregation:

select a.*,
       (select nz(min(a2.starttime), a.endtime)
        from activity as a2
        where a2.starttime > a.starttime
       ) as endtime
from activity as a;

Normally in such an example, there would be an additional column identifying a "grouping" of some sort -- such as a person. If you have such a column, you would have an equality condition in the subquery as well as the inequality on time.

Also, there are much better ways to do this in almost any other database -- notably, the lead() function.

Upvotes: 1

Gustav
Gustav

Reputation: 55816

You can use this query:

SELECT 
    Activity.ActivityCode, 
    Activity.StartTime, 
    Nz((Select Top 1 StartTime 
        From Activity As T 
        Where T.StartTime > Activity.StartTime 
        Order By StartTime Asc),
        [StartTime]) AS EndTime, 
    CDate(TimeSerial(Val([EndTime])\100,Val([EndTime]) Mod 100,0)-
        TimeSerial(Val([StartTime])\100,Val([StartTime]) Mod 100,0)) AS Duration
FROM 
    Activity;

Output:

Output

Upvotes: 1

Related Questions