SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time and Attendance need help pls...


Time and Attendance need help pls...

Author
Message
sharon_sfy2k
sharon_sfy2k
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 11
Please can someone help me....
I have a query which query the min and max of time in and out...the scenario is Employee log in at July 7 at 2pm at log out at 1am of July 8. He came in again at 2pm of July 8 and log out at July 8 around 11pm. the problem is i query the MIN and MAX, how can i get his 2 out on the same day?

thanks
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49430 Visits: 21142
I don't quite follow you.

Can you provide a couple of extra bits of information please?

1) Sample input data
2) Desired results, based on the input data provided.

If you follow the link in my signature, you'll see how best to ask questions here.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sharon_sfy2k
sharon_sfy2k
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 11
sorry sir if make you confused....

EmpNo DateLog Timein TimeOut
001 07/01/2012 2:00pm
001 07/02/2012 1:00am
001 07/02/2012 2:00pm 11:00pm
001 07/03/2012 2:00pm 11:00pm

the employee has 3 shifts.... TMS standard only generate first in last out...the problem is the employee log out same day with different time.. is there any possible way to generate the time and date as their first in and last out on the same day?...


thanks..
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19721 Visits: 7660
SELECT EmployeeNumber, Date, Min(time) as Login, MAX( Time) AS Logout
From Table
Group By EmployeeNumber, Date

If you'd like better tested code, check out the first link in my signature as to how to lay the problem out for us to provide you with working examples.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
sharon_sfy2k
sharon_sfy2k
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 11
hi,

that is exact script that i have so far...the problem is it wont generate same date with different time as log out...thanks
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42731 Visits: 14925
sharon_sfy2k (7/10/2012)
sorry sir if make you confused....

EmpNo DateLog Timein TimeOut
001 07/01/2012 2:00pm
001 07/02/2012 1:00am
001 07/02/2012 2:00pm 11:00pm
001 07/03/2012 2:00pm 11:00pm

the employee has 3 shifts.... TMS standard only generate first in last out...the problem is the employee log out same day with different time.. is there any possible way to generate the time and date as their first in and last out on the same day?...


thanks..


Please provide the table DDL. It is nearly impossible to figure this out with what you have provided. Looking at the data you have provided it looks like there are consecutive rows with a timein and no timeout. Is this correct? I'd probably do something with ROW_NUMBER() to correct the issue. Something like:

DECLARE @time TABLE
(
empno CHAR(3),
datelog DATETIME,
timein DATETIME,
[timeout] DATETIME
);

INSERT INTO @time
(empno, datelog, timein, [timeout])
VALUES
('001', '07/01/2012', '2:00pm', NULL),
('001', '07/02/2012', '1:00am', NULL),
('001', '07/02/2012', '2:00pm', '11:00pm'),
('001', '07/03/2012', '2:00pm', '11:00pm');

WITH timetest
AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY DateLog ASC) AS row_asc,
empno,
datelog,
timein,
[timeout]
FROM
@time
),
inandout
AS (
SELECT
TIN.empno,
TIN.datelog,
TIN.timein,
TOUT.datelog AS timeout_date,
CASE WHEN TIN.timeout IS NULL AND
TOUT.timeout IS NULL THEN TOUT.timein
ELSE TIN.timeout
END AS [timeout]
FROM
timetest AS TIN
LEFT JOIN timetest AS TOUT
ON TIN.empno = TOUT.empno AND
TIN.row_asc = CASE WHEN TIN.timeout IS NULL AND
TOUT.timeout IS NULL
THEN TOUT.row_asc - 1
ELSE TOUT.row_asc
END
)
SELECT
*
FROM
inandout
WHERE
timein IS NOT NULL AND
[TIMEOUT] IS NOT NULL






Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
emmadcst
emmadcst
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 10
I had been using SQL but not very sure about this query. Will have to look into this further. We had been tracking time for business purposes as required by projects using Replicon's time and attendance software which is another utility that helps for better productivity.
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49430 Visits: 21142
emmadcst (7/19/2012)
I had been using SQL but not very sure about this query. Will have to look into this further. We had been tracking time for business purposes as required by projects using Replicon's time and attendance software which is another utility that helps for better productivity.


What is the purpose of your post?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sdhanpaul
sdhanpaul
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 566
i think jack pretty much has the idea...i'm looking for a solution for this problem right now my self.

i originally used the quirky update to test the transaction number for the date for that employee...eg, punch 1 is time in, punch 2 is out. punch 3 is in...and so on...

one way i figured out to determine the number of hrs that makes up a day...

to the original poster, did you ever get the problem solved? well, if you did...im interested in finding out how you did it.

thanks.
sdhanpaul
sdhanpaul
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 566
To the original poster...If you haven't figured this out yet, let us know. I got the perfect solution...i feel so much like a genius day by day...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search