x

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'
more ▼

asked Jul 15 at 12:21 PM in Default

avatar image

syedasrarali
50 1 3 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jul 18 at 07:21 PM

avatar image

Arcanas
281 1 5 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1105
x49

asked: 6 days ago

Seen: 19 times

Last Updated: 2 days ago

Copyright 2017 Redgate Software. Privacy Policy