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.
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..
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:
answered Jul 18, 2017 at 07:21 PM