Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2015-02-19
Medium Priority
107 Views
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)
``````

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'.

Jim
0
Question by:Jim Horn
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 21

Expert Comment

ID: 40620496
;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

LVL 66

Author Comment

ID: 40621575
Alpesh - My question asks for a scalar function, and code is to return a set with I'm not sure what logic infolved.
0

LVL 66

Author Comment

ID: 40621616
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
``````
0

LVL 13

Assisted Solution

LIONKING earned 1000 total points
ID: 40621941
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
``````
0

LVL 66

Accepted Solution

Jim Horn earned 0 total points
ID: 40622104
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
``````
0

LVL 75

Expert Comment

ID: 40623885
You should be able to resolve this with ROW_NUMBER() and a CTE.
0

LVL 13

Expert Comment

ID: 40624125
@Anthony Perkins: That's basically what I proposed as a solution, but instead of ROW_NUMBER, I used an identity column.
0

LVL 49

Assisted Solution

PortletPaul earned 1000 total points
ID: 40624986
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 |
``````

``````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 |
``````

For negative < (less than) is fine (because the inherit assumption is the parameter at the beginning of the day)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month5 days, 18 hours left to enroll