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.
But, as I delve into graphs and the like, I need to actually know the exact days that the date range cover.
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:
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:
Let us create our helper function; it will be an Inline table function (ITVF).
Functions – Inline Table Value Function
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
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
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’
Thankfully, Rolling Stone (RS) has a link to “10 New Country Artists You Need to Know: Spring 2015”.
One of the artists mentioned is Andrew Combs. NPR likes Mr. Combs’, as well:
First Listen: Andrew Combs, ‘All These Dreams’
Andrew Combs, ‘Nothing To Lose’
If you did not sample Andrew’s music from RS nor NPR, here are a couple of YouTube links:
With all the time we all waste on the Net, occasionally we find something actually New.