How to Count number of days?

Question

Using SQL Server 2005

Table1

ID FromDate ToDate

001 23-02-2009 25-02-2009
001 27-02-2009 29-02-2009
002 12-02-2009, 25-03-2009

...,

Table2

ID Name Total

001 Raja 30
002 Ravi 22

I want to get total day for the personid

Tried Query,

SELECT
   table2.Id, table2.name, table2.total, 
   datediff(day, table1.fromdate, table2.todate) 
FROM table1 
LEFT OUTER JOIN table2 ON table1.personid = table2.personid

Getting output

ID Name Total Days

001 Raja 30 3
001 Raja 30 3
...,

It should total the days and it should display in one line,

Note: Suppose I am selecting the particular period date means it should display that days only

For example

where date between 26-02-2009 to 03-03-2009, It should display

ID Name Total Days

001 Raja 30 3
...,

Because am taking date after 25-02-2009,

Expected Output

ID Name Total Days

001 Raja 30 6
002 Ravi 22 16

How to modify my query?

Answer 1

SELECT  table2.Id, table2.name, table2.total,
        COALESCE(
        (
        SELECT  SUM(DATEDIFF(day, table1.fromdate, table1.todate) + 1)
        FROM    table1
        WHERE   table1.personid = table2.personid
        ), 0) AS [days]
FROM    table2

Answer 2

I think a GROUP BY query would be simpler:

SELECT table2.Id, table2.name, table2.total, 
SUM(DATEDIFF(day, table1.fromdate, table1.todate)) AS Days
FROM table1 
left outer join table2 on 
table1.personid = table2.personid
GROUP BY table2.Id, table2.name, table2.total

Answer 3

DATEDIFF gives the number of days difference between two dates, so in the same way the different between 1 and 3 is 2 (3 - 1 = 2), DATEDIFF(d) is effectively D2 - D1. So to compensate for the extra day you want to count, you need to DATEADD a day to either (ToDate or FromDate) to offset your dates:

SELECT table2.id, table2.Name, table2.Total, SUM(DATEDIFF(d, DATEADD(d, -1, table1.FromDate), table1.ToDate))
    FROM table1
     INNER JOIN table2 ON table1.id = table2.id
    GROUP BY table2.id, table2.Name, table2.Total