September 18, 2013 at 1:37 am
Hello,
i have a problem regarding query,
my data is like this
EID------checktime-------------status-----
123-----7/1/2013 08:00:00 PM-----I---------
123-----7/2/2013 02:00:00 AM-----O---------
I means in and O means Out
employee enter in a factory on 7/1/2013 08:00:00 PM
and out in a factory on 7/2/2013 02:00:00 AM at night becouse after 12:00 date is change.
i want a query that shows data like this
EID------checktime-------------status-----
123-----7/1/2013 08:00:00 PM-----I---------
123-----7/1/2013 02:00:00 AM-----O---------
Please Help me out.
Thanks
immad
September 18, 2013 at 2:33 am
Please read the link in my signature on how to post questions.
In short, we need the following: table DDL, sample data (in the form of insert statements) and desired output.
On topic: so you want the out to come before the in, that seems strange.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2013 at 5:21 am
Need much more information, your question(s) makes no sense at all.
September 19, 2013 at 6:12 am
Koen is exactly right - we will need the basics in a reproducible form to have any chance of addressing the problem.
This looks like it's for a time and attendance system where people work multiple shifts. Having worked on this type of system years ago, I can tell you that it's going to be much more complicated than you initially think. It will also be highly customized to the business rules of your company. You're going to have to consider what happens when people EOD on a day before they BOD (3rd shift), when their lunch time breaks across day a boundary, when people pull a double-shift and when people switch from one shift to another for a few days in the middle of a week. Then there's the time-and-a-half and double-time rules and how it's calculated for different types of employees. You can default when people forget to scan EOD, but a real difficulty is when people forget to scan BOD. Flexible shifts may also cause you significant pain when people forget to scan.
This is probably not going to be a simple, one-day project, but rather a system that matches scans (BOD/EOD, lunch out/in, personal business out/in, etc.) and then makes entries into an hours accounting system. There may also be a manual approval step where someone has to approve what's been calculated to make sure nobody gets paid for 30 hours in a single day. In short, it will be complicated.
September 19, 2013 at 6:21 am
YES , we r making this type of software your jugement is right,and we make 75% software complete.can u help me out on this one
immad
September 19, 2013 at 6:25 am
To sort your output, use an ORDER BY clause. I believe your column is checktime.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply