Need to show the number of days between to dates in a pivot when the source has multiple departure and arrival dates in SQL Server

Question

I am working on a report for a costumer in which he wants to know how many days each of his drivers has spent abroad. He wants the format to be

| RoadmapNr | Roadmapdate| Driver | January | Febr | March | April | May | and so on
| c 9231    | 2023-03-07 | John   |    13   |  28  |   6   |   0   |  0  |
| a 2542    | 2023-02-25 | Mark   |     0   |  7   |  21   |   0   |  0  |

I have done a query for this but the problem is that i can't get the corect date for the last month, it's always 1 day short.

In John's case the table that holds the dates of departure and arrival has 2 rows. DepartureDate = '2023-01-19' ArrivalDate = '2023-02-15' DepartureDate = '2023-02-15' ArrivalDate = '2023-03-06' -> in this case in march the result is 5 instead of 6. I need to count the firts departure date and the last arrival date, and not count the duplicate dates in between.

I have done something like this:

First i created a table in which i have put all the dates

IF OBJECT_ID('dbo.DateTable', 'U') IS NULL 
BEGIN
    CREATE TABLE dbo.DateTable
    (
        [Date] DATE PRIMARY KEY
    );

 

    DECLARE @StartDate DATE = '20000101'; 
    DECLARE @EndDate DATE = '20991231';    

 

    WHILE @StartDate <= @EndDate
    BEGIN
        INSERT INTO dbo.DateTable ([Date])
        VALUES (@StartDate);

 

        SET @StartDate = DATEADD(DAY, 1, @StartDate);
    END
END

After i created a function to count the days in each month of a year

CREAT FUNCTION daysabroad
(
    @departuredate date, @arrivaldate date
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
    SELECT MONTH([Date]) as month, COUNT([Date]) as c
    FROM dbo.DateTable
    WHERE [Date] >= @departuredate AND [Date] < @arrivaldate
    GROUP BY MONTH([Date])
);

And then i've made the query to give me the results i need.

DECLARE @tblextern TABLE (RoadmapNr varchar(100), RoadmapDate date, Driver varchar (100), DepartureDate date, ArrivaldDate date)

INSERT INTO @tblextern
SELECT fp.RoadmapNr , fp.RoadmapDate, fpc.Driver , fpd.DepartureDate , fpd.ArrivaldDate 
FROM Roadmaps_Details fpd
INNER JOIN Roadmaps fp ON fpd.Roadmap_id = fp.Roadmap_id
INNER JOIN Drivers fpc ON fp.Driver_id = fpc.Driver_id


;WITH v AS 
(
  SELECT RoadmapNr , RoadmapDate, Driver , DepartureDate , ArrivaldDate 
  FROM @tblextern 
)

SELECT 
* 
INTO #tmptbl
FROM v 
CROSS APPLY daysabroad (v.DepartureDate , v.ArrivaldDate ) AS f
PIVOT (MAX(c) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p

OPTION (MAXRECURSION 0)
 

SELECT RoadmapNr , RoadmapDate, Driver , 
  isnull(SUM([1]),0) as ian,
  isnull(SUM([2]),0) as feb,
  isnull(SUM([3]),0) as mar,
  isnull(SUM([4]),0) as apr,
  isnull(SUM([5]),0) as mai,
  isnull(SUM([6]),0) as iun,
  isnull(SUM([7]),0) as iul,
  isnull(SUM([8]),0) as aug,
  isnull(SUM([9]),0) as sep,
  isnull(SUM([10]),0) as oct,
  isnull(SUM([11]),0) as noi,
  isnull(SUM([12]),0) as dec,
  SUM(isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0)) as Total
FROM #tmptbl 
GROUP BY RoadmapNr, RoadmapDate, Driver 
ORDER BY RoadmapDate, Driver

DROP TABLE #tmptbl

Answer 1

I've found the answer

Needed to select from first date of departure to the last date of arrival.

;WITH v AS 
(
  SELECT RoadmapNr , RoadmapDate, Driver , min(DepartureDate) as DepartureDate , max(ArrivaldDate) as ArrivalDate 
  FROM @tblextern 
group by RoadmapNr, RoadmapDate, Driver
)