CareerCup

The following a working SQL query. You can try it yourself.
-----------------------------------------------------------------------------------

SELECT     TEMP.EmpID, TEMP.EmpName, 
                     Employees_HoursRate.hour_rate *  TEMP.TotalHoursPerMonth AS SAL
FROM         Employees_HoursRate INNER JOIN
                    (SELECT     Employee.empid AS EmpID, Employee.empname AS EmpName, 
                     SUM(Employee_TimesSheet.no_of_hours) AS TotalHoursPerMonth
                     FROM        Employee INNER JOIN
                     Employee_TimesSheet ON Employee.empid = Employee_TimesSheet.empid
                     GROUP BY Employee.empid, Employee.empname, 
                                          DATEPART(month, Employee_TimesSheet.date)) AS TEMP ON 
                     Employees_HoursRate.empid = TEMP.EmpID

ORDER BY TEMP.EmpID

create table employee(eid smallint,ename varchar(10));

insert into employee values(1 ,'Abhishek');
insert into employee values(2, 'Nikhil');
insert into employee values(3,'Rama');
insert into employee values(4 , 'Vidyut');
insert into employee values(5 , 'Pavan');

select * from timesheet;

create table timesheet(eid smallint,no_hrs smallint,cdate date);

insert into timesheet values(1,100 , '2011-06-30');
insert into timesheet values(2 , 105 , '2011-06-30');
insert into timesheet values(3 , 90 , '2011-06-30');
insert into timesheet values(4 , 85 , '2011-06-30');
insert into timesheet values(5 , 110 , '2011-06-30');


insert into timesheet values(5 , 110 , '2011-04-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-03-30');
insert into timesheet values(2 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2011-08-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(2 , 110 , '2010-06-30');
insert into timesheet values(1 , 110 , '2011-06-30');
insert into timesheet values(1 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');


select * from hr_rate;
+------+---------+
| eid | hr_rate |
+------+---------+
| 1 | 50 |
| 2 | 60 |
| 3 | 40 |
| 4 | 30 |
| 5 | 80 |

create table hr_rate(eid smallint,hr_rate smallint);

insert into hr_rate values(1,50);
insert into hr_rate values(2,60);
insert into hr_rate values(3,40);
insert into hr_rate values(4,30);
insert into hr_rate values(5,80);


select e.ename,month(t.cdate),year(t.cdate),sum(t.no_hrs*hr.hr_rate)
from employee e,timesheet t,hr_rate hr
where e.eid=t.eid and e.eid=hr.eid
group by e.ename,month(t.cdate),year(t.cdate)
order by ename,year(t.cdate),month(t.cdate)

SELECT 
    E1.empid,
    E1.empname,
    E2.Hours_rate*E3.total as SAL
FROM 
    PWM.EMP as E1, 
    PWM.emp_rate as E2, 
    ( 
    SELECT 
        EMPID, 
        SUM(HOURS) as total 
    FROM 
        PWM.EMP_TIMESHEET 
    WHERE 
        DATE     > '2011-01-01' 
        and DATE < '2011-10-01' 
    GROUP BY 
        EMPID 
    ) as E3 where E1.empid = E2.empid and E1.empid =E3.empid

select emp.empname, ab.yearmonth, total_hours*ehr.hour_rate
from employee emp
inner join
(
select b.yearmonth,b.empid, sum(b.no_of_hours) total_hours
(
select CAST(datepart(year,ts.date) AS VARCHAR(6)) + CAST(datepart(month,ts.date) AS VARCHAR(6)) as yearmonth,
ts.empid, ts.no_of_hours, date
from Employee_timesheet ts
) b
group by b.yearmonth, b.empid
) ab on ab.empid = emp.empid
inner join employee_hour_rate ehr on ehr.empid = emp.empid

I believe following query shud work. Natural join. Group by month

select EE.empname, ET.yearmonth, sum(ET.total_hours)*EHR.hour_rate payrate
from Employee EE, Employee_timesheet ET, Employee_hour_rate EHR
group by extract(month from date)

select e.empid,
e.empname,
to_date(date,'MM') as month,
sum(et.no_of_hours)*ehr.hour_rate
from Employee as e, Employee_timesheet as et, Employee_hour_rate as ehr
where e.empid = et.empid
and e.empid = ehr.empid
group by e.empid, e.empname, to_date(date,'MM'), ehr.hour_rate

SELECT
e.empid,
e.empname,
CAST(YEAR(t.date) AS VARCHAR) + '/' + CAST(MONTH(t.date) AS VARCHAR) AS ReportingMonth,
SUM(no_of_hours) * r.hour_rate AS Total
FROM
Employee AS e INNER JOIN
Employee_timesheet AS t ON e.empid = t.empid INNER JOIN
Employee_hour_rate AS r ON e.empid = r.empid
GROUP BY
e.empid, e.empname, r.hour_rate, CAST(YEAR(t.date) AS VARCHAR) + '/' + CAST(MONTH(t.date) AS VARCHAR)

select e.epname,v.total from Employee e JOIN
(select sum(empt.hours)*empr.rate TOTAL_SAL,empid
FROM employee_timesheet empt JOIN employee_hour_rate empr
ON empid
WHERE
empt.date between (select trunc(sysdate, 'MM')as start_date from dual) AND (select add_months(trunc(sysdate, 'MM'), 1) -1 as last_date from dual)
group by empid)
USING empid

SELECT E.ENAME,SUM(ET.NO_OF_HOURS*ER.HOUR_DATE) FROM EMPLOYEE E,EMPLOYEE_TIMESHEET ET,EMPLOYEE_HOR_RATE EH
WHERE E.EMPID=ET.EMPID AND EH.EMPID=E.EMPID
AND TO_CHAR(DATE,'MM')=TO_CHAR(SYSDATE,'MM')
GROUP BY E.ENAME

create table employee (
empid int auto_increment primary key,
name varchar(20))TYPE=InnoDB;

create table employee_time(
timeid int(10) auto_increment primary key,
emp_id int,
no_of_hours int(10),
edate datetime,
index(emp_id),
foreign key (emp_id) references employee(empid))TYPE=InnoDB;

create table employee_rate(
rateid int auto_increment primary key,
emp_id int,
rate int,
foreign key (emp_id) references employee(empid))TYPE=InnoDB;

insert into employee(empid, name) values(1, 'adam');
insert into employee(empid, name) values(2, 'eve');

insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02-26');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02,25');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02-24');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-03-01');

insert into employee_time(emp_id,no_of_hours,edate) values(2,8,'2013-02-26');
insert into employee_time(emp_id,no_of_hours,edate) values(2,8,'2013-02,25');

insert into employee_rate(emp_id,rate) values(1,10);
insert into employee_rate(emp_id,rate) values(2,10);

select * from employee;
select * from employee_time;
select * from employee_rate;

select name, sum(no_of_hours*rate) as salary from employee e,employee_time t, employee_rate r
where e.empid = t.emp_id and t.emp_id = r.emp_id and t.edate between '2013-02-01' and '2013-02-28' group by name

select e.empid, e.empname, T.yearR, T.monthR, T.salary
from guol.emp e left join
(
select h.empid as eid, extract(year from t.dateE) as yearR, extract(month from t.dateE) as monthR, sum(t.no_of_hours*h.rate) as salary
from guol.emp_h h, guol.emp_t t
where h.empid=t.empid
group by h.empid, extract(year from t.dateE), extract(month from t.dateE) ) T on e.empid=T.eid
;

mysql> select * from employee;
+-----+----------+
| eid | ename |
+-----+----------+
| 1 | Abhishek |
| 2 | Nikhil |
| 3 | Rama |
| 4 | Vidyut |
| 5 | Pavan |
+-----+----------+
5 rows in set (0.00 sec)

mysql> select * from timesheet;
+------+--------+------------+
| eid | no_hrs | cdate |
+------+--------+------------+
| 1 | 100 | 2011-06-30 |
| 2 | 105 | 2011-06-30 |
| 3 | 90 | 2011-06-30 |
| 4 | 85 | 2011-06-30 |
| 5 | 110 | 2011-06-30 |
+------+--------+------------+
5 rows in set (0.00 sec)

mysql> select * from hr_rate;
+------+---------+
| eid | hr_rate |
+------+---------+
| 1 | 50 |
| 2 | 60 |
| 3 | 40 |
| 4 | 30 |
| 5 | 80 |
+------+---------+
5 rows in set (0.00 sec)

mysql> select Employee.ename,timesheet.no_hrs,hr_rate.hr_rate,timesheet.no_hrs*hr_rate.hr_rate as TOTAL
-> from Employee inner join timesheet using (eid)
-> inner join hr_rate using (eid)
-> where DAY(timesheet.cdate) = DAY(LAST_DAY(CURDATE()));
+----------+--------+---------+-------+
| ename | no_hrs | hr_rate | TOTAL |
+----------+--------+---------+-------+
| Abhishek | 100 | 50 | 5000 |
| Nikhil | 105 | 60 | 6300 |
| Rama | 90 | 40 | 3600 |
| Vidyut | 85 | 30 | 2550 |
| Pavan | 110 | 80 | 8800 |
+----------+--------+---------+-------+
5 rows in set (0.00 sec)