T-SQL function to go x number of days where bit column = 1

Hi All

I'm working on another calendar table article and for some reason I'm having a brain freeze...
I have a calendar table of days, where each date = 1 row, with an is_workday column where 1 is a workday and 0 is not, because of either a weekend or holiday.   The below code example is for two months:

CREATE TABLE days (dt date, is_workday bit) 

INSERT INTO days (dt, is_workday)
VALUES 
   -- Each row is one week, Sunday through Saturday
   ('2014-02-01', 0), ('2014-02-02', 1), ('2014-02-03', 1), ('2014-02-04', 1), ('2014-02-05', 1), ('2014-02-06', 1), ('2014-02-07', 0), 
   ('2014-02-08', 0), ('2014-02-09', 1), ('2014-02-10', 1), ('2014-02-11', 1), ('2014-02-12', 1), ('2014-02-13', 1), ('2014-02-14', 0), 
   ('2014-02-15', 0), ('2014-02-16', 0), ('2014-02-17', 1), ('2014-02-18', 1), ('2014-02-19', 1), ('2014-02-20', 1), ('2014-02-21', 0), 
   ('2014-02-22', 0), ('2014-02-23', 1), ('2014-02-24', 1), ('2014-02-25', 1), ('2014-02-26', 1), ('2014-02-27', 1), ('2014-02-28', 0), 
   ('2014-03-01', 0), ('2014-03-02', 1), ('2014-03-03', 1), ('2014-03-04', 1), ('2014-03-05', 1), ('2014-03-06', 1), ('2014-03-07', 0), 
   ('2014-03-08', 0), ('2014-03-09', 1), ('2014-03-10', 1), ('2014-03-11', 1), ('2014-03-12', 1), ('2014-03-13', 1), ('2014-03-14', 0), 
   ('2014-03-15', 0), ('2014-03-16', 1), ('2014-03-17', 0), ('2014-03-18', 1), ('2014-03-19', 1), ('2014-03-20', 1), ('2014-03-21', 0), 
   ('2014-03-22', 0), ('2014-03-23', 1), ('2014-03-24', 1), ('2014-03-25', 1), ('2014-03-26', 1), ('2014-03-27', 1), ('2014-03-28', 0),
   ('2014-03-29', 0), ('2014-03-30', 1), ('2014-03-31', 1)

Open in new window


I'd like to write a scalar function that takes two parameters:  @dt date, and @increment smallint, which resembles the DATEADD funciton for days, but incorporates the is_workday column to do the math only for days where is_workday = 1.

For example...
SELECT dbo.cal_workday_datediff('2014-02-15', 7) would return '2014-02-25', as it is the seventh day after '2014-02-15' where is_workday = 1.  (February 17th, 18, 19, 20, 23, 24, 25)

dbo.cal_workday_datediff('2014-03-07', 4) would return '2014-03-12'.
dbo.cal_workday_datediff('2014-03-07', -4) would go backwards four days and return '2014-03-03'.

Thanks in advance.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Figured it out.  Didn't know you can do a SELECT TOP (@variable).
Not real graceful, but it does the job.
I'll leave this question open for awhile in case someone has a more elegant solution (2012 windowing functions?), and then close.

Declare @dt date = '2014-03-14', @increment int = -4

IF @increment > 0
	begin
	SELECT TOP 1 dt
	FROM (
		SELECT TOP (@increment) dt
		FROM days
		WHERE dt > @dt AND is_workday = 1
		ORDER BY dt) a
	ORDER BY dt DESC
	end

IF @increment < 0
	begin
	SELECT TOP 1 dt
	FROM (
		SELECT TOP (ABS(@increment)) dt
		FROM days
		WHERE dt < @dt AND is_workday = 1
		ORDER BY dt DESC) a
	ORDER BY dt 
	end

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
;with a (dt , is_workday )
      as
      (
      select CASt(getdate() as date) dt, 1 is_workday
      union all
      select dateadd(DAY, 1, dt),   CASE WHEN datepart(dW, dt) in (1,7) THEN 0 ELSE 1 END  is_workday  from a
      where dt < '2015-04-30'
      )
      select * from a
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Alpesh - My question asks for a scalar function, and code is to return a set with I'm not sure what logic infolved.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
In other words, how can I alter the below T-SQL to return only a scalar value, starting at @dt, and moving @increment days forwards/backwards?
Declare @dt date = '2014-03-14' , @increment int = 4

select dt
FROM days
WHERE is_workday = 1 AND dt >= @dt

Open in new window

4th-day-after-march-14.jpg
0
 
LIONKINGConnect With a Mentor Commented:
Just an idea... I'm sure there's stuff to adjust, but it might get you started.

CREATE TABLE udays (dt date, is_workday bit) 

INSERT INTO udays (dt, is_workday)
VALUES 
   -- Each row is one week, Sunday through Saturday
   ('2014-02-01', 0), ('2014-02-02', 1), ('2014-02-03', 1), ('2014-02-04', 1), ('2014-02-05', 1), ('2014-02-06', 1), ('2014-02-07', 0), 
   ('2014-02-08', 0), ('2014-02-09', 1), ('2014-02-10', 1), ('2014-02-11', 1), ('2014-02-12', 1), ('2014-02-13', 1), ('2014-02-14', 0), 
   ('2014-02-15', 0), ('2014-02-16', 0), ('2014-02-17', 1), ('2014-02-18', 1), ('2014-02-19', 1), ('2014-02-20', 1), ('2014-02-21', 0), 
   ('2014-02-22', 0), ('2014-02-23', 1), ('2014-02-24', 1), ('2014-02-25', 1), ('2014-02-26', 1), ('2014-02-27', 1), ('2014-02-28', 0), 
   ('2014-03-01', 0), ('2014-03-02', 1), ('2014-03-03', 1), ('2014-03-04', 1), ('2014-03-05', 1), ('2014-03-06', 1), ('2014-03-07', 0), 
   ('2014-03-08', 0), ('2014-03-09', 1), ('2014-03-10', 1), ('2014-03-11', 1), ('2014-03-12', 1), ('2014-03-13', 1), ('2014-03-14', 0), 
   ('2014-03-15', 0), ('2014-03-16', 1), ('2014-03-17', 0), ('2014-03-18', 1), ('2014-03-19', 1), ('2014-03-20', 1), ('2014-03-21', 0), 
   ('2014-03-22', 0), ('2014-03-23', 1), ('2014-03-24', 1), ('2014-03-25', 1), ('2014-03-26', 1), ('2014-03-27', 1), ('2014-03-28', 0),
   ('2014-03-29', 0), ('2014-03-30', 1), ('2014-03-31', 1)

DECLARE @dateCalc TABLE
(
	ID INT IDENTITY(1,1),
	DateValue DATE
);

INSERT INTO @dateCalc(DateValue)
SELECT dt
FROM dbo.udays
WHERE is_workday = 1
ORDER BY dt ASC

DECLARE @startDate DATE
	, @increment INT;

-- TESTING
SET @startDate = '20140215';
SET @increment = 7;

WITH NewDate AS
(
	SELECT New_Id = ID + @increment + CASE WHEN @increment > 0 THEN -1 ELSE 0 END 
	FROM @dateCalc
	WHERE DateValue = CASE WHEN EXISTS(SELECT 1 FROM @dateCalc T1 WHERE T1.DateValue = @startDate)
			THEN @startDate
		ELSE 
			(SELECT TOP 1 T2.DateValue FROM @dateCalc T2 WHERE T2.DateValue > @startDate ORDER BY T2.DateValue ASC)
		END
)
SELECT varDate.DateValue
FROM @dateCalc varDate
	INNER JOIN NewDate ON
		varDate.ID = NewDate.New_Id

Open in new window

0
 
Anthony PerkinsCommented:
You should be able to resolve this with ROW_NUMBER() and a CTE.
0
 
LIONKINGCommented:
@Anthony Perkins: That's basically what I proposed as a solution, but instead of ROW_NUMBER, I used an identity column.
0
 
PaulConnect With a Mentor Commented:
For positive days (looking forward) you need >= (greater than or equal)
IF the date parameter is a working day, than that needs to be included in the count of days

consider these:
Declare @dt date = '2014-03-14', @increment int = 4

		SELECT TOP (@increment) dt
		FROM days
		WHERE dt > @dt AND is_workday = 1
		ORDER BY dt
;
|         DT |
|------------|
| 2014-03-16 |
| 2014-03-18 |
| 2014-03-19 |
| 2014-03-20 |

Open in new window


Declare @dt date = '2014-03-16', @increment int = 4

		SELECT TOP (@increment) dt
		FROM days
		WHERE dt > @dt AND is_workday = 1
		ORDER BY dt
;
|         DT |
|------------|
| 2014-03-18 |
| 2014-03-19 |
| 2014-03-20 |
| 2014-03-23 |

Open in new window


For negative < (less than) is fine (because the inherit assumption is the parameter at the beginning of the day)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.