asked on

sql average syntax

I am trying to return an average amount for the month by JobNum on three columns generated by this query:
Select VP_TOTALS.APPLYDATE As Dates, VP_TOTALS.LABORLEVELDSC2 As DepartmentNam,VP_TOTALS.LABORLEVELNAME2 As [DepartmentNum],
VP_TOTALS.LABORLEVELDSC3 As JobNam,VP_TOTALS.LABORLEVELNAME3 As [JobNum], VP_TOTALS.TIMEINSECONDS / 3600 As Hours,
CASE
WHEN VP_TOTALS.PAYCODENAME = 'Regular' THEN VP_TOTALS.WAGEAMOUNT ELSE 0 end As RegWages,
case WHEN VP_TOTALS.PAYCODENAME = 'Overtime' THEN VP_TOTALS.WAGEAMOUNT Else 0 end As OvertimeWages,
case WHEN VP_TOTALS.PAYCODENAME = 'Holwrkd' THEN VP_TOTALS.WAGEAMOUNT ELSE 0 end As HolwrkdWages
From VP_TOTALS
Where (VP_TOTALS.APPLYDATE >= Convert(DateTime,'2009-01-01 00:00:00',102) And
VP_TOTALS.APPLYDATE <= Convert(DateTime,'2009-01-31 00:00:00',102)) And
VP_TOTALS.LABORACCTID > 0
Group By VP_TOTALS.LABORLEVELNAME3,VP_TOTALS.APPLYDATE, VP_TOTALS.LABORLEVELDSC2, VP_TOTALS.LABORLEVELDSC3, VP_TOTALS.LABORLEVELNAME2, VP_TOTALS.TIMEINSECONDS, VP_TOTALS.WAGEAMOUNT,VP_TOTALS.PAYCODENAME
order by VP_TOTALS.LABORLEVELNAME3

How can I return only one JobNum for each Job number with an average for the month amount for the three columns RegWages, OvertimeWages and Holwrkdwages?

Can someone please help ?

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

BTW,

VP_TOTALS.APPLYDATE <= Convert(DateTime,'2009-01-31 00:00:00',102))

does not count 31th January. You should write:

VP_TOTALS.APPLYDATE <= Convert(DateTime,'2009-01-31 23:59:59',102))

If you need year on output then add following expression to the SELECT list and GROUP BY:
Year(VP_TOTALS.APPLYDATE) As DateYear

Ryedog

ASKER

Thank you very much pcelba. Your code worked perfectly. the points are yours, but I was hoping I could find out one more thing. How can I exclude the JobNum's that return null for all three column's ?

Ryedog

ASKER

The perfect solution. Thank you !

Pavel Celba

To avoid nulls you have to add one or two more conditions to WHERE clause:

Where (VP_TOTALS.APPLYDATE >= Convert(DateTime,'2009-01-01 00:00:00',102) And 
  VP_TOTALS.APPLYDATE <= Convert(DateTime,'2009-02-01 00:00:00',102)) And 
  VP_TOTALS.LABORACCTID > 0 AND
  VP_TOTALS.PAYCODENAME IN ('Regular','Overtime','Holwrkd')

-- If it still does not help add this one:
  and VP_TOTALS.WAGEAMOUNT IS NOT NULL

Open in new window

Ryedog

ASKER

Thank you. Very helpful

Pavel Celba

You are welocme!