Aggregation on monthly base

I need to create a report containing the headcounts of the employees for each month. The data I can retrieve from the database looks like this:

Name

StartEmp

EndEmp

tom

1/jan/05

15/mrt/05

dirk

1/jan/05

31/mrt/06

jan

1/feb/05

NULL

In order to get the right information for the report the information should be represented in the following way:

January 2005

February 2005

March 2005

"April" 2005

Tom

1

1

0,5

1

Dirk

1

1

1

1

Jan

0

1

1

1

HeadCount

2

3

2,5

3

(there should be more data in the 2nd table but it's a sliced representation of the data. Sliced by 2 report parameters: startdate (=1 January 2005) and enddate (=30 April 2005) )

I need some help about building up the query to aggregate the info from table1 to the format of table2.

Hi,

Are you using SQL Server 2005? If so, check out the PIVOT function in the Books Online.

HTH

|||

Yes I use sql server 2005. I looked at the pivot function and it can help me I guess (I don't know the function that well yet). But I still think that just using this function will not give me the wanted result.

My goal is to have a report with 2 parameters: a startdate and an enddate. For example 1 January 2005 as startdate, 31 December 2005 as enddate. Then the report should show a total headcount per month. So each column is a month, each row is an employee. (It should be shown in a bar-chart afterwards but I first need to retrieve/ manipulate the data)

|||

Hmmm... this is not really the strong point of databases.
It is possible but it will be a quite complicated procedure and I'm not sure it belongs on the database tier.

If you really feel it belongs there, it can all be done with standard syntax and a lot of creativity :-) But I would implement this as an application procedure.

|||

with application procedure; do you mean the CLR stored procedures which can be written in .NET code? And will that type of stored procedure be less complex?

I tried making a stored procedure, and I used the following article a bit (it's about pivot tables for MS SQL Server): http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp But I'm stuck at the point where each month is a column. I really don't know how to start adding the headcounts

Please take a look at the results (3 tables almost on top of page) the sp. already creates: http://users.telenet.be/master/ict/uspHeadCount.html

I was thinking: In the headcount table I have a column for each month, so ... I iterate through the employees table, and add a row to the headcount table for each employee. But how do I add an aggregation to the month rows? Because they are dynamic.

Each row in the headcount table should contain an Employee and per employee row the columns should contain this logic:
IF
([end of employment] > Month OR [end of employment] is null)
AND [start of employment] < Month
THEN headcount = 1
ELSE
...(lets leave the else out of it for now )
(Month is the date per column)

Everything that can help me is welcome!!!! Feedback, comments, guidance on the working method, tips, suggestions, ...