Date time sql to find start and end time

Question

Date time sql to find start and end time

Aditi Sharma 61

I need to create pivot datetime column in such a way that when the Order column value keep increasing take the lowest value as start time and highest value as end time but once the counter reset it should create a new row for start & end time.

Sample data

   computername currentuser datetime        order   
        abc      xyz   7/5/2022 20:04:51     1   
        abc      xyz   7/5/2022 20:04:51     1   
        abc      xyz   7/6/2022 6:45:51     1   
        abc      xyz   7/6/2022 6:45:51     1   
        abc      xyz      7/6/2022 7:06:45     2   
        abc      xyz   7/6/2022 7:06:45     3   
        abc      xyz   7/6/2022 7:07:00     4   
        abc      xyz   7/6/2022 7:59:12     2   
        abc      xyz   7/6/2022 7:59:12     3   
        abc      xyz   7/6/2022 7:59:19     4   
        abc      xyz   7/6/2022 7:59:21     5   
        abc      xyz   7/6/2022 21:28:19     1   
        abc      xyz   7/6/2022 21:28:19     1   
        abc      xyz   7/6/2022 21:28:24     2   
        abc      xyz   7/6/2022 21:28:24     3   
        abc      xyz   7/6/2022 21:28:24     4   
     
     
   Expected Output    
     
          computername   currentuser  starttime              endtime   
            abc             xyz      7/5/2022 20:04:51     7/5/2022 20:04:51   
             abc            xyz      7/6/2022 6:45:51       7/6/2022 7:07:00   
            abc             xyz      7/6/2022 7:59:12        7/6/2022 7:59:21   
            abc             xyz      7/6/2022 21:28:19      7/6/2022 21:28:24

Ronen Ariely 15,206 Reputation points

2022-08-07T00:48:54.33+00:00

Can you please elaborate what is the business logic which end with this result set

How do you get to each one of the rows in the result from the source data?

I have a feeling that you based your expected result on the order that the rows were inserted to the table.
Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2022-08-07T09:36:29.92+00:00
I have a feeling that this is not your original problem. Somehow, this looks to me like something you have produced from your original source data, thinking that this might be a way to the final solution, but then you got stuck at this point. If my assumption is correct, I think it is better that you give us the starting point, because the overall solution could be completely different.

And if my assumption is wrong, that is, this is really is your source data. I would like to know more about it. For instance, what this order column represent?

I notice that the sample data includes complete duplicates:

abc xyz 7/5/2022 20:04:51 1 abc xyz 7/5/2022 20:04:51 1

Is this really correct? What does it mean?

Also:

abc xyz 7/6/2022 7:07:00 4 abc xyz 7/6/2022 7:59:12 2

Here the order is not "reset" as you say in your question, but it does fall back to a lower value. Can something like this happen? If so, what does it mean? Or was that just a mistake of yours when composing the post?

The reason I ask these questions is that I want to know this in order to be able to help you with the best solution possible.

5 answers

Your answer

Ronen Ariely 15,206 Reputation points

2022-08-07T00:48:54.33+00:00

Can you please elaborate what is the business logic which end with this result set

How do you get to each one of the rows in the result from the source data?

I have a feeling that you based your expected result on the order that the rows were inserted to the table.
Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2022-08-07T09:36:29.92+00:00

I have a feeling that this is not your original problem. Somehow, this looks to me like something you have produced from your original source data, thinking that this might be a way to the final solution, but then you got stuck at this point. If my assumption is correct, I think it is better that you give us the starting point, because the overall solution could be completely different.

And if my assumption is wrong, that is, this is really is your source data. I would like to know more about it. For instance, what this order column represent?

I notice that the sample data includes complete duplicates:

abc xyz 7/5/2022 20:04:51 1 abc xyz 7/5/2022 20:04:51 1

Is this really correct? What does it mean?

Also:

abc xyz 7/6/2022 7:07:00 4 abc xyz 7/6/2022 7:59:12 2

Here the order is not "reset" as you say in your question, but it does fall back to a lower value. Can something like this happen? If so, what does it mean? Or was that just a mistake of yours when composing the post?

The reason I ask these questions is that I want to know this in order to be able to help you with the best solution possible.

Answer 1

Try shortening the next query (if it works):

;  
with Q1 as  
(  
    select *,  
        lag([order]) over (partition by computername, currentuser, cast([datetime] as date) order by [datetime]) po,  
        lead([order]) over (partition by computername, currentuser, cast([datetime] as date) order by [datetime]) no  
    from MyTable  
),  
Q2 as  
(  
    select *,  
        case when po is null or po > [order] then [datetime] end as starttime,  
        case when no is null or [order] > no then [datetime] end as endtime  
    from Q1  
),  
Q3 as  
(  
    select *  
    from Q2  
    where starttime is not null or endtime is not null  
),  
Q4 as  
(  
    select computername, currentuser,  
        starttime,  
        lead(endtime) over (partition by computername, currentuser, cast([datetime] as date) order by [datetime]) as endtime  
    from Q3  
)  
select *  
from Q4  
where starttime is not null

Answer 2

Hi @Viorel

You query is non-deterministic - meaning that it might return different result in deferent execution.

As you know, a table is a SET of un-ordered rows and unless we explicitly "command" the server to use specific ORDER BY then the server might return the data in different order and can also use parallelism.

The problem is that in tables with small amount of rows usually the server uses single thread and returns the rows in the same order that we inserted them. This leads to the confusing of people that base their solution on specific order.

The simplest way to prove that the solution is not well (non-deterministic) is to insert the exact same sample of rows in different order. This way we can show different result based on the inserted order of the rows.

Code added as file attached

and the result when I only moved two rows to the start in test B

Test A

Test B

228842-forumdemo.txt

Answer 3

Aditi Sharma 61

This is the login time I get from vpn log files .For a particular computer and user .The order is the connection state of the user at different state during their connection process.The order column determine 1- start, 4 exit but some time the connection drop and it can start again 2 reconnections etc. This data will then used with Mac outlook logs files to determine when that person was connected and exit.

We have other table which store Mac outlook information logintime so we can find start and end time to use a between function.

Something like

Logintime between starttime and end time.

A person will login log off multiple times in a day. So if we have start and end time in each row. The comparison becomes easier

Aditi Sharma 61 Reputation points

2022-08-07T10:56:54.74+00:00

I will try both the solution and update the post soon.Thanks everyone for helping me out.
Ronen Ariely 15,206 Reputation points

2022-08-07T11:21:19.937+00:00

Hi,

I will try both the solution and update the post soon.

Which both solutions?!?

You got only one solution by @Viorel and you have my explanation why you should NOT use this solution. So basically you have no solution to test yet

Answer 4

Thank you very much for the explanation! It helps a lot to know what you are working with and it is also good to know that what looks as an accident is in fact intentional.

I did not check Viorel's solution in detail, but rather I started on my own, but I took benefit of the CREATE TABLE + INSERT that Ronen had in his script..

   CREATE TABLE T (computername VARCHAR(100),currentuser VARCHAR(100), DT datetime, TheOrder INT)  
   GO  
   TRUNCATE TABLE T  
   GO  
   INSERT T(computername, currentuser, DT, TheOrder)  
   VALUES  
   ('abc', 'xyz', CONVERT(DATETIME,'7/5/2022 20:04:51',101),1),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/5/2022 20:04:51',101),1),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 6:45:51',101),1),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 6:45:51',101),1),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:06:45',101),2),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:06:45',101),3),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:07:00',101),4),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:59:12',101),2),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:59:12',101),3),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:59:19',101),4),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 7:59:21',101),5),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 21:28:19',101),1),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 21:28:19',101),1),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 21:28:24',101),2),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 21:28:24',101),3),  
   ('abc', 'xyz', CONVERT(DATETIME,'7/6/2022 21:28:24',101),4)  
   go  
   SELECT * FROM T  
   go  
   ; WITH DistinctValues AS (  
      SELECT DISTINCT computername, currentuser, DT, TheOrder  
      FROM   T  
   ), GetPrevOrder AS (  
      SELECT computername, currentuser, DT, TheOrder,   
             LAG(DT)        OVER (PARTITION BY computername, currentuser ORDER BY DT) AS PrevDT,  
             LAG(TheOrder)  OVER (PARTITION BY computername, currentuser ORDER BY DT) AS PrevOrder,  
             LEAD(TheOrder) OVER (PARTITION BY computername, currentuser ORDER BY DT) AS NextOrder  
      FROM   DistinctValues  
   ), InterestingOnes AS (  
      SELECT computername, currentuser, DT, PrevDT  
      FROM   GetPrevOrder  
      WHERE  TheOrder = 1 OR  
             TheOrder = 2 AND PrevOrder > 2 OR   
             NextOrder IS NULL  
   ), pairing AS (  
      SELECT computername, currentuser, DT AS starttime,   
             LEAD(PrevDT) OVER (PARTITION BY computername, currentuser ORDER BY DT) AS endtime  
      FROM   InterestingOnes  
   )  
   SELECT computername, currentuser, starttime, endtime  
   FROM   pairing  
   WHERE  endtime IS NOT NULL  
   go  
   DROP TABLE T

Answer 5

create table test (computername varchar(50), currentuser varchar(50), [datetime] datetime,[order] int)  
  
insert into test (computername,currentuser,[datetime], [order]) values  
('abc','xyz','7/5/2022 20:04:51', 1 ),  
('abc','xyz','7/5/2022 20:04:51', 1 ),  
('abc','xyz','7/6/2022 6:45:51 ',1  ),  
('abc','xyz','7/6/2022 6:45:51 ',1  ),  
('abc','xyz','7/6/2022 7:06:45 ',2  ),  
('abc','xyz','7/6/2022 7:06:45 ',3  ),  
('abc','xyz','7/6/2022 7:07:00 ',4  ),  
('abc','xyz','7/6/2022 7:59:12 ',2  ),  
('abc','xyz','7/6/2022 7:59:12 ',3  ),  
('abc','xyz','7/6/2022 7:59:19 ',4  ),  
 ('abc','xyz','7/6/2022 7:59:21 ',5  ),  
('abc','xyz','7/6/2022 21:28:19', 1 ),  
('abc','xyz','7/6/2022 21:28:19', 1 ),  
('abc','xyz','7/6/2022 21:28:24', 2 ),  
('abc','xyz','7/6/2022 21:28:24', 3 ),  
('abc','xyz','7/6/2022 21:28:24', 4 )  
;with mycte as (  
select computername,currentuser, [datetime],[order]  
,row_number() over(partition by computername,currentuser order by [datetime]) rn  
,dense_rank() over(partition by computername,currentuser order by [datetime]) dnk  
,row_number() over(partition by computername,currentuser order by [datetime])   
-dense_rank() over(partition by computername,currentuser order by [datetime]) delta  
   
from test  
)  
  
,mycte2 as (  
select m.*,     
sum(coalesce( case when (m.[order]<=m2.[order] and m.dnk<>1) or m.delta =0 then 0 else null end ,m.dnk)) Over(order by m.rn desc) grp  
from mycte m left join mycte m2   
 on m.computername=m2.computername and m.currentuser=m2.currentuser  
 and m2.rn=m.rn+1 and m2.[order]>=m.[order]  
 )  
  
 Select computername,currentuser,  min([datetime]) startDT, Max([datetime]) endDT  
 from mycte2  
 group by computername,currentuser,grp   
 order by   min([datetime])   
	  
  
drop table test

Share via

Date time sql to find start and end time

5 answers

Test A

Test B

Your answer