How To Find The Total Between The Dates For Each Values

Table1 Period datefrom dateto code id 01/2012 18/12/2011 28/12/2011 A Emp1 01/2012 11/01/2012 14/01/2012 B Emp1 02/2012 20/12/2011 25/12/2011 A Emp2 02/201

Solution 1:

SET DATEFORMAT dmy

SELECT
    ID,
    Period,
    (SELECT ISNULL(SUM(DATEDIFF(DAY,datefrom,dateto) + 1),0)
        FROM Test 
        WHERE DATEDIFF(DAY,datefrom,dateto) > 0 and code = 'A'
        and x.Period = Period and x.id = id)
     as ACode,
    (SELECT ISNULL(SUM(DATEDIFF(DAY,datefrom,dateto) + 1),0)
        FROM Test 
        WHERE DATEDIFF(DAY,datefrom,dateto) > 0 and code = 'B'
        and x.Period = Period and x.id = id)
     as Bcode
FROM TABLENAME x
GROUP BY
    Period,
    ID

The plus one added to the date diff is to account for part days.