December 6, 2013 at 5:23 am
Hello All,
I want to calculate min and max datetime for every person one by one.
There are 50 person in this data and also there are datetime information everyday.
I want to decide min and max datetime for all day one by one.
But every people have 4-5 records per a day so I want to decide min and max from each of them.
CREATE TABLE [dbo].[EVENTS] (
[event_date] [datetime] ,
[employee_names] [fullname] ,
) ON [PRIMARY]
The table looks something like this:
[employee_names] [event_date]
EMLY WİNHOUSE
2013-02-01 08:52:54
2013-02-01 09:52:54
2013-02-01 17:52:54
2013-02-01 18:55:54
HARRY SEERE
2013-02-01 09:52:54
2013-02-01 10:52:54
2013-02-01 19:52:54
2013-02-01 20:55:54
barryy beaer
2013-02-01 14:52:15
.
.
.
I want something like this result:
Full name log-in log-out
Emly winhouse 2013-02-01 09:00:00 2013-02-01 18:55:54
HARRY SEERE 2013-02-01 09:52:54 2013-02-01 20:55:54
barryy beaer 2013-02-01 09:00:00 2013-02-01 18:00:00
***(emly winhouse log-in time must me 09:00 because min<09:00 clock)
but there are conditions :
1-) if employees min time < 09:00 o'clock set min = 09:00. if it not ,there is no operation just take min time.
2-) if there arent any record for one person for a day no information will come.
3-) if there is only one record min = 09:00 clock and max = 18:00 clock.
what I mean is if min = max 😉
December 6, 2013 at 6:05 am
general20 (12/6/2013)
Hello All,I want to calculate min and max datetime for every person one by one.
There are 50 person in this data and also there are datetime information everyday.
I want to decide min and max datetime for all day one by one.
But every people have 4-5 records per a day so I want to decide min and max from each of them.
CREATE TABLE [dbo].[EVENTS] (
[event_date] [datetime] ,
[employee_names] [fullname] ,
) ON [PRIMARY]
The table looks something like this:
[employee_names] [event_date]
EMLY WINHOUSE
2013-02-01 08:52:54
2013-02-01 09:52:54
2013-02-01 17:52:54
2013-02-01 18:55:54
HARRY SEERE
2013-02-01 09:52:54
2013-02-01 10:52:54
2013-02-01 19:52:54
2013-02-01 20:55:54
barryy beaer
2013-02-01 14:52:15
.
.
.
I want something like this result:
Full name log-in log-out
Emly winhouse 2013-02-01 09:00:00 2013-02-01 18:55:54
HARRY SEERE 2013-02-01 09:52:54 2013-02-01 20:55:54
barryy beaer 2013-02-01 09:00:00 2013-02-01 18:00:00
***(emly winhouse log-in time must me 09:00 because min<09:00 clock)
but there are conditions :
1-) if employees min time < 09:00 o'clock set min = 09:00. if it not ,there is no operation just take min time.
2-) if there arent any record for one person for a day no information will come.
3-) if there is only one record min = 09:00 clock and max = 18:00 clock.
what I mean is if min = max 😉
Hello and welcome to the forums. Let's start by creating some readily-consumable DDL and populate it. Please note that [fullname] is not a valid data type so I changed it to varchar(32).
CREATE TABLE [dbo].[EVENTS] (
[event_date] [datetime] ,
[employee_name] varchar(32));
insert into dbo.EVENTS(employee_name, [event_date])
values('EMILY WINHOUSE', '2013-02-01 08:52:54'),
('EMILY WINHOUSE', '2013-02-01 09:52:54'),
('EMILY WINHOUSE', '2013-02-01 17:52:54'),
('EMILY WINHOUSE', '2013-02-01 18:55:54'),
('HARRY SEERE', '2013-02-01 09:52:54'),
('HARRY SEERE', '2013-02-01 10:52:54'),
('HARRY SEERE', '2013-02-01 19:52:54'),
('HARRY SEERE', '2013-02-01 20:55:54'),
('barryy beaer', '2013-02-01 14:52:15');
Now that we have that done, we can move on to the problem. The following query does everything you asked for, but you might want to think about the part where min = max and they get credit for working a full day. You may want to throw a message because people will find ways to "milk the system" like coming in at 9:00 and leaving at noon without scanning out.
with cte as (
select employee_name, MIN(event_date) start_time, MAX(event_date) end_time
from dbo.EVENTS
group by employee_name)
select employee_name, start_time, end_time,
case when start_time = end_time then CONVERT(datetime2, CONVERT(varchar, start_time, 112) + ' ' + CONVERT(varchar, '09:00:00', 108))
else case when datepart(hour, start_time) < 9 then CONVERT(datetime2, CONVERT(varchar, start_time, 112) + ' ' + CONVERT(varchar, '09:00:00', 108))
else start_time
end
end adjusted_start_time,
case when start_time = end_time then CONVERT(datetime2, CONVERT(varchar, start_time, 112) + ' ' + CONVERT(varchar, '18:00:00', 108))
else end_time
end adjusted_end_time
from cte;
December 6, 2013 at 6:29 am
thank you very much.
but it doest give me correct solution.
I want to set between which date are going to calculate .
for example 12-07-2013 between 12-09-2013
On the other hand, whats adjusted time ? I just want 2 time
log-in time and log-out time(min and max)
December 6, 2013 at 6:37 am
general20 (12/6/2013)
thank you very much.but it doest give me correct solution.
I want to set between which date are going to calculate .
for example 12-07-2013 between 12-09-2013
On the other hand, whats adjusted time ? I just want 2 time
log-in time and log-out time(min and max)
I gave you the original start and end times and then the adjusted ones in separate columns so you could visually see that the adjustments are being done correctly. You can modify the query to remove the unwanted columns, but checking that it's doing what you want is important. Please don't ever just accept that a query someone (including me) posts online is correct. You need to understand the query that you are putting into production. After all, you're the one who is going to be supporting it.
I don't understand what you mean by wanting "to set between which date are going to calculate."
December 6, 2013 at 7:02 am
Ed Wagner (12/6/2013)
general20 (12/6/2013)
thank you very much.but it doest give me correct solution.
I want to set between which date are going to calculate .
for example 12-07-2013 between 12-09-2013
On the other hand, whats adjusted time ? I just want 2 time
log-in time and log-out time(min and max)
I gave you the original start and end times and then the adjusted ones in separate columns so you could visually see that the adjustments are being done correctly. You can modify the query to remove the unwanted columns, but checking that it's doing what you want is important. Please don't ever just accept that a query someone (including me) posts online is correct. You need to understand the query that you are putting into production. After all, you're the one who is going to be supporting it.
I don't understand what you mean by wanting "to set between which date are going to calculate."
Could you please improve this code according to my conditions ?
SELECT FullName,MIN(event_date) LOGIN_time ,MAX(event_date)LOGOUT_time
FROM Events
WHERE event_date BETWEEN '07-12-2013' AND '12-12-2013'
GROUP BY FullName
, CONVERT(date, event_date)
December 6, 2013 at 8:23 am
SELECT FullName,MIN(event_date) AS [LOGIN_time] ,MAX(event_date) AS [LOGOUT_time]
FROM Events
WHERE event_date >= '20131207'
AND event_date < DATEADD(day,1,'20131212')
GROUP BY FullName
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply