How to query this? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

How to query this?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-12 : 09:07:30

Hi,

I have a table which keeps entrance/exit data. Here is a sample:
ID SNO USERID POINTID DIRECTIONID FIRMID DEPARTMENTID SECTIONID OPERATIONDATETIME
1 3383 1000184 5 1 2 61 7 2008-02-01 08:13:16.000
2 3383 1000184 9 2 2 61 7 2008-02-01 11:55:54.000
3 3383 1000184 9 1 2 61 7 2008-02-01 12:50:24.000
4 3383 1000184 10 2 2 61 7 2008-02-01 17:37:04.000
5 3383 1000184 3 1 2 61 7 2008-02-04 08:40:46.000
6 3383 1000184 6 1 2 61 7 2008-02-04 08:41:42.000
7 3383 1000184 4 2 2 61 7 2008-02-04 12:00:16.000
8 3383 1000184 3 1 2 61 7 2008-02-04 12:55:34.000
9 3383 1000184 5 1 2 61 7 2008-02-04 12:56:19.000
10 3383 1000184 4 2 2 61 7 2008-02-04 17:43:40.000

DirectionID 1 = entrance
DirectionID 2 = exit
PointID is the gates (I need 4,5,6,7)
What I want is to get entrance/exit from 13:00 to 14:25 monthly?

How can I get it?

Best Regards.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-12 : 09:18:19

Would something like this work for you? You may need to change the columns in the group by clause to meet your needs:

SELECT
	YEAR(OPERATIONDATETIME) Yr,
	MONTH(OPERATIONDATETIME) Mnth,
	userid,
	COUNT(CASE WHEN directionID = 1 THEN 1 ELSE 0 END) AS Entrances,
	COUNT(CASE WHEN directionID = 2 THEN 1 ELSE 0 END) AS Exits
FROM
	Tbl
WHERE
	CAST(OPERATIONDATETIME AS TIME ) BETWEEN '13:00' AND '14:25'
GROUP BY
	YEAR(OPERATIONDATETIME),
	MONTH(OPERATIONDATETIME),
	userid

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-12 : 09:43:20

Hi,
I am getting this error:
Type TIME is not a defined system type

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-12 : 09:59:23

quote:
Originally posted by raysefo

Hi,
I am getting this error:
Type TIME is not a defined system type

What version of Microsoft SQL Server are you using? You can find using this query:

SELECT @@VERSION

If you are using a version earlier than SQL 2008, change theWHERE clause to this

WHERE
   DATEADD(dd,DATEDIFF(dd,OPERATIONDATETIME,0),OPERATIONDATETIME) BETWEEN '13:00' AND '14:25'

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-12 : 13:10:09

Hi,

I changed the code as follows and it works. But I need to change the requirement. I want to retrieve the ones which exits 13:00 and enters after lets say 75 minutes. Is there a way to do it?

SELECT
YEAR(OPERATIONDATETIME) Yr,
MONTH(OPERATIONDATETIME) Mnth,
USERID,
COUNT(CASE WHEN DIRECTIONID = 1 THEN 1 ELSE 0 END) AS Entrances,
COUNT(CASE WHEN DIRECTIONID = 2 THEN 1 ELSE 0 END) AS Exits

FROM ITE_OPERATION
WHERE CONVERT(VARCHAR,OPERATIONDATETIME,108) BETWEEN '13:00' AND '14:25'
GROUP BY
YEAR(OPERATIONDATETIME),
MONTH(OPERATIONDATETIME),
USERID

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-12 : 13:45:53

Shortly I want to get the ones more than 75 mins (entrance - exit interval)

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-12 : 16:04:19

I want to get the ones which has 75 mins in difference and more.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-13 : 08:20:03

Anybody can help?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 08:50:11

quote:
Originally posted by raysefo

Anybody can help?

I am hesitating to respond because of a few things:

a) I don't know what version of Microsoft SQL Server you are are using (or if even you are on SQL Server or some other RDBMS). You can run this command to display the version of SQL Server.

SELECT @@VERSION;

b) The problem definition is not very clear to me. In the original sample data that you posted, there are two ENTRANCES (DirectionID = 1) and eight EXITS (DirectionID = 2). What is the expected output for that set of input data?

I am sure there are others on the forum who can help as well, but I suspect they may be running into these same questions that I have.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-13 : 09:05:38

Hi,
The version is Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86) Dec 17 2008 15:19:45 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

What I would like to do is, in a specific month (lets say May 2012) I would like to know the people (USERID) who spent more than 50 mins outside in a day at lunch time.

Hope this is more clear.

Best Regards.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-13 : 15:17:08

What is the output you would expect given the sample data you posted in your initial posting?

Also, how do you define lunch time? If someone exited at 10:30 AM and entered at 12:30 would that count?

To calculate what you are trying to calculate, one has to be able to pair off entrances with exits. In your sample data, there are more exits than entrances, so I am not sure what logic to use for pairing off.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-13 : 16:52:06

[code]
declare @raysefo table(ID int,SNO int,USERID int,POINTID int,
DIRECTIONID int,FIRMID int,DEPARTMENTID int,SECTIONID int, OPERATIONDATETIME datetime)

insert into @raysefo
SELECT 1, 3383, 1000184, 5, 1, 2, 61, 7, '2008-02-01 08:13:16.000'
UNION
SELECT 2, 3383, 1000184, 9, 2, 2, 61, 7, '2008-02-01 11:55:54.000'
UNION
SELECT 3, 3383, 1000184, 9, 1, 2, 61, 7, '2008-02-01 12:50:24.000'
UNION
SELECT 4, 3383, 1000184, 10, 2, 2, 61, 7, '2008-02-01 17:37:04.000'
UNION
SELECT 5, 3383, 1000184, 3, 1, 2, 61, 7, '2008-02-04 08:40:46.000'
UNION
SELECT 6, 3383, 1000184, 6, 1, 2, 61, 7, '2008-02-04 08:41:42.000'
UNION
SELECT 7, 3383, 1000184, 4, 2, 2, 61, 7, '2008-02-04 12:00:16.000'
UNION
SELECT 8, 3383, 1000184, 3, 1, 2, 61, 7, '2008-02-04 12:55:34.000'
UNION
SELECT 9, 3383, 1000184, 5, 1, 2, 61, 7, '2008-02-04 12:56:19.000'
UNION
SELECT 10, 3383, 1000184, 4, 2, 2, 61, 7, '2008-02-04 17:43:40.000'

--SELECT
--YEAR(OPERATIONDATETIME) Yr,
--MONTH(OPERATIONDATETIME) Mnth,
--USERID,
--COUNT(CASE WHEN DIRECTIONID = 1 THEN 1 ELSE 0 END) AS Entrances,
--COUNT(CASE WHEN DIRECTIONID = 2 THEN 1 ELSE 0 END) AS Exits

--FROM @raysefo
--WHERE CONVERT(VARCHAR,OPERATIONDATETIME,108) BETWEEN '13:00' AND '14:25'
--GROUP BY
--YEAR(OPERATIONDATETIME),
--MONTH(OPERATIONDATETIME),
--USERID

select SUM( cast(datename(MI,OPERATIONDATETIME) as float)) sumMinutes, DIRECTIONID, datename(dw,OPERATIONDATETIME) as dw, USERID, COUNT(*)
from @raysefo
where POINTID IN (4,5,6,7)
group by DIRECTIONID, datename(dw,OPERATIONDATETIME), USERID

sumMinutes DIRECTIONID dw USERID Count
13 1 Friday 1000184 1
97 1 Monday 1000184 2
43 2 Monday 1000184 2
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-14 : 14:13:46

Hi,

You should ignore if there are more entries than exits or vice versa. We are trying to retrieve the ones for lunch more than 60 mins among who is out from 11:30 to 14.00.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-14 : 14:18:16

define lunch. do you have night shift? do you have a table definition for break/lunch times per shift?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-14 : 16:15:36

Hi,

Lunch may start from 11:30 till 14:00 ,but it has to be no more than 60 mins. I am looking for people who go to lunch between 11:30 to 14:00 and stays outside more than 60 mins.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-14 : 16:17:12

No shift or etc. Just want a query to look for people who stays out for more than 60 mins between 11:30 and 14:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources