Problem with comparing date/time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with comparing date/time

Author

Topic

ricapires
Starting Member

7 Posts

Posted - 2005-03-09 : 06:18:17

Hello! :)
This is my first post here! :)

i am trying to do a query about something but didnt got any success

i have a table that registers when a person does a login and a logout:
id -> id of the person
dt_in -> data/hour of login
dt_out -> data/hour of logout

i wanted to do a query that searchs for the id's that had a simultaneos login's

for me a simultaneos login is for example

id dt_in dt_out
11 10-02-2005 15:57:04 10-02-2005 15:59:04
11 10-02-2005 15:58:04 10-02-2005 16:57:04

in this sample, id 11 had two simultaneos login's

i have done this query but it doesnt work:

select s1.id,to_char(s1.dt_in,'DD') as Dia,count(s1.id) from tableA s1,tableB s2 where s1.dt_in<s2.dt_out
and to_char(s1.dt_in,'MM')=02
AND to_char(s1.dt_in,'YYYY')=2005
and to_char(s2.dt_out,'MM')=02
AND to_char(s2.dt_out,'YYYY')=2005
and s1.ID_PROFESSIONAL=s2.ID_PROFESSIONAL
group by to_char(s1.dt_in,'DD'),s1.id_professional

This query was done so that i could retrive from tableA the number of simultaneos login's on month 02 of year 2005.

Can anyone help me with this??

Thanks for all the help! :)

Ricardo

Kristen
Test

22859 Posts

Posted - 2005-03-09 : 06:33:19

"to_char"?

Isn't it just:


SELECT COUNT(*)
SELECT *
FROM   TableA S1
       JOIN TableB S2
            ON S2.dt_in >= S1.dt_in
           AND S2.dt_in <= S1.dt_out
           AND S2.ID <> S1.ID -- Don't match with "self"
WHERE      S1.dt_out >= '01-Feb-2005 
       AND S1.dt_in < '01-Mar-2005

?

Kristen

ricapires
Starting Member

7 Posts

Posted - 2005-03-09 : 07:02:10

i used to_char, so that i could specify the exact month and year.

i changed your query to this one that checks simultaneos login's for 10-Fev-2005

SELECT COUNT(*) FROM
(SELECT *
FROM TableA S1
JOIN TableA S2
ON S2.dt_in >= S1.dt_in
AND S2.dt_in <= S1.dt_out
AND S2.id <> S1.id
WHERE S1.dt_out >= '10-Feb-2005'
AND S1.dt_in < '10-Feb-2005')

but is returns much more elements then it should! :(

By the way,you had a TableA and TableB, but the tables must be the same.

ricapires
Starting Member

7 Posts

Posted - 2005-03-09 : 07:19:49

i checked the inner select you made and it return equals id's! :(

Kristen
Test

22859 Posts

Posted - 2005-03-09 : 07:21:38

Is to_char SQL Server? If not I'm not sure how valid any of my other suggestions are!

"By the way,you had a TableA and TableB, but the tables must be the same"

I guessed that, but your original example had two different tables ...

Note that your End Date needs to be AFTER your start date:

AND S1.dt_in < '10-Feb-2005')

should be

AND S1.dt_in < '11-Feb-2005')

How many records are there in total - few enough that you can display them and hand check which ones are "incorrect"??

I was intending that "AND S2.id <> S1.id" prevented a match on a record with itself - do you have a unique ID [i.e. a single column which is unique] within that table?

Kristen

ricapires
Starting Member

7 Posts

Posted - 2005-03-09 : 07:34:35

no, to_char was a function i made.

yes, sorry, i didnt saw that my query had two different tables. my mystake!

you are also rigth about the end date.

the number of returned values was 50, but in my database i had only 26. i manage to fix it by using this one:

SELECT COUNT(*) FROM
(SELECT DISTINCT s1.id
FROM TableA S1
JOIN TableA S2
ON S2.dt_in >= S1.dt_in
AND S2.dt_in <= S1.dt_out
AND S2.id <> S1.id
WHERE s1.dt_out='01-Feb-2005' AND s1.dt_in='01-Feb-2005' AND s1.id=14)

the query you made returned equal id's, and by using the distinct, i eliminate this problem.

thanks a lot for this! if it werent for you i wouldnt be able to solve this! :)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources