Group on particular date every year

Hi,
We have a load of personal records like (e.g.) these
Name, Function, Stardate,Enddate
Fred,assistent, div1, 1/1/2001, 31/12/2004
Richard,assistent, div2, 1/1/2001, 28/2/2003
Richard,director, div2, 1/3/2003, 31/7/2003
...
Now we need a matrix showing how many people works at date 1/7 of a
particular year and what function they had
2001 2002 2003 2004
ass dir ass dir ass dir ass dir
Div1 1 0 1 0 1 0 0 0
Div2 1 0 1 0 0 1 0 0
---
Total 2 0 2 0 1 1 0 0
The biggest problem I have is determining who is at 1/7 in what function and
group on that info.
Can somebody get me started? I'm trying to work with the IIF function but
with no good results.
Thak you very much
DanielDaniel,
If you aren't using SQL Server 2005, then this won't work. SQL Server
2005 introduced a function called PIVOT. Check out the SQL below to see
how it works.
SQL:
SELECT
div as Division, [2001-assistant], [2001-director], [2002-assistant],
[2002-director], [2003-assistant], [2003-director], [2004-assistant],
[2004-director]
FROM
(
SELECT div, '2001-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2001-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2001-01-07
00:00:00', 102))
UNION
SELECT div, '2002-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2002-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2002-01-07
00:00:00', 102))
UNION
SELECT div, '2003-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2003-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2003-01-07
00:00:00', 102))
UNION
SELECT div, '2004-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2004-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2004-01-07
00:00:00', 102))
) AS sourceTable
PIVOT
(count(name) FOR yearFunction IN
([2001-assistant],[2001-director],[2002-assistant],[2002-director],[2003-assistant],[2003-director],[2004-assistant],[2004-director]))
AS pivotTable
ORDER BY
div
Results: (formatting might get messed up because of word wrap)
Division 2001-assistant 2001-director 2002-assistant 2002-director
2003-assistant 2003-director 2004-assistant 2004-director
-- -- -- -- --
-- -- -- --
div1 1 0 1 0 1
0 1 0
div2 1 0 1 0 1
1 0 0
Hope your using 2005. Hope this helps.
-Josh
Daniel wrote:
> Hi,
> We have a load of personal records like (e.g.) these
> Name, Function, Stardate,Enddate
> Fred,assistent, div1, 1/1/2001, 31/12/2004
> Richard,assistent, div2, 1/1/2001, 28/2/2003
> Richard,director, div2, 1/3/2003, 31/7/2003
> ...
> Now we need a matrix showing how many people works at date 1/7 of a
> particular year and what function they had
> 2001 2002 2003 2004
> ass dir ass dir ass dir ass dir
> Div1 1 0 1 0 1 0 0 0
> Div2 1 0 1 0 0 1 0 0
> ---
> Total 2 0 2 0 1 1 0 0
> The biggest problem I have is determining who is at 1/7 in what function and
> group on that info.
> Can somebody get me started? I'm trying to work with the IIF function but
> with no good results.
> Thak you very much
> Daniel