7 replies to this topic

[newphpcoder]

Hi..


I know its a couple of weeks that i have problem in datetime..

For example i have this data:

00100 2011-11-20 05:35:00
00100 2011-11-20 13:35:00
00100 2011-11-21 21:35:00
00100 2011-11-22 05:35:00

when I tried this query for testing:

SELECT a.EMP_NO, max(a.DTR), min(b.DTR) FROM regular_dtr a LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO;

i have this output:

EMP_NO--max_dtr------------------min_dtr
00100----2011-11-22 05:35:00-----2011-11-20 05:35:00

i need result is:

EMP_NO--max_dtr------------------min_dtr
00100----2011-11-20 13:35:00-----2011-11-20 05:35:00
00100----2011-11-22 05:35:00-----2011-11-21 21:35:00

I really, don't know what syntax should i need..

I'm sorry if until now, I did not solve this:(

Any help is highly appreciated..

Thank you so much...i hope you would not angry with me...the reason why i post again this problem because now i only have two columns, EMP_NO and DTR(IN and OUT) and i have no right to demand to separate the data of in and out...so that in my part I need to do that but sad to say, I have only few knowledge in mysql..specially in functions.

[WingedPanther]

The max for each employee is the max, regardless of whether you use a join or not. Same for the min.

What is the logic for the two values you have?

[newphpcoder]

this sample data:

00100 2011-11-20 05:35:00 //this is the time in for the first day
00100 2011-11-20 13:35:00 // this is the time out for the first day
00100 2011-11-21 21:35:00 //this is the time in for the second day
00100 2011-11-22 05:35:00 // this is the time out for the second day.

this is the situation:

I have 3 shifts
1. 05:35 - 13:35 // this is the same date like for example: 2011-11-20 05:35 2011-11-20 13:35
2. 13:35 - 21:35 //this is the same date like for example: 2011-11-20 13:35 2011-11-20 21:35
3. 21:35 - 05:35 // this is not same date like for example: 2011-11-21 21:35 2011-11-22 05:35


And now theirs a changes and problem..Now I need to insert in a new table that data but extracted:

like this:

EMP_NO--date_dtr----- max_dtr------------------min_dtr
00100----2011-11-20---2011-11-20 13:35:00-----2011-11-20 05:35:00
00100----2011-11-21---2011-11-22 05:35:00-----2011-11-21 21:35:00

date_dtr is date from min_dtr

min_dtr is time in
max_dtr is time out

the min and max function is work correctly if the date is the same but in my third shift its not work correctly becuase is not the same date.

I really...really don't know how to fix it...

Thank you so much for your help..

i know that I don't have a brilliant logic but i tried..

---------- Post added at 08:22 AM ---------- Previous post was at 08:11 AM ----------

if php code is the solution can you give me an example???

I really don't know where to start..

Thank you so much..

[WingedPanther]

Why not just extract ALL the data, loop through it building the corresponding insert statements (using whatever language you like)? Failing that, you'll have to come up with an identifier you can use that matches a clock in to a clock out time.

[newphpcoder]

Now i used only query statment..but somebody told me i need to used php but i don't know how:(

Thank you

---------- Post added at 12:43 AM ---------- Previous post was at 12:42 AM ----------

1. Actually, this is the scenario..i upload that data in my database. And I only used mysql insert statement. And after the data inserted in a table i have again the another table where inserted the data but separate the min and max of DTR. I only used min and max top distinguish what is the min or check in and max or check out.
2.Yes, it happens...with the used of min and max i only get the minimum time for check in and maximum time for check out.
3.the programs for attendance is separately...I only get the data from the database.
4. the program for attendance is 24 hours run..but I get only the data before the cut off period like for example i get the attendance from december 1, 2011 - december 15, 2011 I will get it on december 16, 2011 so that the data is completed.
5.the table contain all the history of attendance. for the reference.
6. I don't have programs to.. i only have upload programs to upload the attendance and i used insert statement to save the data in my database.

[newphpcoder]

I only want to know is what can i do?what is the syntax if i only have data EMP_NO and Daily_Time_Record which mix check in and check out and also theirs a scenario that the employee check in twice or check out twice or sometimes no checkin or no check out.

It's hard for me to figured out how can I get the date in check in, and the check in and checkout of an employee. Like i've said before i used min and max and i found out I have problem if the shift or his Daily_Time_Record is 2011-11-21 21:35:00 - 2011-11-22 05:35:00 the date is different...I don't have problem if the schedule is 2011-11-20 05:35:00 - 2011-11-20 13:35:00

My head was crushing i don't know what to do..what the syntax is.. :-[

If it is php code or pure mysql and how..

Thank you for your help..

[WingedPanther]

It is PHP code or C# code or Java code or C++ code or Pascal code or...

it is NOT MySQL.

[newphpcoder]

Before I only used mysql..but somebody should i need to code it in php but I don't know how?:confused:


Thank you

---------- Post added at 12:57 AM ---------- Previous post was at 12:41 AM ----------

Someone give me this idea but my problem I need to code it in php but I have no idea how cn I code it in php.

(1) Use the simple query
SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

(2) Read one record. Presumably, it will be a CHECKIN DTR. Remember the DTR value from that records as the "checkin" time.
(3) In a loop, read the next records. When you find one that is obviously for the same SHIFT as the record from (2) you remember its DTR value as the "checkout" time. You may only find one record for the same SHIFT or you may find 2 or 3 more for that shift. [*You* will have to define what a "SHFIT" is. I would assume it is a checkout time that is no more than, say, 12 hours (?? maybe??) from the checkin.
(4) When you read a DTR time that obviously is *NOT* from the same SHIFT, then you write a record to the new table:
EMP_NO, CHECKIN_DTR, CHECKOUT_DTR
(5) After writing that record, you use the DTR time that is not from the same SHIFT as the new checkin time for the *next* SHIFT. And you loop back to (3).

Notice that if the EMP_NO changes, that is *automatically* a change of SHIFT.

EMP_NO   DTR

110011    Dec 3, 2011, 8:35 AM

110011    Dec 3, 2011, 9:05 AM

110011    Dec 3, 2011, 5:20 PM

110011    Dec 4, 2011, 9:20 PM

110011    Dec 4, 2011, 9:50 PM

110011    Dec 5, 2011, 3:50 AM

110011    Dec 5, 2011, 4:05 AM

220022    Dec 3, 2011, 8:40 AM

...

Isn't it *OBVIOUS* when looking at those date/times that the following is true?

 

EMP_NO    BEGINSHIFT              ENDSHIFT

110011    Dec 3, 2011 8:35 AM     Dec 3, 2011 5:20 PM

110011    Dec 4, 2011 9:20 PM     Dec 5, 2011 4:05 AM

220022    Dec 3, 2011 8:40 AM     ... etc. ...

So by making one run through the "raw" DTR data, you should be able to create a table with BEGINSHIFT and ENDSHIFT and then you can do *ALL* your computations (e.g, total time worked, etc.) from that new table.