5

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?

1
  • Could you change the schema at all, like a Rental table that was made of ToolId, CheckIn, CheckOut and then sum the distinct dates?
    – user57916
    May 23, 2016 at 21:47

4 Answers 4

7

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;
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Jun 5, 2016 at 5:02
7

Instead of doing an expensive CROSS JOIN plus aggregating a potentially large number of rows you can use this approach: SUM the days including the duplicates and subtract the number of rows with duplicate dates:

;with cte as 
( select  *,
     row_number()
     over (partition by ToolId 
           order by StartDate, EndDate) as rn
  from #tmpToolRentalDays
)
select t1.ToolId,
       -- sum including duplicate days
   SUM(t1.RentalDays)
       -- number of duplicate dates
   - COUNT(T2.RentalDays) As RentalDaysSum
from cte as t1
left join cte as t2
  on t1.ToolId = t2.ToolId 
 and t2.StartDate = t1.EndDate -- same day
 and t2.rn = t1.rn + 1         -- on the "next" row?
group by t1.ToolId;

SQL Server will calculate the ROW_NUMBER twice. To avoid this you you might simply add that column when you create the temp table (you wrote the table is generated).

ROW_NUMBER is needed here, because there may be mini-cartesian products of multiple single-day rentals. Without row numbers, every single-day rental would match every same-day rental, so e.g. for two rows you would get four as the result of the join. SUMs would be skewed particularly much if another multi-day rental either started or ended on the same day as many single-day rentals, because such a SUM would be multiplied too.

0
6

You can avoid the expensive recursive CTE by using a date table. These are easy to construct and very useful. I used a variation of the code here to generate one.

Note: I'm using a temp table for the date here. This is just for the demo code. The permanent solution should use a permanent date table. It should probably also include some/all of the columns I removed from the original code. (Day, Week, etc) These are very handy for all kinds of date related queries.

DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals

SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

-- this is just a holding table for intermediate calculations:

CREATE TABLE #dim
(
  [date]       DATE PRIMARY KEY
);

-- use the catalog views to generate as many rows as we need

INSERT #dim([date]) 
SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;

That plus the code you have above gives us this very simple code to generate your solution.

SELECT #tmpToolRentalDays.ToolId, COUNT(DISTINCT #dim.[Date]) AS Cnt
FROM #tmpToolRentalDays
JOIN #dim
    ON #dim.[Date] >= #tmpToolRentalDays.StartDate
    AND #dim.[Date] <= #tmpToolRentalDays.EndDate
GROUP BY #tmpToolRentalDays.ToolId

The COUNT DISTINCT gets rid of any duplicate dates.

0
-4

try this

SELECT ToolId,SUM(RentalDays) As RentalDaysSum
FROM #tmpToolRentalDays
GROUP BY ToolId , Cast(StartDate as DATE)
1
  • 5
    Unfortunately this doesn't eliminate the duplicate rental days when a tool is rented more than once on the same day. May 23, 2016 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.