I have a table Attendance and a table with Activity Types

I want to group per month the activities per persons in separate columns

Example table:

Attendance
----------

Person ID  

Date  

GateIn  

GateOut  

ActivityID  

Activity
--------

ID  

Name

Values are Activity.Name are: Present, Absence, Ill and Holiday

I want to get a view like (Grouped per Person ID and month
----------------------------------------------------------

PersonID  

Month  

Year  

Absence  

Holiday  

Ill  

LessThan4Hours  

MoreThan4Hours  

MoreThan5Hours  

What is the best way to create this grouping??
I already have two SQL statements, but both seems to be too unfriendly

Who could help me out???
Thanks

```
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, COUNT(1) as Absence, 0 as Holiday, 0 as Ill, 0 as LessThan4Hours, 0 as MoreThan4Hours, 0 as MoreThan5Hours
    FROM Attendance att
    inner join Activities act on att.ActivityID = act.ID
    WHERE act.Name = 'Absence'
    GROUP BY year(att.Date), month(att.Date), att.PersonID
    UNION
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, 0 as Absence, COUNT(1) as Holiday, 0 as Ill, 0 as LessThan4Hours, 0 as MoreThan4Hours, 0 as MoreThan5Hours
    FROM Attendance att
    inner join Activities act on att.ActivityID = act.ID
    WHERE act.Name = 'Holiday'
    GROUP BY year(att.Date), month(att.Date), att.PersonID
    UNION
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, 0 as Absence, 0 as Holiday, count(1) as Ill, 0 as LessThan4Hours, 0 as MoreThan4Hours, 0 as MoreThan5Hours
    FROM Attendance att
    inner join Activities act on att.ActivityID = act.ID
    WHERE act.Name = 'Ill'
    GROUP BY year(att.Date), month(att.Date), att.PersonID
    UNION
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, 0 as Absence, 0 as Holiday, 0 as Ill, COUNT(1) as LessThan4Hours, 0 as MoreThan4Hours, 0 as MoreThan5Hours
    FROM Attendance att
    inner join Activities act on att.ActivityID = act.ID
    WHERE att.GateOut is not null
    AND (DATEDIFF(n, att.GateIn, att.GateOut)/60) <= 4
    AND act.Name = 'Present'
    GROUP BY year(att.Date), month(att.Date), att.PersonID
    UNION
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, 0 as Absence, 0 as Holiday, 0 as Ill, 0 as LessThan4Hours, COUNT(1) as MoreThan4Hours, 0 as MoreThan5Hours
    FROM Attendance att
    inner join Activities act on att.ActivityID = act.ID
    WHERE att.GateOut is not null
    AND (DATEDIFF(n, att.GateIn, att.GateOut)/60) > 4
    AND (DATEDIFF(n, att.GateIn, att.GateOut)/60) <= 5
    AND act.Name = 'Present'
    GROUP BY year(att.Date), month(att.Date), att.PersonID
    UNION
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, 0 as Absence, 0 as Holiday, 0 as Ill, 0 as LessThan4Hours, 0 as MoreThan4Hours, COUNT(1) as MoreThan5Hours
    FROM Attendance att
    inner join Activities act on att.ActivityID = act.ID
    WHERE att.GateOut is not null
    AND (DATEDIFF(n, att.GateIn, att.GateOut)/60) > 5
    AND act.Name = 'Present'
    GROUP BY year(att.Date), month(att.Date), att.PersonID

```

and i also worked this out in SELECT CASE, maybe even worse?

```
    SELECT a.PersonID, a.cYear, a.cMonth, SUM(a.Absence) AS Absence, SUM(a.Holiday) AS Holiday, SUM(a.Ill) AS Ill, SUM(a.LessThan4Hours) AS LessThan4Hours, SUM(a.MoreThan4Hours) AS MoreThan4Hours, SUM(a.MoreThan5Hours) AS MoreThan5Hours
    FROM 
    (
    SELECT year(att.Date) as cYear, month(att.Date) as cMonth, att.PersonID, CASE 
        WHEN act.Name = 'Ill' THEN 1
        WHEN act.Name = 'Holiday' THEN 0
        WHEN act.Name = 'Absence' THEN 0
        WHEN GateOut = null THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 5 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 4 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) <= 4 THEN 0
        ELSE 0 
      END AS Ill,
       CASE 
        WHEN act.Name = 'Ill' THEN 0
        WHEN act.Name = 'Holiday' THEN 1
        WHEN act.Name = 'Absence' THEN 0
        WHEN GateOut = null THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 5 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 4 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) <= 4 THEN 0
        ELSE 0 
      END AS Holiday,
      CASE 
        WHEN act.Name = 'Ill' THEN 0
        WHEN act.Name = 'Holiday' THEN 0
        WHEN act.Name = 'Absence' THEN 1
        WHEN GateOut = null THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 5 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 4 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) <= 4 THEN 0
        ELSE 0 
      END AS Absence,
      CASE 
        WHEN act.Name = 'Ill' THEN 0
        WHEN act.Name = 'Holiday' THEN 0
        WHEN act.Name = 'Absence' THEN 0
        WHEN GateOut = null THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 5 THEN 1
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 4 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) <= 4 THEN 0
        ELSE 0
      END AS MoreThan5Hours,
      CASE 
        WHEN act.Name = 'Ill' THEN 0
        WHEN act.Name = 'Holiday' THEN 0
        WHEN act.Name = 'Absence' THEN 0
        WHEN GateOut = null THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 5 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 4 THEN 1
        WHEN (DATEDIFF(n, gatein, gateout)/60) <= 4 THEN 0
        ELSE 0 
      END AS MoreThan4Hours,
      CASE 
        WHEN act.Name = 'Ill' THEN 0
        WHEN act.Name = 'Holiday' THEN 0
        WHEN act.Name = 'Absence' THEN 0
        WHEN GateOut = null THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 5 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) > 4 THEN 0
        WHEN (DATEDIFF(n, gatein, gateout)/60) <= 4 THEN 1
        ELSE 0 
      END AS LessThan4Hours
  from Attendance att
  inner join Activities act on att.ActivityID = act.ID 
  ) a
  GROUP BY a.PersonID, a.cYear, a.cMonth
  ORDER BY a.cYear DESC, a.cMonth DESC, a.PersonID

```