| Author |
 Topic  |
|
|
raysefo
Constraint Violating Yak Guru
257 Posts |
 Posted - 06/12/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/12/2012 : 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
257 Posts |
Posted - 06/12/2012 : 09:43:20
|
Hi,
I am getting this error:
Type TIME is not a defined system type |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/12/2012 : 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 thisWHERE
DATEADD(dd,DATEDIFF(dd,OPERATIONDATETIME,0),OPERATIONDATETIME) BETWEEN '13:00' AND '14:25'
|
|
|
|
raysefo
Constraint Violating Yak Guru
257 Posts |
Posted - 06/12/2012 : 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
257 Posts |
Posted - 06/12/2012 : 13:45:53
|
| Shortly I want to get the ones more than 75 mins (entrance - exit interval) |
|
|
|
raysefo
Constraint Violating Yak Guru
257 Posts |
Posted - 06/12/2012 : 16:04:19
|
| I want to get the ones which has 75 mins in difference and more. |
|
|
|
raysefo
Constraint Violating Yak Guru
257 Posts |
Posted - 06/13/2012 : 08:20:03
|
| Anybody can help? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/13/2012 : 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
257 Posts |
Posted - 06/13/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/13/2012 : 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
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/13/2012 : 16:52:06
|
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
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
Edited by - yosiasz on 06/13/2012 17:02:20 |
|
|
|
raysefo
Constraint Violating Yak Guru
257 Posts |
Posted - 06/14/2012 : 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
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/14/2012 : 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
257 Posts |
Posted - 06/14/2012 : 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
257 Posts |
Posted - 06/14/2012 : 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 |
|
|
| |
Topic |
|
|
|
How to query this?
