FInd specific rows and use data

Transact-SQL
1

Hi,

I hope someone can help me with this nasty problem.

This is a little result of a view. This is a dataset with duties of driver over a certain period. A driver cab start in the morning or in the evening around 22:00. So the time often pass the 00:00.

driverID is part of the condition

  • Find the first record with activity signin. If found get date and time
  • search with the given date and time the last record with activity sign out and get date and time
  • everything between, including signin and sign out, date and time signin and date and time sign out is 1 driver duty
  • the result of the query must show activity signin as the first record following by several activities en the last recods must be the activity signout

How can I do this?

2

Something like this perhaps:

select b.*
  from (select a.driverid
              ,a.activityid as from_id
              ,min(b.activityid) as to_id
          from yourtable as a
               inner join yourtable as b
                       on b.driverid=a.driverid
                      and b.activityid=213
                      and b.id>a.id
         where a.activityid=39
         group by a.driverid
                 ,a.activityid
       ) as a
       inner join yourtable as b
               on b.driverid=a.driverid
              and b.id>=a.from_id
              and b.id<=a.to_id
;
3

Hi Bitsmed, thank you for your reply. The problem is that some activities will registered before or after de 2 main events. So working with the ID's is not going to work.

What I have done now is created a dataset of all the records with the begin en end activity. The next step is to find the first activity en the last activity. Get the date and time of these activities and use them in a another query.

How can I collect the first activity 'signin' and the belonging activity 'signout'? These two records will be the duty of a driver.

4

Oh, didn't notice you wanted to use date/time. The following query uses exact same principle as before, with the exception of merging the date and time fields:

with cte(id,driverid,startdatetime,enddatetime,activityid,activity)
  as (select id
            ,driverid
            ,dateadd(dd,datediff(dd,'19000101',startdate),cast(starttime as datetime)) as startdatetime
            ,dateadd(dd,datediff(dd,'19000101',enddate),cast(endtime as datetime)) as enddatetime
            ,activityid
            ,activity
        from yourtable
     )
select b.*
  from (select a.driverid
              ,a.startdatetime
              ,min(b.enddatetime) as enddatetime
          from cte as a
               inner join cte as b
                       on b.driverid=a.driverid
                      and b.activityid=213
                      and b.startdatetime>a.startdatetime
         where a.activityid=39
         group by a.driverid
                 ,a.startdatetime
       ) as a
       inner join cte as b
               on b.driverid=a.driverid
              and b.startdatetime<=a.enddatetime
              and b.enddatetime>=a.startdatetime
;

Hope this brings you closer to a solution.

5

I've got some code in my head, but without sample data I can't test it. A picture of data is useless. I need INSERT statements at least (and preferably a CREATE TABLE too).

1 Like
6

Hi Bitsmed,

Again thanks for your reply. I'll try your solution.

Regards,

Ronald

7

Hi ScottFletcher,

Thank you for your reply.

I'm working on an existing database of a software system. I can sent you a .bak file of the database. But you can't work with it without explanation I think.

Regards Ronald

8

If you can just convert the data in the picture into INSERT statements, that would be good enough.