sql - count total transaction with in a date range -

i have data: tb_leave

id   |   empid   |  startdate   |   enddate 1    |     1     |  01/02/2014  |  01/05/2014     ------ 2, 3, 4, 5 2    |     2     |  01/03/2014  |  01/03/2014     ------ 3 3    |     3     |  01/04/2014  |  01/07/2014     ------ 4, 5, 6, 7 4    |     4     |  01/03/2014  |  01/07/2014     ------ 3, 4, 5, 6, 7 5    |     5     |  01/09/2014  |  01/09/2014     ------ 9 

i want return total leave in specific days. out put:

total   |   date   1     |   01/02/2014   3     |   01/03/2014   3     |   01/04/2014   3     |   01/05/2014   2     |   01/06/2014   2     |   01/07/2014   1     |   01/09/2014 

you can expand dates using recursive cte , use group by:

with cte (       select startdate thedate, enddate       tb_leave       union       select dateadd(day, 1, startdate), enddate       cte       startdate < enddate ) select thedate, count(*) cte group thedate (maxrecursion 0); 

note: assumes there not more 99 days 1 row. otherwise, add maxrecursion option. can joining in numbers table, such spt_values:

select dateadd(day, v.number - 1, startdate) thedate, count(*) tb_leave l join      spt_values v      on dateadd(day, v.number - 1, startdate) <= l.enddate group dateadd(day, v.number - 1, startdate) order 1;