General SQL Question

Hey guys,

Maybe one of you guys can help me out figuring this SQL delimma. Here's the deal :

I have 3 tables (Employee, PayGroup and Work_Summary).

Employee
------------
Emp_Id (PK)
Loc_Id


PayGroup
------------
Paygroup_Id (PK)
PayGrp_Name (FK to Loc_Id in Employee table)
End_Date


Work_Summary
--------------------
Wrks_Id (PK)
Emp_Id (FK to Emp_Id in Employee table)
Wrks_Work_Date
Wrks_Base_Rate

The Paygroup table has one record per employee
The Work_Summary table has multiple records per employee.

The task is to generate a report as such :

Emp_Id Rate1 Rate2 Rate3 Rate4 Rate 5 Avg
-----------------------------------------------------------------------------
1000 20.200 21.200 25.200 21.000 19.000 avg val
........
........

This is how the 'rate1, rate2' values are to be calculated :

1) For every employee, start off by matching the LOC_ID in the employee table to the PayGrp_Name in the Paygroup table. This gives you one and only one record.

2) Pick up the End_Date from the Paygroup table for this employee and substract 7 from this date (basically go back a week).

3) Match this date to the Wrks_Work_Date column from the Work_Summary table based on the employee's Id and look up the value in Wrks_Base_Rate column. This gives you the value for 'Rate5'.

Then, using the same End_Date value that was used earlier in step 2), go back 14 days, and do the same as mentioned above in Step 3). This gives you 'Rate 4'.

Similarly, 'Rate 3' is minus 21 days, 'Rate 2' is minus 28 days and 'Rate 1' is minus 35 days.

But they all need to appear on the same line in the report.

And then the average that is calculated in the last column is the total of all NON-ZERO 'Rate' values divided by the number of non-zero rates. Eg :

Rate1 Rate2 Rate3 Rate4 Rate5
10.05 20.50 10.00 0.00 0.00

In this case, the avg is (10.05 + 20.50 + 10.00)/3
and NOT (10.05 + 20.50 + 10.00 + 0.00 + 0.00)/5

This is one complex SQL to me !! )

Anyone that can help me out and teach me a few things in the process ??

Thanks in advance for your help !

You didn't say what DB, this is how it could be done with Oracle. The rate's are easy but getting the avg is a little tricky. The decode is like an inline if that I am using to come up with the denominator for the avg calculation. You should be able to find the equivilent for your db:

Dude,

Thanks for the reply. Really appreciate it. A few questions/comments :

1) What is the decode trying to do ?

2) Also, when I run the SQL, it shows the rates 1 thru 5, but the average is not being computed correctly. (if all but one rate has a value, the average is still showing up as 0)

3) If I want the rates 1 thru 5 to be rounded UP to the nearest half cent, is there a function to do that ?

Thanks again !
S

Hedge,

Also, should I not be joining the emp table to the work_summary table in your SQL using something like this in the where clause :

where ws1.emp_id = e.emp_id
and ws2.emp_id = e.emp_id
and ws3.emp_id = e.emp_id
and ws4.emp_id = e.emp_id
and ws5.emp_id = e.emp_id

1. The decode is trying to come up with the denominator by doing a test on each rate, if it is 0 then return 0 otherwise return 1... by adding these up we should be able to get the correct denominator.

2. I looked at it again and it seems to me that it should work, maybe split the query to show the numerator and denominator seperately to see which one is wrong.

3. yes, look at the round function

Yep, It looks like I missed the correct join