Hello,

  I am retrieving the data from two tables.
Emp:
----
empid empname basic

dailydetails:
--------------
indexno empid qty target trdate piecerate nohours


I want to get the details from these tables.
The second table contain many records for the same empid on the same date. Like that, for each empid i want to find, the number of days worked for each employee in the specific month and year.

Suppose, empid: 0101 month:Jan Year: 2005

i want to find the number of days he worked. How can i find???

i need to use two queries???/

hi,
this is the basic idea
SELECT dailydetails.*, Emp.* FROM dailydetails INNER JOIN Emp ON dailydetails.empid = Emp.empid where Emp.empid=0101;

but you have to write two commant

select and select count

please date type for the perticular search field

surendran
(Anything is Possible)

Plese provide some sample data and exactly how you like your results. for e.g. trdate is this a datetime field? etc.

Hello,

  Thanx for the replies.

Sample Data:

Emp:
0101 AAAA 260
0102 BBBB 260
0103 CCCC 280

dailydetails:
index empid qty target trdate piecerate nohours
1 0101 200 250 01-Jan-2005 Y 8
2 0101 250 260 01-Jan-2005 Y 8
3 0101 400 500 02-Jan-2005 Y 8
4 0101 350 300 02-Jan-2005 Y 8
5 0101 450 600 02-Jan-2005 Y 8
6 0102 240 250 01-Jan-2005 Y 8
7 0102 500 550 01-Jan-2005 Y 8
8 0102 350 350 03-Jan-2005 Y 8
9 0102 300 250 03-Jan-2005 Y 8
10 0102 350 380 04-Jan-2005 Y 8
...

My report should look like...

emp.empid emp.empname emp.basic nodays
-------------------------------------------
0101 AAAA 260 2
0102 BBBB 260 3

....


thanks

SELECT emp.empname, emp.basic, Q.empid, count(q.trdate) AS NoOfDays
FROM [SELECT empid, trdate
FROM dailydetails
GROUP BY empid, trdate ]. AS Q INNER JOIN Emp ON emp.empid = Q.empid
GROUP BY Q.Empid, emp.empname, emp.basic;

Hello,

   Sorry, its giving error "Can not find table "select empid,trdate..."

SELECT emp.empname, emp.basic, Q.empid, count(q.trdate) AS NoOfDays
FROM [SELECT dailydetails.empid, dailydetails.trdate
FROM dailydetails
GROUP BY dailydetails.empid, dailydetails.trdate ]. AS Q INNER JOIN Emp ON emp.empid = Q.empid
GROUP BY Q.Empid, emp.empname, emp.basic;