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 (,,,,,,,,,,,)) AS p OPTION (MAXRECURSION 0) SELECT RoadmapNr , RoadmapDate, Driver , isnull(SUM(),0) as ian, isnull(SUM(),0) as feb, isnull(SUM(),0) as mar, isnull(SUM(),0) as apr, isnull(SUM(),0) as mai, isnull(SUM(),0) as iun, isnull(SUM(),0) as iul, isnull(SUM(),0) as aug, isnull(SUM(),0) as sep, isnull(SUM(),0) as oct, isnull(SUM(),0) as noi, isnull(SUM(),0) as dec, SUM(isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0) + isnull(,0)) as Total FROM #tmptbl GROUP BY RoadmapNr, RoadmapDate, Driver ORDER BY RoadmapDate, Driver DROP TABLE #tmptbl