sql - Get Month columns from datetime column and count entries -

sql - Get Month columns from datetime column and count entries -

i have next table:

| id | name | datea | timetowork | timeworked | |:--:|:----:|:----------:|:----------:|:----------:| | 1 |frank | 2013-01-01 | 8 | 5 | | 2 |frank | 2013-01-02 | 8 | null | | 3 |frank | 2013-01-03 | 8 | 7 | | 4 |jules | 2013-01-01 | 4 | 9 | | 5 |jules | 2013-01-02 | 4 | null | | 6 |jules | 2013-01-03 | 4 | 3 |

the table long, every person has entry every day in year. each person have date worked (datea), hours has work according contract (timetowork) , hours worked (timeworked). can see days person didnt work on day had to. when person took total day overtime.

what seek accomplish next table out of first 1 above.

| name | jan | feburary | march | ... | sum | |:----:|:----------:|:--------:|:-----:|:---:|:---:| |frank | 2 | 0 | 1 | ... | 12 | |jules | 5 | 1 | 3 | ... | 10 |

for each month want count days person took total day off , sum in sum column.

i tried select (case when datetime(month, datea = 1 count(case when timetowork - (case when timeworked 0 end) = timetowork 1 else 0 end) end) 'january' tsql not , code doent work @ all. btw using select command 40 lines.

i appreciate if help me or give me link source can read myself it.

if understand question right, gordon linoff's reply beginning, doesn't deal "full day off".

select name, sum(case when month(datea) = 01 , timeworked null 1 else 0 end) jan, sum(case when month(datea) = 02 , timeworked null 1 else 0 end) feb, ... sum(case when month(detea) = 12 , timeworked null 1 else 0 end) dec, sum(case when timeworked null 1 else 0 end) sum table t year(datea) = 2013 grouping name

this method solves problem?

sql tsql