Hi,
Well today’s post is a simple one, but this one which gave me a good fight to get the trick right !!!
Situation:
I have 2 date ranges, and I need to know the number of days over-lapping between these 2 date ranges
The above highlighted red area is what I am interested in !!
Resolution:
I just create a Scalar user defined function (MSSQL) as below and call it in my query with the necessary parameters to obtain the results.
CREATE FUNCTION [dbo].[fn_ComputeRangeDaysFromBoundaries](@OuterStart INT,@OuterEnd INT,@CheckStart INT,@CheckEnd INT) RETURNS INT AS BEGIN --DECLARE @OuterStart INT = 20180808; --DECLARE @OuterEnd INT = 20180826; --DECLARE @CheckStart INT = 20180805; --DECLARE @CheckEnd INT = 20180820; DECLARE @ReturnCoverDays INT = 0; IF (@OuterStart <= @OuterEnd) AND (@CheckStart <= @CheckEnd) --Check for valid date ranges BEGIN DECLARE @DateAPart INT , @DateBPart INT; IF(@OuterStart = @CheckStart) SET @DateAPart = @CheckStart ELSE IF (@OuterStart < @CheckStart) SET @DateAPart = @CheckStart ELSE IF (@CheckStart < @OuterStart) SET @DateAPart = @OuterStart IF(@OuterEnd = @CheckEnd) SET @DateBPart = @CheckEnd ELSE IF(@OuterEnd < @CheckEnd) SET @DateBPart = @OuterEnd ELSE IF(@CheckEnd < @OuterEnd) SET @DateBPart = @CheckEnd --SELECT @DateAPart , @DateBPart IF (@DateAPart <= @DateBPart) SET @ReturnCoverDays = DATEDIFF(DAY , dbo.fn_ConvertIntToDate(@DateAPart), dbo.fn_ConvertIntToDate(@DateBPart)) + 1 END RETURN @ReturnCoverDays; END
And that’s it…
The solution is above which made my query so much more optimised and fast (rather than leveraging Date/Time tables to join upon and get the day count between Start/End dates).
But it was a hard one to derive (due to boundary conditions).
Hope it helps.
A good one! Thanks! 🙂
Hi,
Thx for this function!
If I’m right this function includes another “custom” function!? “dbo.fn_ConvertIntToDate”
Do you also have the query to create this 2nd function?
Thx and Best,
Max