July 2, 2012 at 3:04 am
Hi There,
I would like to learn more mysql commands coz Im having problem on my queries, please correct me if this the proper forum.
I have two tables:
BiometricsLogs Table (comes when I transfer Biometrics Logs to mysql)
PK_logid
IDNumber
Date/Time
Check-in/out
Schedule Table
PK_SchedID
IDNumber
Date
SchedIn
SchedOut
OTID
OTIn
OTOut
Questions:
1. How can I seperate Date and Time from Biometrics Table (ex. 7/1/2012 8:00:00 AM)
2. How can I make the report table like this: (Do I need to crosstab)
Date : SchedIn : SchedOut : CheckIn : CheckOut
How can I loop the date from the selected start-date to end-date where
IDNumber is equals to selected IDNumber
3. How can I insert computation for late, overtime to insert as column in report table?
July 2, 2012 at 3:21 am
Hi
This is a Microsoft SQL Server forum, not a MySQL forum so you might be best asking MySQL questions on a dedicated MySQL forum.
But I am sure that there are some people who use a mixture of MSSQL and MySQL who could lend a hand.
In relation to point 1, I just did a search for MySQL Data Types and it seems like you have, much like MSSQL, the DATE and TIME data types, so it would be a case of converting DATETIME into DATE and TIME.
Point 2, you will want to use a join of some type, ensuring that you have the correct links between each table.
Point 3, without more details as what you define as late, overtime etc then cannot answer on that, but I suggest you have a table of normal working hours for each person and then you can link to that then then clock in/out etc
Note - I am not a MySQL user so my knowledge on that product is none.
July 2, 2012 at 3:46 am
Thanks for the reply. Sorry for the post but it can be answered by mssql since im still checking what will be the best sql server for the program.
Im ok with joining table but having hard time to crosstab it. Do I need to crosstab or i do have other options.
How can I seperate date and time?
July 2, 2012 at 3:50 am
There are links in my signature on Cross Tabs, if you are stuck please follow the second link in my signature on posting code and data so that we can create a mock environment and help you out.
As for seperating DATE and TIME, look at the convert function.
Also note that if we give you an MSSQL solution it may/maynot work with MySQL so it is best to choose your platform first. So is it going to be MSSQL or MySQL?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply