• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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 ?
0
Ryedog
Asked:
Ryedog
  • 4
  • 3
1 Solution
 
pcelbaCommented:
You did not write what to do with VP_TOTALS.TIMEINSECONDS, so I'veremoved it.
Select 
  MONTH(VP_TOTALS.APPLYDATE) As DateMonth, 
  VP_TOTALS.LABORLEVELDSC2 As DepartmentNam,
  VP_TOTALS.LABORLEVELNAME2 As [DepartmentNum],
  VP_TOTALS.LABORLEVELDSC3 As JobNam,
  VP_TOTALS.LABORLEVELNAME3 As [JobNum], 
  AVG(CASE WHEN VP_TOTALS.PAYCODENAME = 'Regular' THEN VP_TOTALS.WAGEAMOUNT ELSE null END) As AvgRegWages, 
  AVG(CASE WHEN VP_TOTALS.PAYCODENAME = 'Overtime' THEN VP_TOTALS.WAGEAMOUNT ELSE null END) As AvgOvertimeWages,
  AVG(CASE WHEN VP_TOTALS.PAYCODENAME = 'Holwrkd' THEN VP_TOTALS.WAGEAMOUNT ELSE null END) As AvgHolwrkdWages
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,
  MONTH(VP_TOTALS.APPLYDATE), 
  VP_TOTALS.LABORLEVELDSC2, 
  VP_TOTALS.LABORLEVELDSC3, 
  VP_TOTALS.LABORLEVELNAME2 
order by VP_TOTALS.LABORLEVELNAME3

Open in new window

0
 
pcelbaCommented:
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

0
 
RyedogAuthor Commented:
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 ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RyedogAuthor Commented:
The perfect solution. Thank you !
0
 
pcelbaCommented:
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

0
 
RyedogAuthor Commented:
Thank you. Very helpful
0
 
pcelbaCommented:
You are welocme!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now