How Do I Average Counts of Employees? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

How Do I Average Counts of Employees?

Author

Topic

Bill Z
Starting Member

27 Posts

Posted - 2014-12-02 : 13:50:50

This is my first attempt to average anything using SQL.

Again, I am new to this and tried some examples both here and on the internet but they don’t work.

My SQL server is T-SQL running on Windows 2008 Server

The code below will count the specific employees. This runs OK but what I need is an average number of employees in each period using @startDate and @endDate.

How do I change this SQL to give me the average number of employees and not the count?


DECLARE @startDate DATETIME , @endDate DATETIME, @startTime DATETIME
set @startDate = '20141101'  
set @endDate   = '20141202'  
set @startTime = '11:59'

SELECT LABORLEVELNAME4 as Campus, (count(PERSONNUM)) as acCt 
FROM  VP_EMPCUREARNTIME 
WHERE  INPNCHENTEREDONDTM <> ''
  and  EVENTDATE >= @startDate
  and  EVENTDATE <= @endDate
  and  LABORLEVELNAME2 = 'SU'
  and  LABORLEVELNAME3 = LABORLEVELNAME4
  and  LABORLEVELNAME5 = '0'
  and  LABORLEVELNAME6 = '0'
  and  convert(char(5), STARTDTM, 108) <= @startTime
GROUP BY LABORLEVELNAME4

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 14:12:50

Average by what? That is for any given period, the number of periods = 1 and the average number of employees = count of the employees. Do you want to sub-divide the periods somehow? But laborlevel perhaps?

Bill Z
Starting Member

27 Posts

Posted - 2014-12-02 : 14:29:17

You have very good questions.

Each day there may be a different number of employees in each LABORLEVELNAME4 value in the database.

For example: date 11/01 there may be 18 employees (different PERSONNUM) with LABORLEVELNAME4 = 001. On date 11/02 there may be only 16 employees with LABORLEVELNAME4 = 001 and so on. There can be 250 different values in LABORLEVELNAME4.
What I want to know is the average number of employees in each LABORLEVELNAME4 for each day beginning with @startDate through @endDate.

I do hope this is clear. I’m trying to make is so.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

Bill Z
Starting Member

27 Posts

Posted - 2014-12-02 : 14:59:11

Just maybe this explains some what better.

Here I get a count for each day for each Campus. What I need is the average for each Campus. See, Campus 001 has 41, 44, 42, 37 & 34. One value for each day. What I want is 39.6 for Campus 001.


DECLARE @startDate DATETIME , @endDate DATETIME, @startTime DATETIME
set @startDate = '20141117'  
set @endDate   = '20141121'  
set @startTime = '11:59'

SELECT top(20)  LABORLEVELNAME4 as Campus,  COUNT(PERSONNUM) as EmpCt 
FROM  VP_EMPCUREARNTIME 
WHERE  INPNCHENTEREDONDTM <> ''
  and  EVENTDATE >= @startDate
  and  EVENTDATE <= @endDate
  and  LABORLEVELNAME2 = 'SU'
  and  LABORLEVELNAME3 = LABORLEVELNAME4
  and  LABORLEVELNAME5 = '0'
  and  LABORLEVELNAME6 = '0'
  and  convert(char(5), STARTDTM, 108) <= @startTime
GROUP BY EVENTDATE, LABORLEVELNAME4

The output is:

Campus EmpCt
001 41
001 44
001 42
001 37
001 34
003 9
003 10
003 10
003 9
003 9
018 20
018 24
018 25
018 25
018 20
019 1
019 1
019 1
019 1
019 1

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 15:12:30

So, make your original query a subquery and wrap it like this:


select Campus, avg(empCt)
from (

SELECT top(20)  LABORLEVELNAME4 as Campus,  COUNT(PERSONNUM) as EmpCt 
FROM  VP_EMPCUREARNTIME 
WHERE  INPNCHENTEREDONDTM <> ''
  and  EVENTDATE >= @startDate
  and  EVENTDATE <= @endDate
  and  LABORLEVELNAME2 = 'SU'
  and  LABORLEVELNAME3 = LABORLEVELNAME4
  and  LABORLEVELNAME5 = '0'
  and  LABORLEVELNAME6 = '0'
  and  convert(char(5), STARTDTM, 108) <= @startTime
GROUP BY EVENTDATE, LABORLEVELNAME4 

) subq

group by Campus

Bill Z
Starting Member

27 Posts

Posted - 2014-12-02 : 16:04:27

Yes. Yes!! Thanks.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources