-
Help with query (sorry if double posted)
Hey all, my last msg didn't post. I thought it might be because of the link so here it is without....
If anyone has a second to look at a query I would be incredibly grateful. My SQL skills are not the greatest but I'm working on 'em .
I'm using MySQL 5.0.x
What I have is a table of employees with name, salary, position, department, subdept, start_date, and end_date('1000-01-01' if no end date). What I am trying to get is a resultset with a breakdown of how many employees are employed in each dept and subdept and a company total for a given period of time. I need the break down to be each month.
So what i envision is a result with a column for dept, subdept, and each month out of the time period given in the query(e.g. 'Jan_07', 'Feb_07', 'Mar_07', etc.) which would contain the count for that month.
What I've come up with so far is kind of a mess but it might have something close if i'm lucky. It works for one month but i need to expand it out and i don't know how.
Code:
SELECT
dept_id,
subdepartmentid AS subdept_id,
fore_id,
COUNT(*) AS Jun_07
FROM
headcount
WHERE 1 = 1
AND dept_id IN (329,330,331,332,360,359)
AND subdepartmentid IN (1,65)
AND fore_id = 120
AND startdate < '2007-06-01'
AND (TerminateDate > '2007-06-30'
OR TerminateDate = '1000-01-01')
GROUP BY
dept_id,
subdept_id,
fore_id
-
At first, put the period inside not outside of the month
AND startdate > '2007-06-01'
AND (TerminateDate < '2007-06-30'
OR TerminateDate = '1000-01-01')
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|