aspose file tools*
The moose likes JDBC and the fly likes Report Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Report Query" Watch "Report Query" New topic
Author

Report Query

Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

Hi,

Database : MySql

Table Name : Signin_Signout
===================

signindate, empid, intime, outtime, workinghours
2012-03-12 TEK323 2012-03-12 11:57:17 2012-03-12 16:57:14 4
2012-03-12 TEK341 2012-03-12 11:57:51 2012-03-12 15:49:27 3
2012-03-12 TEK383 2012-03-12 11:59:05 2012-03-12 15:49:56 3
..... up to 12000 records....

Table Name : ActualWorkingDays
=====================

actual_year actual_month acutal_daysofworking
2011 JAN 16
2011 FEB 20
2011 MAR 23
.... upto 10 years values



Here How Can i get following report fields and values

Emp Id Emp Name TotalDays Worked Days Leave Days..



Here empid and name details are we can get from master tables, totaldays also avail in ActualWorkingDays Table,

here the problem is i have to calculate the working days using this 'Signin_Signout' Table ..

if the employee have less then 3 Working hrs that is = Leave
if the employee have greater then 3 and less then 7 Working hrs mean = Half Day Leave

How to write a Query please help me out from this....






Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19670
    
  18

This is going to be very tricky, if not impossible, to do through a query. What about holidays, weekends, etc? What about leap years where February 29th is added, but in some years as day the employee should have worked but in others where it's a weekend.

I did do this myself once, but I put that part of the code into the business logic of the application. The database just provided the relevant data and the application calculated these values.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

or a packaged procedure, if your database supports them, and if you want to do it lots and lots of times
Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

Rob Spoor wrote:This is going to be very tricky, if not impossible, to do through a query. What about holidays, weekends, etc? What about leap years where February 29th is added, but in some years as day the employee should have worked but in others where it's a weekend.

I did do this myself once, but I put that part of the code into the business logic of the application. The database just provided the relevant data and the application calculated these values.



Holidays and weekends are no need .... please look care fully.. i have another table call "actualworkingdays" right in that only i have mentioned total working days of the particular month..
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I assume that workers do not stay overnight, otherwise you'd have to specify how to handle that.

From MySQL manual (I don'd do MySQL myself):

- You could use the TIMEDIFF function to determine how much time elapsed between the intime and outtime times.

- If there can be more than one record per given employee and day, you should use GROUP BY to aggregate actually worked time (see above) per employee and day.

- Use case expressions to convert the time you get into values 0, 0.5 or 1 respectively, according to the rules you specified.

- Use join to join the result to the ActualWorkingDays table. I don't now how complicated queries MySQL can handle, so you might need to use a temporary table for that. Keep in mind that if somebody works during non-working days, your report won't catch this. This is probably not desired.
Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

My Table Structures are

i have three tables

employee - empid,firstname,lastname....

Full Texts empid firstname midname lastname birthdate gender panno maritalstatus residential_address permnent_address edlevel deptid designation salary active
Edit Delete TEK323 Arunkumar NULL Chinnadurai 1989-10-12 MALE AVDPA1900B NO 263D,Krishnanagar,Edapalayam,Redhills,Chennai-6000... 3,Sri Ram towers,mariyappan street,ram nagar,coimb... 15 D101 Associate Software E 100.00 ACTIVE
Edit Delete TEK341 Gnanasekar NULL Rajamanikam 1985-09-28 MALE NULL NO Udumal pet Udumal pet 18 D101 Associate Software E 100.00 ACTIVE
Edit Delete TEK344 Sivababu NULL NULL NULL MALE NULL NO NULL NULL NULL D101 Associate Software E NULL ACTIVE



actualworkingdays - year,month,workingdays

Full Texts actual_year actual_month acutal_daysofworking
Edit Delete 2011 1 17
Edit Delete 2011 3 23
Edit Delete 2011 4 20
Edit Delete 2011 5 21
Edit Delete 2011 6 22
Edit Delete 2011 7 21
Edit Delete 2011 8 21
Edit Delete 2011 9 22
Edit Delete 2011 10 20




signin_signout - singindate,empid,intime,outtime,workedhours

Full Texts signindate empid intime outtime workinghours
Edit Delete 2012-03-12 TEK323 2012-03-12 11:57:17 2012-03-12 16:57:14 4
Edit Delete 2012-03-12 TEK341 2012-03-12 11:57:51 2012-03-12 15:49:27 3
Edit Delete 2012-03-12 TEK383 2012-03-12 11:59:05 2012-03-12 15:49:56 3
Edit Delete 2012-03-12 TEK384 2012-03-12 12:00:10 2012-03-12 15:50:02 3
Edit Delete 2012-03-12 TEK001 2012-03-12 12:01:36 2012-03-12 14:24:30 2
Edit Delete 2012-03-12 TEK002 2012-03-12 12:05:01 2012-03-12 14:24:37 2
Edit Delete 2012-03-12 TEK003 2012-03-12 12:05:54 2012-03-12 14:24:49 2
Edit Delete 2012-03-12 TEK004 2012-03-12 12:07:01 2012-03-12 14:24:56 2
Edit Delete 2012-03-12 TEK005 2012-03-12 12:07:34 2012-03-12 14:25:02 2
Edit Delete 2012-03-12 TEK381 2012-03-12 12:09:28 2012-03-12 15:50:54 3
Edit Delete 2012-03-12 TEK382 2012-03-12 12:10:24 2012-03-12 15:51:00 3
Edit Delete 2012-03-12 TEK343 2012-03-12 12:21:30 2012-03-12 15:51:08 3
Edit Delete 2012-03-12 TEK344 2012-03-12 12:21:36 2012-03-12 15:51:14 3



I have to create following report using these taples,

Yearly Report













Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Resource Name No of Leaves

Peter 2 0 4 5 2 3 6 2 3 2 3 3 35


using single query ...


Note :
1) Workers are not stay night,
2) Do not consider about the holidays
3) if it is married female employee have only 6 hrs is full working day 3 hrs is half day,
4) all the should have 7 hrs working hours for full day, 3 hrs for half day


Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

well
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Arunkumar Chinnadurai wrote:well

as we are doing 1 word posts.
patience
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

so what have you done so far?
which particular bit are you stuck upon?
Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

Wendy Gibbons wrote:so what have you done so far?
which particular bit are you stuck upon?


i did not get output., any body can help me out from this...
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

you haven't told us what you are doing HOW CAN WE HELP, all you have told us are the table layout, and the data in the tables, once again
SHOW US WHAT YOU HAVE TRIED, show us the code.....

we are not just going to print an answer for you but try to help you solve your problem.
Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

Wendy Gibbons wrote:you haven't told us what you are doing HOW CAN WE HELP, all you have told us are the table layout, and the data in the tables, once again
SHOW US WHAT YOU HAVE TRIED, show us the code.....

we are not just going to print an answer for you but try to help you solve your problem.



I need query for that out put..
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

so you haven't even made a first attempt?
even got any thoughts on how to start?

Did you listen to robs answer:

This is going to be very tricky, if not impossible, to do through a query. What about holidays, weekends, etc? What about leap years where February 29th is added, but in some years as day the employee should have worked but in others where it's a weekend.

I did do this myself once, but I put that part of the code into the business logic of the application. The database just provided the relevant data and the application calculated these values.


and have you even thought about how to do this in java
take a pencil and paper and start working out how to do this programattically.

come back when you have some design ideas, we can discuss.
Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

Wendy Gibbons wrote:so you haven't even made a first attempt?
even got any thoughts on how to start?

Did you listen to robs answer:

This is going to be very tricky, if not impossible, to do through a query. What about holidays, weekends, etc? What about leap years where February 29th is added, but in some years as day the employee should have worked but in others where it's a weekend.

I did do this myself once, but I put that part of the code into the business logic of the application. The database just provided the relevant data and the application calculated these values.


and have you even thought about how to do this in java
take a pencil and paper and start working out how to do this programattically.

come back when you have some design ideas, we can discuss.



No I have worked with java code.. and its working perfectly....but my manager says "should not use business logic ... you just create single query and show the report out put nu ",

Actually what they expecting means a single query give all the result then i have to store object.

I have writen my ideas in my note more then 5 pages 9 [queries and java codes all things].

Ok i`m sending .. what i have tried in query..


ok stil i`m sticking this query only
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

just out of interest how does your boss know it can be done in 1 query?
and can he be more help, as he already seems to know the answer?
Arunkumar Chinnadurai
Ranch Hand

Joined: Dec 15, 2011
Posts: 56

Wendy Gibbons wrote:just out of interest how does your boss know it can be done in 1 query?
and can he be more help, as he already seems to know the answer?


Actually speaking the query mean that may be a function[mysql] or procedure or a view ... me also faced this kind of question in my college days...
i`m keep on trying surely i will get ...
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Arunkumar Chinnadurai wrote:
Wendy Gibbons wrote:just out of interest how does your boss know it can be done in 1 query?
and can he be more help, as he already seems to know the answer?


Actually speaking the query mean that may be a function[mysql] or procedure or a view ... me also faced this kind of question in my college days...
i`m keep on trying surely i will get ...


ahh so it can be a stored procedure, well that makes it so much easier.
Remember a stored procedure is just like a mini program stored on the database.

So we are back to the pen and paper and working out the sums you need to do to solve this.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Report Query