Hi there! I have a problem and i want you guys to help me.
I have 1 table which has many fields. (mysql and im connected from microsoft visual studio..runs very well so there no problem with this).
lets forget the fields that we dont want now.. i have 4 fields:
f_date (datetime)
f_name (varchar)
f_out (varchar)
f_in (varchar)
Ok i know its not the best idea to have time in varchar but when i was doing that project i was getting erros with dates so i managed to make it work like this (but as i remember in some sql commands i had to convert the pure text from sql to time within the sql command).
table example:
f_name|||||f_out|||||f_in|||||f_date
peter|||||15:00:00|||||15:34:00|||||2014-06-07 15:00:00
alex|||||11:00:00|||||15:04:00|||||2014-06-13 11:00:00
alex|||||4:00:00|||||5:38:00|||||2014-06-22 4:00:00
peter|||||1:00:00|||||3:23:00|||||2014-06-06 1:00:00
peter|||||5:00:00|||||6:31:00|||||2014-06-07 5:00:00
peter|||||17:00:00|||||18:49:00|||||2014-06-07 17:00:00
alex|||||18:00:00|||||18:11:00|||||2014-06-22 18:00:00
peter|||||19:00:00|||||19:34:00|||||2014-06-07 19:00:00
i want a command which will get input the name and date from and date to and search between these dates to find the data..
it has to return the dates that there are records for this name AND (here is the trick)
the time differance between
a)the first f_out of the day(there can be many f_out for same day for same person)
b)the last f_in of the day(there can be many f_in for same day for same person)
so for example i want for a search between 2014-06-07 00:00:00 and 2014-06-29 00:00:00 to return for peter the time differance between (first f_out of that day) 5:00:00 and (last f_in) 19:34:00 for 2014-06-07 and so on for the rest days..
there are "blocks" of work time.. f_out and f_in. f_out is when someone started job and f_in when finished this block... but he can have many bloicks as you can see each day..i want to find his duty time which starts on the first f_out of the day and finishes on the last f_in of the same day.. as you can see f_date for each record contains the day and also the f_out (when started work)..he can never have same f_date even if he has many blocks of work the same day because as i mentioned this field is not only date but also time(time of f_out).
time differance only for the dates that there are records??? pleaseeee :D
i know probably im asking a lot but anyone please heeeelp :)