July 10, 2012 at 9:39 am
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
July 10, 2012 at 9:44 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 10, 2012 at 5:53 pm
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..
July 10, 2012 at 5:55 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 10, 2012 at 5:59 pm
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
July 11, 2012 at 7:46 am
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
Consultant - Straight Path Solutions
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
July 19, 2012 at 3:30 am
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.
July 19, 2012 at 5:11 am
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2012 at 5:28 pm
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.
November 5, 2012 at 3:22 pm
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...
November 5, 2012 at 5:31 pm
hi sdhanpaul, sorry if i didnt check the forum more often. no i havent found any solution yet.... can you please guide me? thanks:-)
November 5, 2012 at 6:45 pm
with rows as (
select *, row_number() over (order by emppin, trxdate) as rownum
from rawtrx)
select *, rowsMinusOne.trxtime as trxtimeIN,
ISNULL(rows.trxtime,1) as trxtimeOUT
from rows as rowsMinusOne
left outer join rows
on rows.rownum = rowsMinusOne.rownum + 1 and rows.emppin = rowsMinusOne.emppin
let me know if you need clarification on this.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply