Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

dk
Starting Member

35 Posts

Posted - 2009-01-05 : 12:07:25

Hi all,

I am very new with SQL. I have table with events of attendance. I need to make the query which will find people working at that moment when query is started. I succeeded to find all workers who have any events that day, but I want to put out the workers who have one or more events because I assume they have go in and out.
My query is:

SELECT DISTINCT USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME
FROM USERS INNER JOIN
EVENTS ON USERS.NO = EVENTS.USERNO
WHERE (EVENTS.DT BETWEEN DATEADD(hour, -DATEPART(hour, GETDATE()), GETDATE()) AND GETDATE())

ORDER BY USERS.LASTNAME

Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:09:31

did you mean this?


SELECT  USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME,COUNT(*)
FROM USERS INNER JOIN
EVENTS ON USERS.NO = EVENTS.USERNO
WHERE (EVENTS.DT BETWEEN DATEADD(hour, -1 * DATEPART(hour, GETDATE()), GETDATE()) AND GETDATE()) 
GROUP BY USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME
ORDER BY USERS.LASTNAME

dk
Starting Member

35 Posts

Posted - 2009-01-05 : 12:14:39

This way I only got all workers who have any events that day with number of their events, but I want to have only people who have only one events that day, assuming that they only got in. I hope my pure english is not the problem.
Thanks for help, but problem is still there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:19:22

[code]
SELECT USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME
FROM USERS INNER JOIN
EVENTS ON USERS.NO = EVENTS.USERNO
WHERE (EVENTS.DT BETWEEN DATEADD(hour, -1 * DATEPART(hour, GETDATE()), GETDATE()) AND GETDATE())
GROUP BY USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME
HAVING COUNT(*)=1
ORDER BY USERS.LASTNAME
[/code]

dk
Starting Member

35 Posts

Posted - 2009-01-05 : 12:23:44

Thats it. Thanks for help. How fast is the way with HAVING? Is it fast enough because the database is big?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:25:51

quote:
Originally posted by dk

Thats it. Thanks for help. How fast is the way with HAVING? Is it fast enough because the database is big?

test it and see how it performs.i dont have a sql box here to test.

dk
Starting Member

35 Posts

Posted - 2009-01-05 : 12:27:11

It's working ok. Thanks for help, you did great job.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:28:28

no problem...you're welcome

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 08:03:51

now i have more events id and don't know how to get out people having two events:

SELECT USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME, EVENTS.JOBNO, EVENT_DEF.LONG_NAME
FROM USERS INNER JOIN
EVENTS ON USERS.NO = EVENTS.USERNO INNER JOIN EVENT_DEF ON
EVENTS.EVENTID = EVENT_DEF.EVENTID

WHERE (EVENTS.DT BETWEEN DATEADD(hour, -1 * DATEPART(hour, GETDATE()), GETDATE()) AND GETDATE())
AND ((EVENTS.EVENTID = 22)
OR (EVENTS.EVENTID = 32)
OR (EVENTS.EVENTID = 33)
OR (EVENTS.EVENTID = 34)
OR (EVENTS.EVENTID = 35))
GROUP BY USERS.ID, USERS.LASTNAME, USERS.FIRSTNAME, EVENTS.JOBNO, EVENT_DEF.LONG_NAME
HAVING COUNT(*)=1
ORDER BY EVENTS.JOBNO, USERS.LASTNAME, USERS.FIRSTNAME

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 08:11:19

Your WHERE clause is flawed.
If you run the query at 3:55 pm (15:55), you will get all records that day from 0:55 am !
And miss any records dated between midnight and 0:55...

Use this

SELECT		u.ID,
		u.LastName,
		u.FirstName
FROM		Users AS u
INNER JOIN	[Events] AS e ON e.UserNo = u.[No]
WHERE		e.DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
		AND e.DT <= GETDATE()
GROUP BY	u.ID,
		u.LastName,
		u.FirstName
HAVING		COUNT(*) = 1
ORDER BY	u.LastName

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 08:15:19

[code]SELECT u.ID,
u.LastName,
u.FirstName,
e.JobNo,
d.Long_Name
FROM Users AS u
INNER JOIN [Events] AS e ON e.UserNo = u.[No]
INNER JOIN Event_Def AS d ON d.EventID = e.EventID
WHERE WHERE e.DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND e.DT <= GETDATE()
AND e.EventID IN (22, 32, 33, 34, 35)
GROUP BY u.ID,
u.LastName,
u.FirstName,
e.JobNo,
d.Long_Name
HAVING COUNT(DISTINCT e.EventID) >= 2
ORDER BY e.JobNo,
u.LastName,
u.FirstName[/code]

E 12°55'05.63"
N 56°04'39.26"

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 08:19:56

Thanks for help. How to solve problem if one user have two events in one day. Event 22 is the registration event, and 32..35 is menu choise. People can use events for menu coise as registration events, but sometimes they forget to do so, and then they get just 22 event.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:45:53

so you want to return only people with two events?

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 08:54:37

No, I want people with only one event, because two means they got in and got out, one way or another...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:56:42

then wont peso's suggestion at 01/28/2009 : 08:11:19 be enough?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 08:57:28

See post made 01/28/2009 : 08:15:19 and change >= 2 to = 1

E 12°55'05.63"
N 56°04'39.26"

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 12:00:11

Not working, still I got all events.
Example:
one choose menu A going in, and he made event 32. when he go out form work place he made registration and he made event 22. I don't want to have him on the list. I need only people who are at the work place at that moment.
Another example is if one forgot to choose his meal, then he made only event 22, and when he go out he made event 22 again.

I want to have on the list only people with one event, assuming that people with two events are out.

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 12:03:13

I am very grateful for any help...

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 12:12:44

Sorry guys. I would like to have the list with the first events from that day. That would be the best solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:32:37

quote:
Originally posted by dk

Not working, still I got all events.
Example:
one choose menu A going in, and he made event 32. when he go out form work place he made registration and he made event 22. I don't want to have him on the list. I need only people who are at the work place at that moment.
Another example is if one forgot to choose his meal, then he made only event 22, and when he go out he made event 22 again.

I want to have on the list only people with one event, assuming that people with two events are out.

wont this give you the desired result?


SELECT		u.ID,
		u.LastName,
		u.FirstName,
		e.JobNo,
		d.Long_Name
FROM		Users AS u
INNER JOIN	[Events] AS e ON e.UserNo = u.[No]
INNER JOIN	Event_Def AS d ON d.EventID = e.EventID 
WHERE		WHERE		e.DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
		AND e.DT <= GETDATE()
		AND e.EventID IN (22, 32, 33, 34, 35)
GROUP BY	u.ID,
		u.LastName,
		u.FirstName,
		e.JobNo,
		d.Long_Name
HAVING		COUNT(e.EventID) = 1
ORDER BY	e.JobNo,
		u.LastName,
		u.FirstName

dk
Starting Member

35 Posts

Posted - 2009-01-28 : 12:42:22

no, i got some people with two events. i have to find why...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources