Want work costing from SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Want work costing from SQL

Author

Topic

vinaym
Starting Member

8 Posts

Posted - 2011-05-27 : 05:30:08

I have Employee Salary table as following with data
EmployeeSalary
ID EmployeeId StartDate Sallary
1 25 2011-03-01 10000
2 18 2011-03-01 5000
3 31 2011-03-01 1250
4 25 2011-04-01 15000
5 18 2011-04-01 7500
6 31 2011-04-01 2500

I have work Table as following with data (not all fields)
Work
ID WorkId StartDate EndDate
1 25 2011-03-01 2011-06-01
2 18 2011-03-01 2011-06-01
3 31 2011-03-01 2011-07-01
4 25 2011-05-01 2011-08-01
5 18 2011-05-01 2011-09-01
6 31 2011-05-01 2011-06-01

one more table as a transaction of work as following with data

ID Employee_ID TransactionDate Duration Work_ID
Logsheet
2 18 2011-03-29 10 2
3 18 2011-03-29 10 2
4 25 2011-03-29 10 4
5 25 2011-03-29 10 1
6 25 2011-03-29 5 3
7 25 2011-03-29 5 2
8 31 2011-04-25 10 4
10 31 2011-04-26 30 4
14 31 2011-04-26 45 3
15 31 2011-04-26 30 5
16 18 2011-04-27 20 5
18 18 2011-04-27 70 5
19 18 2011-04-27 13 1
21 18 2011-05-21 10 1

Now I want costing of my work.

Condition: Transaction date and salary date connection.... e.g for transaction date 2011-03-29 and employee id 18 then salary should be consider 10000 and for same employee transaction date 22011-04-27 salary should be consider 15000.

Now I want four combinational types of out-put from these...

1) WorkID wise costing (Total)
2) WorkID wise costing (EmployeeID Wise)
3) EmployeeID wise costing (Date Wise)
4) EmployeeID wise costing (WorkID Wise)

I have all of them out-put but I'm not able to follow my Condition.
I have written following query

SELECT Logsheet.Employee_ID, SUM(Logsheet.Duration) AS Duration, SUM(Logsheet.Duration) * (EmployeeSalary.Sallary / 10500) AS Costing
FROM Logsheet INNER JOIN
Work ON Logsheet.Work_ID = Work.ID INNER JOIN
EmployeeSalary ON Logsheet.Employee_ID = EmployeeSalary.EmployeeId
WHERE (Job_Activity.Job_ID = 1)
GROUP BY Logsheet.Employee_ID

So please guide me in my query

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-05-27 : 05:55:23

Could not understand logic of calculation.
why 10500 is hardcoded in your query ?

Vaibhav T

If I cant go back, I want to go fast...