asked
SQL Logic for counting days
Hi All, Hope you are doing good,I recently came across a complicated sql logic I need to write.I need your suggestions as I could not figure out how do I need to accomplish it. I have a table where I have Row_ID,ID,Journey_Start_Date,Journey_End_Date.I need to count number of travel days for each ID. Below is the sample data. ID Journey_Start_Date Journey_End_Date 123 2017-01-01 2017-01-10 123 2017-02-01 2017-02-12 123 2017-02-12 2017-02-15 For each record I need to count "total_travel_days" ,the challenge is for ID 123 I have 3 records and 2nd and 3rd record have overlapping day,I need not count the the same day again in the 3rd trip as it is already counted in the second trip.I need to accomplish this in the same query without using any temp tables as it can be very huge data set. Some point to be assumed: 1.Same ID can have multiple overlapping trips 2.The data may not be in order of trips 3.I do not have calendar table but can create if needed. 4.Data is for last one year dates. Thanks in advance for your valuable suggestions.. ---=====Sample Data========= if OBJECT_ID('trips') is not null drop table trips go create table trips ( row_id int identity(1,1) ,ID INT ,Journey_Start_Date date ,Journey_End_Date date ) Insert into trips select 123,'2017-01-01','2017-01-10' union select 123,'2017-02-01','2017-02-12' union select 123,'2017-02-12','2017-02-15' union select 100,'2017-02-12','2017-02-15'
I'd look at doing something with a recursive CTE that generates a date list, and then joins to the original table. Using your sample data, I came up with the following. It gives me 4 days for ID 100 and 25 for ID 123, which I think would meet your needs: DECLARE @StartDate DATE DECLARE @EndDate DATE IF ( OBJECT_ID('tempdb..#tmptrips') IS NOT NULL ) BEGIN DROP TABLE #tmptrips END CREATE TABLE #tmptrips ( row_id INT IDENTITY(1, 1), ID INT, Journey_Start_Date DATE, Journey_End_Date DATE ) INSERT INTO #tmptrips SELECT 123, '2017-01-01', '2017-01-10' UNION SELECT 123, '2017-02-01', '2017-02-12' UNION SELECT 123, '2017-02-12', '2017-02-15' UNION SELECT 100, '2017-02-12', '2017-02-15' SELECT @StartDate = MIN(T.Journey_Start_Date), @EndDate = MAX(T.Journey_End_Date) FROM #tmptrips T ; WITH CTE_Dates AS ( SELECT @StartDate AS CalendarDate UNION ALL SELECT DATEADD(DAY, 1, D.CalendarDate) FROM CTE_Dates D WHERE D.CalendarDate < @EndDate ) SELECT
T.ID, COUNT(DISTINCT D.CalendarDate) AS TripDays FROM #tmptrips T INNER JOIN CTE_Dates D ON D.CalendarDate BETWEEN T.Journey_Start_Date AND T.Journey_End_Date GROUP BY
T.ID
18 People are following this question.
