Prev: how query Active Directory from sql2k server?
Next: Query LDAP user group membership from SQL Server
From: SQLWiz on 17 Feb 2005 18:05
select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
FROM table1.

Now, on this table1, time_stamp column is defined in datetime format.

Case1
When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.

Case2

When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.

Iam only interested in finding the difference in seconds. Can sbdy explain
me, if there is a way out ?
From: Steve Kass on 17 Feb 2005 19:36
select DATEDIFF(second, '2/17/2005 8:34:55AM', '2/17/2005 8:35:12AM')
AS responsetime1
select DATEDIFF(second, '2/17/2005 12:00:57 PM', '2/17/2005 12:01:01
PM') AS responsetime2

gives results of 17 and 4. The results you say you get, 0 and 1
are certainly not the right ones, but I've never seen DATEDIFF
give wrong results. Can you post a reproducible script that
gives these wrong results?

It looks like the min and max time_stamp values are not what you think
they are.

Steve Kass
Drew University

SQLWiz wrote:

>select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
>FROM table1.
>
>Now, on this table1, time_stamp column is defined in datetime format.
>
>Case1
>When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
>2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
>
>Case2
>
>When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
>is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
>
>Iam only interested in finding the difference in seconds. Can sbdy explain
>me, if there is a way out ?
>
>
From: SQLWiz on 17 Feb 2005 19:51
Hi

Iam using this query:

Select log_id, track_id, DATEDIFF(second, min (time_stamp),
max(time_stamp)) AS responsetime, convert(char, min(time_stamp), 110) as
Datevalue, convert(char, min(time_stamp), 108) as timevalue
FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
track_id like 'TN-%'and log_id in(
Select distinct log_id from xmllog_cog )group by log_id, track_id

For every log_id, there can be multiple sequence ids ..(like 1,2,3 until x..
where x can vary). Each sequence id has a timestamp attached to it. Iam
interested in finding .. for every log_id what is the time difference between
1) the timestamp @ max of sequence id (lets say 11)
2) the timestamp @ min of sequence id (will always be 1).

Can you pls suggest what could be done ?




"SQLWiz" wrote:

> select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
> FROM table1.
>
> Now, on this table1, time_stamp column is defined in datetime format.
>
> Case1
> When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
> 2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
>
> Case2
>
> When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
> is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
>
> Iam only interested in finding the difference in seconds. Can sbdy explain
> me, if there is a way out ?
From: David Gugick on 17 Feb 2005 22:18
SQLWiz wrote:
> Hi
>
> Iam using this query:
>
> Select log_id, track_id, DATEDIFF(second, min (time_stamp),
> max(time_stamp)) AS responsetime, convert(char, min(time_stamp),
> 110) as Datevalue, convert(char, min(time_stamp), 108) as timevalue
> FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
> track_id like 'TN-%'and log_id in(
> Select distinct log_id from xmllog_cog )group by log_id, track_id
>
> For every log_id, there can be multiple sequence ids ..(like 1,2,3
> until x.. where x can vary). Each sequence id has a timestamp
> attached to it. Iam interested in finding .. for every log_id what is
> the time difference between 1) the timestamp @ max of sequence id
> (lets say 11) 2) the timestamp @ min of sequence id (will always be
> 1).
>
> Can you pls suggest what could be done ?
>
>
>
>
> "SQLWiz" wrote:
>
>> select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS
>> responsetime FROM table1.
>>
>> Now, on this table1, time_stamp column is defined in datetime format.
>>
>> Case1
>> When the min(time_stamp) is 2/17/2005 8:34:55AM, and the
>> max(time_stamp) is 2/17/2005 8:35:12AM ... the responsetime is shown
>> as 0 seconds.
>>
>> Case2
>>
>> When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the
>> max(time_stamp) is 2/17/2005 12:01:01 PM ... the responsetime is
>> shown as 1 seconds.
>>
>> Iam only interested in finding the difference in seconds. Can sbdy
>> explain me, if there is a way out ?

What happens when you run that query without the datediff and use MIN
and MAX as separate columns on the datetime. What values are returned?

--
David Gugick
Imceda Software
www.imceda.com

From: Steve Kass on 17 Feb 2005 23:51
See if these work - they should be equivalent,
but I didn't check them for typos:

select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
where T1.sequence_id = (
select min(sequence_id)
from table1 as T2
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
or
T1.sequence_id = (
select max(sequence_id)
from table1 as T2
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
group by T1.log_id, T1.track_id

-- another way to write this that might be faster:

select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
where T1.sequence_id in (
select
case when i = 1 then min(sequence_id) else max(sequence_id) end
from table1 as T2
cross join (select 1 as i union all select 2) as OneTwo
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
group by T1.log_id, T1.track_id

-- or this

select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
join (
-- a table containing only the min and max
-- sequence_id values along with every
-- (log_id, track_id) pair you need info for
select
T.log_id,
T.track_id,
min(T.sequence_id) as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
where <condition>
group by
T.log_id,
T.track_id
union all
select
T.log_id,
T.track_id,
max(T.sequence_id) as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
where <condition>
group by
T.log_id,
T.track_id
) as T2
on T2.log_id = T1.log_id
and T2.track_id = T1.track_id
and T2.min_or_max_sequence_id = T1.sequence_id
-- the last condition guarantees you only get rows with
-- smallest or largest sequence_id
group by T1.log_id, T1.track_id

-- or this

select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
join (
select
T.log_id,
T.track_id,
case when i = 1 then min(T.sequence_id)
else max(T.sequence_id) end
as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
cross join (select 1 as i union all select 2) as OneTwo
where <condition>
group by
T.log_id,
T.track_id
) as T2
on T2.log_id = T1.log_id
and T2.track_id = T1.track_id
and T2.min_or_max_sequence_id = T1.sequence_id
-- the last condition guarantees you only get rows with
-- smallest or largest sequence_id
group by T1.log_id, T1.track_id

-- SK



SQLWiz wrote:

>Hi
>
>Iam using this query:
>
>Select log_id, track_id, DATEDIFF(second, min (time_stamp),
>max(time_stamp)) AS responsetime, convert(char, min(time_stamp), 110) as
>Datevalue, convert(char, min(time_stamp), 108) as timevalue
>FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
>track_id like 'TN-%'and log_id in(
>Select distinct log_id from xmllog_cog )group by log_id, track_id
>
>For every log_id, there can be multiple sequence ids ..(like 1,2,3 until x..
>where x can vary). Each sequence id has a timestamp attached to it. Iam
>interested in finding .. for every log_id what is the time difference between
>1) the timestamp @ max of sequence id (lets say 11)
>2) the timestamp @ min of sequence id (will always be 1).
>
>Can you pls suggest what could be done ?
>
>
>
>
>"SQLWiz" wrote:
>
>
>
>>select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
>>FROM table1.
>>
>>Now, on this table1, time_stamp column is defined in datetime format.
>>
>>Case1
>>When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
>>2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
>>
>>Case2
>>
>>When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
>>is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
>>
>>Iam only interested in finding the difference in seconds. Can sbdy explain
>>me, if there is a way out ?
>>
>>
 | 
Pages: 1
Prev: how query Active Directory from sql2k server?
Next: Query LDAP user group membership from SQL Server