Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-02-19
8
Medium Priority
?
125 Views
Last Modified: 2015-03-01
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
0
Comment
Question by:Jim Horn
8 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
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

by:Jim Horn
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

by:Jim Horn
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

Open in new window

4th-day-after-march-14.jpg
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Assisted Solution

by:LIONKING
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

Open in new window

0
 
LVL 66

Accepted Solution

by:
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

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40623885
You should be able to resolve this with ROW_NUMBER() and a CTE.
0
 
LVL 13

Expert Comment

by:LIONKING
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

by:PortletPaul
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 |

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question