March 12, 2016 at 3:43 am
Hello,
I have different rows with Datetime records. so now i have to get First In Punch and Last Out Punch from Night shift records. Record will be insert in table like below
Enroll_No Datetime
--------------------------------------------
1 2016-03-10 17:05:00
1 2016-03-10 22:04:00
1 2016-03-11 00:10:00
1 2016-03-11 03:00:00
Now this employee has Shift Time 10-Mar-2016 17:00 to 11-Mar-2016 03:00
Also there will missing punch entries there like from above 10-03-2016 22:04:00 Entry will be missing.
There will be overtime case also so like last punch would be 2016-03-11 07:00. So please provide solution for this also.
So kindly help to get proper In and Out punch in below format
Enroll_No In_Time Out_Time
----------------------------------------------------------------------------------------
1 2016-03-10 17:05:00 2016-03-11 03:00:00
Also attached file.
March 12, 2016 at 4:20 am
well I am going to guess that it really isnt as simple as your sample data.......but as a start to the discussion, see below
USE tempdb
GO
--DROP TABLE yourtable;
CREATE TABLE yourtable(
Enroll_No INT NOT NULL
,Datetime DATETIME NOT NULL
);
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-10 17:05:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-10 22:04:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 00:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 03:00:00.000');
SELECT Enroll_No,
MIN(Datetime) AS In_Time,
MAX(Datetime) AS Out_Time
FROM yourtable
GROUP BY Enroll_No;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 12, 2016 at 4:44 am
Thanks for your reply.
Sorry i forgot to mention that i need to set Date wise In-Out Details. As per attachment. Also i added some entries in your given code. so please request you to set those entries like attachment.
USE tempdb
GO
--DROP TABLE yourtable;
CREATE TABLE yourtable(
Enroll_No INT NOT NULL
,Datetime DATETIME NOT NULL
);
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-10 17:05:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-10 22:04:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 00:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 03:00:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 17:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-12 02:55:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-12 16:50:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-12 01:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-13 03:50:00.000');
SELECT Enroll_No,
MIN(Datetime) AS In_Time,
MAX(Datetime) AS Out_Time
FROM yourtable
GROUP BY Enroll_No;
DROP TABLE yourtable
Thanks in Advance
March 12, 2016 at 4:56 am
hardik-1087613 (3/12/2016)
Thanks for your reply.Sorry i forgot to mention that i need to set Date wise In-Out Details. As per attachment. Also i added some entries in your given code. so please request you to set those entries like attachment.
USE tempdb
GO
--DROP TABLE yourtable;
CREATE TABLE yourtable(
Enroll_No INT NOT NULL
,Datetime DATETIME NOT NULL
);
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-10 17:05:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-10 22:04:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 00:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 03:00:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-11 17:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-12 02:55:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-12 16:50:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-12 01:10:00.000');
INSERT INTO yourtable(Enroll_No,Datetime) VALUES (1,'2016-03-13 03:50:00.000');
SELECT Enroll_No,
MIN(Datetime) AS In_Time,
MAX(Datetime) AS Out_Time
FROM yourtable
GROUP BY Enroll_No;
DROP TABLE yourtable
Thanks in Advance
can you please indicate what your expected results are based on the sample data you gave.
the picture provides a structure, but it only shows a single day...your data spans multiple days.
also how do you determine whether the "datetime" entry is an "IN" or "OUT"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 12, 2016 at 7:28 am
J Livingston SQL (3/12/2016)
also how do you determine whether the "datetime" entry is an "IN" or "OUT"
This is an important question to be answered. Without the scan type, I don't know that it can be done.
This question reminds me of the hours accounting portion of an ERP I used to work on years ago. It was horrible because of the many different problem scenarios. Whatever you write has to take many things into account, such as:
1. Some shifts occur within a calendar day and some cross a calendar day. Each employee had normal working hours defined to determine the time window within for their shift within the day.
2. Employees many or many not work beyond their end of shift.
3. Anything over 8 hours per day is considered overtime for some employees, over 10 per day for others and over 40 per week for others.
4. Employees might not scan out for lunch, but scan in from lunch.
5. Employees might forget their BOD scan. Other might forget their EOD scan.
6. Sometimes employees come in late or leave early.
7. Occasionally, an employee might work 27 hours straight.
8. Instead of scanning in from lunch, they scan in to BOD when they already have one for that shift.
9. Instead of scanning out to lunch, they scan to EOD. Then then scan to EOD again later.
These are most of the main ones. Because you can't rely on the employees to follow the business rules you've defined for your application, you have to include so many scenarios in your application that the code starts to become mildly insane. Labor laws typically say they have to be paid for their hours worked, but mention nothing about them having to use a time clock (punch or scan) correctly.
If you're designing the system from scratch, I'd highly recommend you use scanners and employee cards instead of punch cards to avoid the manual entry of time, which can be another point of errors. One thing that can give you a huge advantage is if you use a scanner that's more like a PC instead of a collect-batch-send terminal. This will allow you interactivity with the employee as they're scanning. You might, for example, only show them the buttons appropriate to their status to prevent mistakes from happening. For example, if they're punched in, don't show them the "in from lunch" or "BOD" buttons at all.
We had the collect-batch-send terminals and it was a nightmare. We had people at each building who had to do a manual review of all scans for all employees for their location and then submit them. We eliminated the problems by switching to industrial hardware that was essentially a miniature PC in very protected hardware to survive the manufacturing environment. I'd recommend you start there and save yourself a great deal of headache and long-term expense.
I'd also recommend that you really think through your whole approach to minimize errors in the data ahead of time. Take a look at the problem scenarios I listed above and come up with some of your own. Know ahead of time that they will occur, so you have to be ready for them.
March 12, 2016 at 10:24 am
hardik-1087613 (3/12/2016)
Also there will missing punch entries there like from above 10-03-2016 22:04:00 Entry will be missing.
Ok... so what needs to happen in such an instance?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2016 at 10:55 am
Jeff Moden (3/12/2016)
hardik-1087613 (3/12/2016)
Also there will missing punch entries there like from above 10-03-2016 22:04:00 Entry will be missing.Ok... so what needs to happen in such an instance?
That's where that "Manual" guy shows up. 😉
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply