Prev: converting number to string in hex
Next: batch file
From: LN on 27 Jul 2010 13:31
I need to subtract 3 business days. Exclude Sunday but include Sat.
ex: if today Monday 07/26/10 then count 22, 23, 24,
07/27/10 then count 26, 24, 23

Thank you in advance.

DECLARE @EnterDt DATETIME
SET @EnterDt = GETDATE()

SELECT *
FROM t
WHERE EnterDt > @EnterDt ??
From: Bob McClellan on 27 Jul 2010 14:13
You can just query the days spanning the range you want
to target and use ...
where EnterDt > @EnterDt
and datepart(dw,EnterDt ) <> 1
...this will exclude Sundays.

"LN" <LN(a)discussions.microsoft.com> wrote in message
news:C2AC48B4-C48B-44FE-B9C2-9600D18341A0(a)microsoft.com...
>I need to subtract 3 business days. Exclude Sunday but include Sat.
> ex: if today Monday 07/26/10 then count 22, 23, 24,
> 07/27/10 then count 26, 24, 23
>
> Thank you in advance.
>
> DECLARE @EnterDt DATETIME
> SET @EnterDt = GETDATE()
>
> SELECT *
> FROM t
> WHERE EnterDt > @EnterDt ??

From: --CELKO-- on 27 Jul 2010 18:45
Busienss days are irregular, so we use a Calendar table:

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
week_in_year INTEGER NOT NULL, -- SQL server is not ISO standard
holiday_type INTEGER NOT NULL
CHECK(holiday_type IN ( ..), --
day_in_year INTEGER NOT NULL,
julian_business_day INTEGER NOT NULL,
...);
Derek Dongray came up with a classification of the public holidays and
weekends he needed to work with in multiple countries. He had eight
types of holiday:
1. Fixed date every year.
2. Days relative to Easter.
3. Fixed date but will slide to next Monday if on a weekend
4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday
(UK Boxing Day is the only one).
5. Specific day of week after a given date (usually first/last
Monday
in a month but can be other days, e.g. First Thursday after November
22 = Thanksgiving)
6. Days relative to Greek Orthodox Easter (not always the same as
Western Easter)
7. Fixed date in Hijri (Muslim) Calendar - this turns out to only be
approximate due to the way the calendar works. An Imam has to see a
full moon to begin the cycle and declare it.
8. Days relative to previous Winter Solstice (Chinese holiday of
Qing
Ming Jie)
9. Civil holidays set by decree, such as a National Day Of Mourning.
As you can see, some of these are getting a bit esoteric and a bit
fuzzy. A calendar table for US Secular holidays can be built from
the
data at this website, so you will get the three-day weekends:
http://www.smart.net/~mmontes/ushols.html

The Julian business day is a good trick. Number the days from
whenever your calendar starts and repeat a number for a weekend or
company holiday.

CREATE TABLE Calendar
(cal_date DATE NOT NUL 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';
From: Wes Groleau on 27 Jul 2010 20:04
On 07-27-2010 18:45, --CELKO-- wrote:
> Derek Dongray came up with a classification of the public holidays and
> weekends he needed to work with in multiple countries. He had eight
> types of holiday:

Your list had nine. :-) I worked for a company that would add

10. Fixed date except Saturday slides to Friday, and
Sunday slides to Monday.

--
Wes Groleau

New Worksheets: Blue Butterfly and War of the Worlds
http://Ideas.Lang-Learn.us/russell?itemid=1598
From: Erland Sommarskog on 28 Jul 2010 04:53
Wes Groleau (Groleau+news(a)FreeShell.org) writes:
> Your list had nine. :-) I worked for a company that would add
>
> 10. Fixed date except Saturday slides to Friday, and
> Sunday slides to Monday.
>

I can add one more:

Fixed date, but Tuesday slides to Monday, and Thursday to Friday.

I was in Jujuy in Argentina on October 11, and I really wanted to get a
flight ticket to Buenos Aires (and it was a little urgent, since my return
flight back home to Europe was on Wednesday), but October 12, the day
Columbus discovered America is a national holiday in Argentina. Except when
it's a Tuesday, they back it one day to Monday. (And, no, I never got the
flight ticket. But I recovered from my stomach problems well enough to make
that 24-hour bus ride anyway.)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: converting number to string in hex
Next: batch file