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
8
Medium Priority
?
107 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
[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
  • Learn & ask questions
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

688 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