SQL Server - Need help with Calendar query.

Asked By mohaaro on 12-Jun-08 10:57 PM
Hello all,

I'm using the Calendar table described in this article (http://
sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-
auxiliary-calendar-table.html).

I'm trying to formulate a query which will return the day number given
an input parameter of how many days from now I want the returned day.
I want it to return only week days (business days) and the problem I'm
having with it is that it doesn't return anything if the day found is
a weekend day.

I want this to work on only week days so if I'm on a friday and I want
the week day three days from now it will return the next Wednesday,
which would be three work days from now.

Can anyone help me with this?

Here is my current query.

DECLARE
@Date datetime,
@Days smallint

SET @Date = GETDATE()
SET @Days = 5

SELECT
DT,
[Month],
[Day],
CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) Today
FROM
SharedInfo.dbo.Calendar
WHERE
[DT] = CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) + @Days
AND
IsWeekDay = 1




Aaron Bertrand [SQL Server MVP] replied on 10-Jun-08 06:41 PM
So if the day is a weekend day, what date do you want used to base the
return on?  The preceding Friday, the following Monday?


If I had to guess, this will return the previous Friday:

DECLARE @Date SMALLDATETIME,
@Days SMALLINT;

SELECT
@Date = DATEIFF(DAY, 0, CURRENT_TIMESTAMP),
@Days = 5;

SELECT TOP 1
DT,
[Month],
[Day],
[Today] = @Date
FROM
SharedInfo.dbo.Calendar
WHERE
[DT] <= DATEADD(DAY, @Days, DATEDIFF(DAY, 0, @Date))
AND IsWeekDay = 1
ORDER BY
[DT] DESC;

You might also want to add a where clause just in case friday is a
holiday...
mohaaro replied on 12-Jun-08 10:57 PM
I wasn't thinking this would be run on a weekend. I was going to
schedule this to only run on week days.

I would say that if this runs on a Saturday and we're looking out 5
days with it then it should return the next Thursday.

It should always only go in one direction, forward by passing it a
positive number of days, or backword by passing it a negitive number
of days. If the day is Saturday and I pass a positive 1, plus one from
today, I think it would be better to have it return Monday. In any
case I was planning on this only running on week days and so it should
only return week days.

I'll add to this that the reason for doing it this way is that
weekends are being chosen by the business as non-business days and
should never be included.

On Jun 10, 3:41=A0pm, "Aaron Bertrand [SQL Server MVP]"
Aaron Bertrand [SQL Server MVP] replied on 11-Jun-08 10:27 AM
OK, so did you *try* the query I posted?  It won't return a weekend
regardless of what day you run it on.  And "the date" I was talking about
was the date that resulted from the calculation in your query, not the day
you run the query.  The problem with your query is that you just returned a
single day where the date was n days out, and since you added IsWeekday = 1,
you go down to the empty set if n days out is a weekend.   If you want the
NEXT weekday then:

DECLARE @Date SMALLDATETIME,
@Days SMALLINT;

SELECT
@Date = DATEIFF(DAY, 0, CURRENT_TIMESTAMP),
@Days = 5;

SELECT TOP 1
DT,
[Month],
[Day],
[Today] = @Date
FROM
SharedInfo.dbo.Calendar
WHERE
[DT] >= DATEADD(DAY, @Days, DATEDIFF(DAY, 0, @Date))
AND IsWeekDay = 1
ORDER BY
[DT];


If neither of these queries do what you want, then you'll need to explain
better what you want.  And I don't mean more narrative, I mean give me some
values for @Date and @Days, and tell me what you want returned in each case.
For example, if what you really want is for @Days to represent WEEKDAYS
ONLY, e.g. 5 weekdays from now, then you'll need to tell us that.



I wasn't thinking this would be run on a weekend. I was going to
schedule this to only run on week days.

I would say that if this runs on a Saturday and we're looking out 5
days with it then it should return the next Thursday.

It should always only go in one direction, forward by passing it a
positive number of days, or backword by passing it a negitive number
of days. If the day is Saturday and I pass a positive 1, plus one from
today, I think it would be better to have it return Monday. In any
case I was planning on this only running on week days and so it should
only return week days.

I'll add to this that the reason for doing it this way is that
weekends are being chosen by the business as non-business days and
should never be included.

On Jun 10, 3:41 pm, "Aaron Bertrand [SQL Server MVP]"
--CELKO-- replied on 12-Jun-08 10:57 PM
A business day and a week day are not the same thing; you do get
holidays off of work, don't you?

Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information.  Do not try to calculate holidays in SQL -- Easter alone
requires too much math.

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,
...);

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.

Derek Dongray came up with a classification of the public holidays and
weekends he needed to work with in multiple countries.  He had nine
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

Time zones with fractional hour displacements:
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=54
http://www.timeanddate.com/worldclock/city.html?n=176
http://www.timeanddate.com/worldclock/city.html?n=246

But the strange ones are:
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=63

CREATE TABLE Calendar
(cal_date DATE NOT NUL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
...);

INSERT INTO Calendar VALUES ('2007-04-05',  42);
INSERT INTO Calendar VALUES ('2007-04-06',  43); -- good Friday
INSERT INTO Calendar VALUES ('2007-04-07',  43);
INSERT INTO Calendar VALUES ('2007-04-08',  43);-- Easter Sunday
INSERT INTO Calendar VALUES ('2007-04-09',  44);
INSERT INTO Calendar VALUES ('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 C2cal_date = '2007-04-10';
Alex Kuznetsov replied on 12-Jun-08 10:57 PM
Yes it is good, but not good enough in some cases. How many business
days are there between Friday June 6, 2008, and Saturday, June 7,
2008?
It depends if you count it between ends of days (the answer is 0) or
between beginnings of days (the answer is 1).
--CELKO-- replied on 12-Jun-08 10:57 PM
There are a lot of different temporal questions you can ask -- number
of days [between, until, after, etc.].  Snodgrass had a list and a
diagram in his book on this topic.
mohaaro replied on 12-Jun-08 10:58 PM
he day number given an input parameter of how many days from now I want the =
returned day. I want it to return only week days (business days) and the pro=
blem I'm having with it is that it doesn't return anything if the day found =
is a weekend day. <<

This is getting a bit more technical then I think it needs to be. To
me the correct answer for this is obviously 1. If I'm running a
business friday is a business day and should be counted as such. I
normal think of counting days as from the bigginning to end of day.

The question is appreciated though.
mohaaro replied on 12-Jun-08 10:58 PM
t

d a
=3D 1,
he
ome
e.



Aaron B.

Your right, I had not tried the query yet. I will plug it into query
analyzer and try it and get back to you.

Thanks
mohaaro replied on 12-Jun-08 10:58 PM
day number given an input parameter of how many days from now I want the re=
turned day. I want it to return only week days (business days) and the probl=
em I'm having with it is that it doesn't return anything if the day found is=
a weekend day. <<
orldclock/city.html?n=3D5http://www.timeanddate.com/worldclock/city.html?n=
=3D54http://www.timeanddate.com/worldclock/city.html?n=3D176http://www.timea=
nddate.com/worldclock/city.html?n=3D246
=3D5http://www.timeanddate.com/worldclock/city.html?n=3D63

Joe,

Thanks for the reply,

Yes I agree that a business day and a week day are not the same thing.
In the context of my problem I don't care if a week day is also a
holiday. I will have to think about this a bit more though. The
requirement now is that we are using this to send reminders out before
or after something happens and they (the business) doesn't want them
received on a weekend day. So I was mostly concerned about week days,
but since you ask the question it might come about that holidays will
be thought about in the same way a weekend day is.
--CELKO-- replied on 12-Jun-08 10:58 PM
It is worth being "a bit more technical" in cases where contracts are
involved, so you don't get in legal trouble.  Ask the accountants or
the lawyers exactly what the definitions are before you code instead
of after you get sued.
Aaron Bertrand [SQL Server MVP] replied on 11-Jun-08 12:54 PM
Don't fret about this too much.  By all means get the business rules right,
but this change only means that you add one of the following to your WHERE
clause:

AND IsHoliday = 0

AND IsBusinessDay = 1

This will simply depend on how you want to define the data in your calendar
table.  But this is exactly the kind of extension that makes a calendar
table very useful.
mohaaro replied on 12-Jun-08 10:58 PM
ome
e.

Aaron B.

OK, what you state here is what I want. The @Days should represent
weekdays. So today is the 11th and I want to count 3 days out from
now. Three weekdays out from now would be the 16th. This way if @Date
is tomorrow (6/12/2008) then three days out would be 6/17/2008. So
we're only counting weekdays.

One last example is if we counted 5 days out from today (6/11/2008)
then the returned day would be 6/18/2008.

Does this help in describing the problem better?
Aaron Bertrand [SQL Server MVP] replied on 11-Jun-08 01:14 PM
Not really.  Do either of the queries I provided answer the question
correctly?  Did you try them yet?  I do not have your data or your calendar
table, so I cannot really test it easily.
mohaaro replied on 12-Jun-08 10:58 PM
On Jun 11, 10:14=A0am, "Aaron Bertrand [SQL Server MVP]"
ndar

Aaron,

This query is closest.

DECLARE
@Date SMALLDATETIME,
@Days SMALLINT;


SELECT
@Date =3D DATEDIFF(DAY, 0, CURRENT_TIMESTAMP),
@Days =3D 3;


SELECT TOP 1
DT,
[Month],
[Day],
[Today] =3D @Date
FROM
SharedInfo.dbo.Calendar
WHERE
[DT] >=3D DATEADD(DAY, @Days, DATEDIFF(DAY, 0, @Date))
AND IsWeekDay =3D 1
ORDER BY
[DT];

If I pass it 3 days from now then it returns the 16th which is
correct, but if I pass it 4 days from now it still returns the 16th
when it should return the 17th. I have to pass 6 days to get the 17th
which is not how I want it to work.

I think it's not working because it's counting the weekend days.
mohaaro replied on 12-Jun-08 10:58 PM
Joe,

Why do you name the column julian_business_nbr? What does Julian mean?

day number given an input parameter of how many days from now I want the re=
turned day. I want it to return only week days (business days) and the probl=
em I'm having with it is that it doesn't return anything if the day found is=
a weekend day. <<
orldclock/city.html?n=3D5http://www.timeanddate.com/worldclock/city.html?n=
=3D54http://www.timeanddate.com/worldclock/city.html?n=3D176http://www.timea=
nddate.com/worldclock/city.html?n=3D246
=3D5http://www.timeanddate.com/worldclock/city.html?n=3D63
--CELKO-- replied on 12-Jun-08 10:58 PM
The Julian date is technically a count of days used by astronomers for
observations (http://en.wikipedia.org/wiki/Julian_date).  The
JulianIZED date is a  count of days from a base day, used in business.
You see it as the "day within year" number on the bottom of the pages
of a calendar.  They are also known as ordinal dates.
mohaaro replied on 12-Jun-08 10:58 PM
Oh, this is great! I just solved my problem using the Julian day
column. Here is my code if anyone else is interested.

DECLARE
@Date datetime,
@Days smallint,
@JulianDayToday smallint

SET @Date =3D GETDATE()
SET @Days =3D 7

--// Get Julian Day Today
SET @JulianDayToday =3D (SELECT JulianBusinessDay FROM
SharedInfo.dbo.Calendar WHERE [DT] =3D CAST(FLOOR(CAST(@Date AS FLOAT))
AS DATETIME))

SELECT
CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) Today,
[Day]
FROM
SharedInfo.dbo.Calendar
WHERE
JulianBusinessDay =3D @JulianDayToday + @Days
AND
IsWeekDay =3D 1
Chris Hohmann replied on 11-Jun-08 08:48 PM
DECLARE @Date SMALLDATETIME
DECLARE @Days SMALLINT
SELECT @Date = '2008-06-11', @Days = 4


SELECT  DATEADD(d, @Days/5*7 + @Days%5 + CASE WHEN DATEPART(dw,@Date) +
@Days%5 > 6 THEN 2 ELSE 0 END, @Date)
Alex Kuznetsov replied on 12-Jun-08 10:58 PM
Well I just happened to work for several projects when the correct
answer depended on some circumstances. that is why I know about it.
mohaaro replied on 12-Jun-08 10:59 PM
Chris,

You certainly know how to query using dates better then I do. Can you
please expain to me how your query works?
Chris Hohmann replied on 12-Jun-08 05:21 PM
Basically, I'm deconstructing the number of workdays into it's constituent
pieces.

@Days/5 = Number of weeks
@Days%5 = Number of dangling days

My solution works only for your particular situation. The
julian_business_nbr solution is much more elegant and will work later when
you have to take holidays into account.