Solved

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

Posted on 2015-02-19
8
89 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 65

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 65

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 250 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 65

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 48

Assisted Solution

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Live Storage Vmotion VMs with shared VMDK 10 57
TSQL query to generate xml 4 33
T-SQL: New to using transactions 9 25
SQL Count issue 24 15
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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