Groups > comp.databases.ms-sqlserver > #1708

Re: Advanced DateDiff Calculation In SQL (exclude weekend time)

X-Received	by 10.182.194.71 with SMTP id hu7mr3463173obc.37.1394417336664; Sun, 09 Mar 2014 19:08:56 -0700 (PDT)
X-Received	by 10.182.118.170 with SMTP id kn10mr1369obb.20.1394417336500; Sun, 09 Mar 2014 19:08:56 -0700 (PDT)
Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!l13no4599807iga.0!news-out.google.com!gi6ni106igc.0!nntp.google.com!ur14no4865456igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Sun, 9 Mar 2014 19:08:56 -0700 (PDT)
In-Reply-To	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=107.207.76.36; posting-account=eTE9_AoAAAD1dS9O9Ccywd_vfKFzS40A
NNTP-Posting-Host	107.207.76.36
References	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<0406603e-4048-4b67-ba0a-968b5f2db614@googlegroups.com> (permalink)
Subject	Re: Advanced DateDiff Calculation In SQL (exclude weekend time)
From	--CELKO-- <jcelko212@earthlink.net>
Injection-Date	Mon, 10 Mar 2014 02:08:56 +0000
Content-Type	text/plain; charset=ISO-8859-1
X-Received-Bytes	1811
X-Received-Body-CRC	2972898262
Xref	csiph.com comp.databases.ms-sqlserver:1708

Show key headers only | View raw

The Julian business day is a good trick. Number the days from whenever your 
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY, 
 julian_business_nbr INTEGER NOT NULL, 
 ...);

INSERT INTO Calendar 
VALUES ('2007-04-05', 42), 
 ('2007-04-06', 43), -- good Friday 
 ('2007-04-07', 43), 
 ('2007-04-08', 43), -- Easter Sunday 
 ('2007-04-09', 44), 
 ('2007-04-10', 45); --Tuesday


To compute the business days from Thursday of this week to next
 Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
  FROM Calendar AS C1, Calendar AS C2
 WHERE C1.cal_date = '2007-04-05',
   AND C2.cal_date = '2007-04-10';

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Advanced DateDiff Calculation In SQL (exclude weekend time) andy.mcvicker@siemens.com - 2014-03-03 12:35 -0800
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) Ross Presser <rpresser@gmail.com> - 2014-03-03 13:38 -0800
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) --CELKO-- <jcelko212@earthlink.net> - 2014-03-09 19:08 -0700
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) --CELKO-- <jcelko212@earthlink.net> - 2014-03-09 19:08 -0700
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) rja.carnegie@gmail.com - 2014-03-09 21:32 -0700

csiph-web