Calculate days in between certain events based on multiple conditions

Question

I am curious if one of you guys can help me to calculate how much days (DAYS BETWEEN TravelStart AND TravelEnd) a certain boat BoatID has been used ONLY for luxury tours BoutTourID = Luxury by different captains CaptainID, and now for the weird part: UNTIL the next Standard tour BoutTourID = Standard starts. I don't want to take the Cancelled trips into account Status = Cancelled.

CaptainID       BoatID      BoatTourID  Status      TravelStart TravelEnd
Jack            AlphaBoat   Standard                1-7-2019    20-7-2019
Kevin           AlphaBoat   Luxury                  21-7-2019   31-7-2019
Eric            AlphaBoat   Luxury      Cancelled   1-8-2019    10-8-2019
Nick            AlphaBoat   Standard                11-8-2019   20-8-2019
John            AlphaBoat   Luxury                  21-8-2019   30-8-2019
Lionel          BigBoat     Standard                1-8-2019    20-8-2019
Jeffrey         BigBoat     Luxury                  20-8-2019   25-8-2019
Chris           BigBoat     Standard                26-8-2019   28-8-2019

This in SQL should give the following results, so in the basis the table shows the exact same amount of records:

CaptainID   
Jack        0   --since BoatTourID = Standard, it should not be calculated
Kevin       10
Eric        0   --since Status = Cancelled
Nick        0
John        9
Lionel      19
Jeffrey     5
Chris       2

It should be possible to run it in 1 SQL query.

The code I wrote so far is very messy and doesn't come close to solving it, so I rather not post it, since I hope for a fresh idea. In case I will still post it, if necessary!

Possible duplicate of [SQL SERVER: Get total days between two dates](https://stackoverflow.com/questions/6068017/sql-server-get-total-days-between-two-dates) — Renat, Aug 20 '19 at 17:27

score 2 · Accepted Answer · answered Aug 20 '19 at 17:54

2

The following query should do what you want:

SELECT 
    CaptainID
    ,CASE WHEN BoatTourID = 'Standard' OR [Status] = 'Cancelled' THEN 0
        ELSE DATEDIFF(DAY,TravelStart,TravelEnd) AS [Date Difference]
FROM YourTable

answered Aug 20 '19 at 17:54

MJoy

1,349
2
9
23

This I think will yeild a better and more accurate result than the other answer. Especially if captains are used more than once. In that case, they would get more than one line, which can then be further resolved by wrapping the case above in a SUM and grouping by CaptainId – TechGnome Aug 20 '19 at 18:54

Shekar Kola · Answer 2 · 2019-08-20T18:44:32.067

0

You can achieve it by CASE expression along with group by. You can add additional columns in select and group by clause upon your requirement

select CaptinID,
       DATEDIFF( DAY,
       MIN (CASE WHEN 
            BoutTourID = 'Luxury' and NOT [Status] = 'Cancelled'
            THEN TravelStart Else cast( GETDATE() as date ) ), 
       MAX (CASE WHEN 
            BoutTourID = 'Luxury' and NOT [Status] = 'Cancelled'
            THEN TravelEnd Else cast ( GETDATE() as date ) )
              ) Duration_Days
from yourtbale
Group by CaptinID

MIN, MAX and Group by would help you get one line per CaptinID duration from whole trips. In case whole trips per captin not a requirement, you can change remove group by just use CASE expression accordingly..

edited Aug 20 '19 at 18:44

answered Aug 20 '19 at 17:46

Shekar Kola

1,287
9
15

This works... but only because in the sample data there was only one line per captain, which I don't think is a safe assumption. If a given captain is used for two trips, this query will get the wrong min/max dates and return an incorrect result. However, given the data, it does return the correct result... but experience has taught me that sample data rarely mirrors real data. – TechGnome Aug 20 '19 at 18:51
@TechGnome, agree, for that reason there is explanation added in the answer. – Shekar Kola Aug 20 '19 at 19:10

Calculate days in between certain events based on multiple conditions

2 Answers2