InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 00:03:33 GMT20http://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspx[code]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 trxtimeOUTfrom rows as rowsMinusOne left outer join rows on rows.rownum = rowsMinusOne.rownum + 1 and rows.emppin = rowsMinusOne.emppin[/code]let me know if you need clarification on this.Mon, 05 Nov 2012 18:45:32 GMTsdhanpaulhttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxhi sdhanpaul, sorry if i didnt check the forum more often. no i havent found any solution yet.... can you please guide me? thanks:-)Mon, 05 Nov 2012 17:31:42 GMTsharon_sfy2khttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxTo 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...Mon, 05 Nov 2012 15:22:00 GMTsdhanpaulhttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxi 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.Sun, 04 Nov 2012 17:28:01 GMTsdhanpaulhttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspx[quote][b]emmadcst (7/19/2012)[/b][hr]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.[/quote]What is the purpose of your post?Thu, 19 Jul 2012 05:11:39 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxI 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.Thu, 19 Jul 2012 03:30:08 GMTemmadcsthttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspx[quote][b]sharon_sfy2k (7/10/2012)[/b][hr]sorry sir if make you confused....EmpNo DateLog Timein TimeOut001 07/01/2012 2:00pm 001 07/02/2012 1:00am001 07/02/2012 2:00pm 11:00pm001 07/03/2012 2:00pm 11:00pmthe 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..[/quote]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:[code="sql"]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 [/code]Wed, 11 Jul 2012 07:46:26 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxhi,that is exact script that i have so far...the problem is it wont generate same date with different time as log out...thanksTue, 10 Jul 2012 17:59:22 GMTsharon_sfy2khttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxSELECT EmployeeNumber, Date, Min(time) as Login, MAX( Time) AS LogoutFrom TableGroup By EmployeeNumber, DateIf 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.Tue, 10 Jul 2012 17:55:54 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxsorry sir if make you confused....EmpNo DateLog Timein TimeOut001 07/01/2012 2:00pm 001 07/02/2012 1:00am001 07/02/2012 2:00pm 11:00pm001 07/03/2012 2:00pm 11:00pmthe 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..Tue, 10 Jul 2012 17:53:34 GMTsharon_sfy2khttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxI don't quite follow you.Can you provide a couple of extra bits of information please?1) Sample input data2) 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.Tue, 10 Jul 2012 09:44:18 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1327699-338-1.aspxPlease 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?thanksTue, 10 Jul 2012 09:39:08 GMTsharon_sfy2k