Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Time and Attendance need help pls... Expand / Collapse
Author
Message
Posted Tuesday, July 10, 2012 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 05, 2012 5:29 PM
Points: 4, 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
Post #1327699
Posted Tuesday, July 10, 2012 9:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1327707
Posted Tuesday, July 10, 2012 5:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 05, 2012 5:29 PM
Points: 4, 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..
Post #1327996
Posted Tuesday, July 10, 2012 5:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1327997
Posted Tuesday, July 10, 2012 5:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 05, 2012 5:29 PM
Points: 4, 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
Post #1327998
Posted Wednesday, July 11, 2012 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 10,910, Visits: 12,545
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

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
Post #1328282
Posted Thursday, July 19, 2012 3:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 19, 2012 3:30 AM
Points: 1, 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.
Post #1332026
Posted Thursday, July 19, 2012 5:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1332084
Posted Sunday, November 04, 2012 5:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, 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.
Post #1380853
Posted Monday, November 05, 2012 3:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, 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...
Post #1381280
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse