CODEWITHSUNDEEP
sqlsql-servert-sql
Gabe
Gabe

Reputation: 6085

Retrieving monthly data for different types

I'm working on a report that needs to retrieve

All of these are split up by department (new marketing emps, new sales, etc).

In total, there'll be about 23 columns..

I'm using a temporary table by populating each field with an update. Here's a simplified example of what I'm doing. Employee table has most of the values needed (hiredate, termdate, etc). There's actually a join in each update and some more conditions. 

update #tmptbl
set MtdHiresSales = select count(empid) from emp e where e.dept = 012 
                           and hiredate between start_of_month() and getdate()
                           -- more predicates
set MtdHiresMkting = ...repeat... with e.dept=013

I'm sure there's a better way because there's a lot of code duplication. Are temporary tables appropriate in this case? I'm not sure how it could be done without one. Any suggestions?

Upvotes: 1

Views: 734

Answers (3)

Mikeb
Mikeb

Reputation: 6361

Maybe I'm confused or not seeing something, but why not just use aggregates?

SELECT count(*) as employeesPerDept, deptId
from mudkips
group by deptId

giving (for my sample data)

employeesPerDept    deptId
6                   100
4                   200

and 

select deptId, count(*) as empsHired, month(hiredate) as monthHired, 
year(hireDate) as yearHired
from mudkips
group by deptId, month(hireDate), year(hireDate)

which then is 

deptId  empsHired   monthHired  yearHired
100         4           1           2010
100         1           1           2011
100         1           2           2010
200         2           1           2010
200         1           2           2010
200         1           3           2010

Upvotes: 0

MatBailie
MatBailie

Reputation: 86745

You could use a common table expression to encapsulate the employee counting code...

WITH
  employee_counts
AS
(
   SELECT
     dept         AS "dept",
     x            AS "x",
     COUNT(empid) AS "employees"
   FROM
     emp
   WHERE
     hiredate BETWEEN start_of_month() AND getdate()
     -- etc, etc
   GROUP BY
     dept,
     x
)
UPDATE
  myTable
SET
  MtdHiresSales  = (SELECT employees FROM employee_counts WHERE dept = 012 AND x = myTable.y),
  MtdHiresMkting = (SELECT employees FROM employee_counts WHERE dept = 013 AND x = myTable.y)

Upvotes: 2

JNK
JNK

Reputation: 65177

You can probably use a CASE for this. For example:

SELECT 
SUM(CASE WHEN e.dept=012 
    AND Hiredate between Start_of_Month() and Getdate() 
    AND... 
    THEN 1 ELSE 0 END) AS 'MtdHiresSales',
...
FROM ...
WHERE ...

Upvotes: 3

Related Questions