Summing data based on distinct dates

I am trying to get an accurate count of total days a tool is out for rental.

Here is a data sample:

CREATE Table #tmpToolRentalDays
(   
    ToolId          BIGINT, 
    StartDate       DATETIME, 
    EndDate         DATETIME,
    RentalDays      FLOAT
)

INSERT INTO #tmpToolRentalDays(ToolId, StartDate, EndDate, RentalDays) 
values
(39,    '2016-02-01 00:00:00.000',  '2016-02-01 00:00:00.000',   1),
(39,    '2016-02-01 00:00:00.000',  '2016-02-02 00:00:00.000',  2),
(39,    '2016-02-04 00:00:00.000',  '2016-02-05 00:00:00.000',  2),
(39,    '2016-02-05 00:00:00.000',  '2016-02-06 00:00:00.000',  2),
(39,    '2016-02-06 00:00:00.000',  '2016-02-07 00:00:00.000',  2),
(36,    '2016-02-07 00:00:00.000',  '2016-02-28 00:00:00.000',  22),
(39,    '2016-02-08 00:00:00.000',  '2016-02-09 00:00:00.000',  2),
(39,    '2016-02-09 00:00:00.000',  '2016-02-10 00:00:00.000',  2),
(11,    '2016-02-14 00:00:00.000',  '2016-02-28 00:00:00.000',  15),
(39,    '2016-02-18 00:00:00.000',  '2016-02-21 00:00:00.000',  4)

SELECT * from #tmpToolRentalDays

A tool can go out for a day and return the same day, then go back out again on the same day. This should be 1 day. I am trying to avoid counting a date like 02-01 twice.

My intention is to get two columns:

ToolID  Rental Days 
39      13
36      22
11      15

How can I achieve that?

I think this will get you what you want.

It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1.

It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates. This gives you one record per toolid for every day within the range of rental dates.

Lastly, it groups by the toolID, and counts the distinct dates that the tool was rented to get rid of duplicate date values.

;WITH Dates(Date_Day) AS
(
    SELECT Convert(DateTime, '2016-01-01') AS Date_Day
UNION ALL
    SELECT DateAdd(day, 1, Date_Day) FROM Dates
    WHERE Date_Day < '2016-03-01'
)
SELECT 
    Rental_Dates.ToolId, 
    Count(DISTINCT Calendar_Dates.Date_Day) 
FROM
    Dates Calendar_Dates
Inner Join
    #tmpToolRentalDays Rental_Dates
        ON
            Calendar_Dates.Date_Day BETWEEN Rental_Dates.StartDate AND Rental_Dates.EndDate 
GROUP BY 
    Rental_Dates.ToolId;

StackExchange Database Administrators Q#139290, answer score: 7