Find the days difference between two dates per month for employee with same id and different start and end date in sql server 2008

Question

I have a table EmployeeProject with three columns empid, startdate , enddate.

I want to fetch the number of days between two dates per month with same empid and different startdate and enddate.

Empid | Startdate | Enddate  |
-----------------------------
1     | 20160115  | 20160330 |
1     | 20160101  | 20161231 |
2     | 20161001  | 20161031 |
2     | 20161215  | 20170131 |

I want output to be as below:

Empid | StartDate | Enddate  | Monthname | Days |
------------------------------------------------
1     | 20160115  | 20160330 | Jan       | 15   |
1     | 20160115  | 20160330 | Feb       | 29   |
1     | 20160115  | 20160325 | Mar       | 25   |
1     | 20160101  | 20161229 | Jan       | 31   |
1     | 20160101  | 20161231 | Feb       | 29   |
2     | 20161001  | 20161031 | Oct       | 31   |
2     | 20161215  | 20170131 | Dec       | 15   |
2     | 20161215  | 20170131 | Jan       | 31   |

Answer 1

I assume that you have errors in your example.
Check this and see if that is what you need (change MyTable)

with        cte (Empid,Startdate,Enddate,month_offset,n) as 
            (
                select      t.Empid,t.Startdate,t.Enddate,datediff(month,t.Startdate,t.Enddate),1
                from        MyTable

                union all

                select      Empid,Startdate,Enddate,month_offset-1,n+1
                from        cte
                where       month_offset > 0
            )

select      Empid,Startdate,Enddate
           ,left(datename(month,dateadd(month,month_offset,Startdate)),3)   as Monthname 

           ,datediff 
            (
                day
               ,case month_offset when 0 then Startdate else dateadd(month,datediff(month,0,Startdate)+month_offset,0) end
               ,case n when 1 then Enddate else dateadd(month,datediff(month,0,Startdate)+month_offset+1,0) end
            )   as days



from        cte

order by    Empid,Startdate,Enddate
           ,case month_offset when 0 then Startdate else dateadd(month,datediff(month,0,Startdate)+month_offset,0) end