Transact SQL – Find Days and Duration across Date Range

Background

The story line is that I have a set of dates, a begin and a corresponding end date.  SQL Server has in-built support for determining how far apart my two dates are; I simply use the datediff function.

Graph

But, as I delve into graphs and the like, I need to actually know the exact days that the date range cover.

Test Case

A ready test case is an International Flight. I like Delta and Love San Francisco and Amman, Jordan.

Let us use the Delta web site and seek out a flight between the two aforementioned cities.

For today, May 11th, 2015, here is what is available:

flightInfo_DeltaAirline

SQL Server Objects

To determine the days and hours spent in flight, we need to define a couple of Calendar Objects.

Blogs are no place to paste code and so I copied them over to Github.

The name of the repository is https://github.com/DanielAdeniji/TransactSQLCalendar

Here is our File details:

Purpose File Name Detail
DDL – Create Objects
 dateUtil.Number.ddl.sql  Create the dbo.Number object.  This is basically an helper table
 dateUtil.Calendar.dll.sql Create the dateutil.Calendar table.  This table contains individual day records
 DML – Populate Tables
 dateUtil.Number.dml.sql Populate the dateUtil.Number table
dateUtil.Calendar.dml.sql Populate the dateUtil.Calendar table
 DDL – Inline Table Function
 dateutil.itvf_DaysInRange.sql Function that computes days and time used for that day
Unit Test Code
 test2.sql Test case based on Delta flight from SF to Amman, Jordan

DDL

Let us create our helper function; it will be an Inline table function (ITVF).

  • [dateutil].[itvf_DaysInRange]

Functions – Inline Table Value Function

dateUtil.itvf_DaysInRange


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go

use [DBLAB]
go

if schema_id('dateutil') is null
begin

    exec('create schema [dateutil] authorization [dbo]')

end
go

IF NOT EXISTS
(
    SELECT name, type
    FROM   sys.objects
    WHERE  object_id = object_id('[dateutil].[itvf_DaysInRange]')
    AND    type = N'IF'
)
begin
     exec('create function [dateutil].[itvf_DaysInRange]() returns table as return ( select 1/0 as [shell] ) ')
end
GO

ALTER FUNCTION [dateutil].[itvf_DaysInRange]
(
      @dateBegin    datetime
    , @dateEnd      datetime
)
RETURNS TABLE
AS RETURN
(

  select
      [dateBegin] = @dateBegin
    , [dateEnd] = @dateEnd
    , calendarDate = tblCal.CalendarDate
    , case
         --same day event
         when (  (cast(@dateBegin as date)) = (cast (@dateEnd as date)) ) then
                datediff(minute, @dateBegin, @dateEnd)

         --when day is start date
         when  ( cast(@dateBegin as date) = tblCal.CalendarDate) then
              datediff(minute, @dateBegin, dateadd(day,1, cast(@dateBegin as date)))

         --when last day
         when  ( cast(@dateEnd as date) = tblCal.CalendarDate) then
              datediff(minute, cast(@dateEnd as date), @dateEnd )

         --all day event
         else (24 *60)

       end as [duration]

  from   [dateutil].[Calendar] tblCal

  where

       (

            ( cast(@dateBegin as date) <= tblCal.CalendarDate)
        and ( @dateEnd >= tblCal.CalendarDate)

     )              

)
go

grant select on [dateutil].[itvf_DaysInRange] to [public]
go

Test Case

Let us return to our Delta schedule; provide the Flight data and see if we are able to determine the contingent days and time spent on each day.


    set statistics io on
    set nocount on
    go

    use [DBLAB]
    go

    declare @tblFlight Table
    (
          [id] int not null identity(1,1)
        , [flightNumber] varchar(60) not null
        , [flightFrom] varchar(60) not null
        , [flightTo] varchar(60) not null
        , [dateBegin] datetime not null
        , [dateEnd]   datetime not null
    )

    /*
        Date in ISO 8601 Format
        https://technet.microsoft.com/en-us/library/ms190977%28v=sql.90%29.aspx
        yyyy-mm-ddThh:mm:ss[.mmm]
    */
    insert into @tblFlight
    ( [flightNumber], [flightFrom], [flightTo], [dateBegin], [dateEnd])
    values
        (
              'Delta 1085'
            , 'San Francisco, CA (SFO)'
            ,'Atlanta, GA (ATL)'
            ,'2015-05-11T23:26:00'  --11:26 PM
            ,'2015-05-12T07:00:00'  --07:00 AM
        )
        ,
        (
             'Delta 0082'
            ,'Atlanta, GA (ATL)'
            ,'PARIS - CHARLES DE GAULLE AIRPORT (CDG)'
            ,'2015-05-12T15:14:00'  --03:14 PM
            ,'2015-05-13T06:10:00'  --06:10 AM
        )

        ,
        (
             'Delta 8438'
            ,'PARIS - CHARLES DE GAULLE AIRPORT (CDG)'
            ,'Armon, Jordan (AMM)'
            ,'2015-05-13T14:45:00'  --02:45 PM
            ,'2015-05-13T20:20:00'  --08:20 PM
        )

    ;

    select
              tblF.*
            , tblC.calendarDate
            , durationInMinutes = tblC.duration

    from   @tblFlight tblF

           cross apply [dateutil].[itvf_DaysInRange]
              ( tblF.dateBegin, tblF.dateEnd) tblC

    order by tblC.calendarDate

    set statistics io off
    go

 Output:

result

Listening

So much lacking in my world lately.

To darken my mood, read current Affairs and found mention of Prince’s Baltimore.

As I waded online for background information and what the lyrics and critics say, I found Rolling Stones’ take.

Hear Prince’s Freddie Gray Protest Song ‘Baltimore’
http://www.rollingstone.com/music/news/hear-princes-freddie-gray-protest-song-baltimore-20150509

Thankfully, Rolling Stone (RS) has a link to “10 New Country Artists You Need to Know: Spring 2015”.

http://www.rollingstone.com/music/lists/10-new-country-artists-you-need-to-know-spring-2015-20150505

One of the artists mentioned is Andrew Combs.  NPR likes Mr. Combs’, as well:

First Listen: Andrew Combs, ‘All These Dreams’
http://www.npr.org/2015/02/22/387200347/first-listen-andrew-combs-all-these-dreams

Andrew Combs, ‘Nothing To Lose’
http://www.npr.org/event/music/379426870/andrew-combs-nothing-to-lose

If you did not sample Andrew’s music from RS nor NPR, here are a couple of YouTube links:

Conclusion

With all the time we all waste on the Net, occasionally we find something actually New.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s