SQL server query data according to time-CodePudding

Like this how to operate, ever give a thought or example what T ~ T

CodePudding user response:

To calculate from in to out of time, also want to be "from out to in time, as long as is not the same as the adjacent line ZT calculation to lag

CodePudding user response:

reference 1/f, kun jins kao kun jins kao response:

both to calculate "out into the" time, also want to be "from out to in time, as long as the adjacent line ZT to compute time difference is it is not the same as

Calculated between into this time is ok, it is mainly in terms of computing don't know how to do

CodePudding user response:

Get rid of adjacent into state data can also be

CodePudding user response:

There is a more troublesome,
the author
The select DATEDIFF (HH, xx1. [TIME], xx2. [TIME]) from
Xx1 (the same subquery),
(the same subquery) xx2
Where xx1. Access number=xx2. In and out of The Times and xx1. ZT='a' and xx2. ZT='into'

Son found a table in the query, the content is table B plus a list of how many times in and out of the field, can use the row_number () over (partition by ZT order by [TIME]) implementation
So you can find out "the NTH out" and "the NTH into" the difference between a few hours the

 
The select xx1. *, DATEDIFF (hh, xx1. [TIME], xx2. [TIME]) from 
(select *, row_number () over (partition by ZT order by [TIME]) in and out of the number of times the from # TA) xx1, 
(select *, row_number () over (partition by ZT order by [TIME]) in and out of the number of times the from # TA) xx2 
Where xx1. Access number=xx2. In and out of The Times and xx1. ZT='a' and xx2. ZT='into'

As for the judgment of the AB, feeling can have a try in this way, use first DATEPART (HH, [TIME]) for what TIME is the TIME field values in the table B, and then judged by between AB corresponding TIME period, to obtain the corresponding access to records, and then the in and out of the TIME difference is calculated

CodePudding user response:

refer to fifth floor kun jins kao kun jins kao response:

there is a more troublesome,
the author
The select DATEDIFF (HH, xx1. [TIME], xx2. [TIME]) from
Xx1 (the same subquery),
(the same subquery) xx2
Where xx1. Access number=xx2. In and out of The Times and xx1. ZT='a' and xx2. ZT='into'

Son found a table in the query, the content is table B plus a list of how many times in and out of the field, can use the row_number () over (partition by ZT order by [TIME]) implementation
So you can find out "the NTH out" and "the NTH into" the difference between a few hours the
 
The select xx1. *, DATEDIFF (hh, xx1. [TIME], xx2. [TIME]) from 
(select *, row_number () over (partition by ZT order by [TIME]) in and out of the number of times the from # TA) xx1, 
(select *, row_number () over (partition by ZT order by [TIME]) in and out of the number of times the from # TA) xx2 
Where xx1. Access number=xx2. In and out of The Times and xx1. ZT='a' and xx2. ZT='into' 
As for the judgment of the AB, feeling can have a try in this way, use first DATEPART (HH, [TIME]) for what TIME is the TIME field values in the table B, and then judged by between AB corresponding TIME period, to obtain the corresponding access to records, the in and out of the TIME difference is calculated and then

thank you for me to study

CodePudding user response:

Select *, datediff (minute, A.t ime, B.t ime)
From the table A
Cross the apply (select top 1 time from the table where A.n ame=name and ZT='into' and time> A.t ime order by time) as B
Where ZT='a'