...sorry

Hi, my name is MANUEL. I don't speak english very well...
Today I am working with SQLServer. I would want to compare two dateTime fields for to determine if two or more customers are connected in the same moment into my applications...
For example:
Begin date of connections:
16/01/2007 16.19.59
end date of connection
16/01/2007 16.25.23 for customers 01

Begin date of connections:
16/01/2007 16.20.59
end date of connection
16/01/2007 16.24.23 for customers 02

What i have to do!!!!
I must make this control to every hour of the day
Please Help!

Hi Manuel,

If I understand you question, you are looking to write a query that shows overlapping sessions.

If you start with a list of all the distinct start times, you can then use a correlated query to look for overlapping sessions (something like):

Code Snippet

select s1.app, st, (
select count(*)
from Sessions as s2
where
s1.app = s2.app

and s1.st >= s2.StartDateTime
and s1.st < s2.StopDateTime ) as Overlaps
from (
select distinct app, StartDateTime st
from Sessions
) as s1

Note that set based solutions around this type of problem can become really slow, really quickly! A cursor based solution can scale much better. You'd have to do some testing to see which would work best for you.

Hope that helps.

Jamie

|||

Here is a query that will retrieve all the connections that are overlapping.

Code Snippet

Select Conn1.Customer, Conn1.BeginDate, Conn1.EndDate,

Conn2.Customer, Conn2.BeginDate, Conn2.EndDate, *

From Connections Conn1, Connections Conn2

Where Conn2.BeginDate Between Conn1.BeginDate And Conn1.EndDate

And Conn1.ID <> Conn2.ID

I'm assuming the table name is called 'Connections' and that there is a primary key which is called ID. Here I've joined the table on itself looking for connections that have their start time between another connection's start and end time. I added 'Conn1.ID <> Conn2.ID' so that the query does not assume that the same connection is as an overlapping one.

I hope this answers your question.

Best regards,

Sami Samir

|||

This query might help you...

Code Snippet

Select
MainThread.CustomerId Customer
,MainThread.Startdate Customer1StartAt
,MainThread.Enddate Customer2EndAt
,ParlelThread.CustomerId ParllelWith
,ParlelThread.Startdate ParllelCustomerJoinAt
,ParlelThread.Enddate ParllelCustomerLeaveAt
,DateDiff(Mi, ParlelThread.Startdate, Case When MainThread.Enddate < ParlelThread.Enddate Then MainThread.Enddate Else ParlelThread.Enddate End ) TotalMinutesParllel
from AuditLog MainThread
Join AuditLog ParlelThread On
MainThread.Enddate > ParlelThread.Startdate And
MainThread.Startdate <= ParlelThread.Startdate
And MainThread.CustomerId <> ParlelThread.CustomerId
Order By 1

|||Sorry!I don't know SQL SERVER too

I have only one table which contains:
IDCustomers
Name
BeginDate
EndDate

My really problem is to abtain the list of cutomers whitch day,mount, years is the same and

have the same hour of logon.

I ask for new excuse for my English

|||

Try this :

Substitute your actual table name in <YourTable>

select BeginDate,IDCustomers,Name,EndDate

from <YourTable>

where BeginDate in

(

select BeginDate

from <YourTable>

group by BeginDate

having count(*) > 1

)

order by BeginDate,IDCustomers

Thanks.

Naras.

|||

The following query may help you...

Code Snippet

Select
MainThread.CustomerId Customer
,MainThread.Startdate Customer1StartAt
,MainThread.Enddate Customer2EndAt
,ParlelThread.CustomerId ParllelWith
,ParlelThread.Startdate ParllelCustomerJoinAt
,ParlelThread.Enddate ParllelCustomerLeaveAt
Into #Data
from <YourTable> MainThread
Join <YourTable>ParlelThread On
MainThread.Enddate > ParlelThread.Startdate And
MainThread.Startdate <= ParlelThread.Startdate
And MainThread.CustomerId <> ParlelThread.CustomerId
Order By 1

Select Customer, Customer1StartAt, Customer2EndAt From #Data
Union
Select ParllelWith, ParllelCustomerJoinAt, ParllelCustomerLeaveAt From #Data

Drop Table #Data

|||thanks to all… I have resolved the problem with one function.
you have been fast and kind
BYE