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?
 
Jim HornMicrosoft 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
LIONKINGCommented:
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
 
PortletPaulfreelancerCommented:
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.