Weekday vs Weekends - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Weekday vs Weekends

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-06 : 17:41:49

Folks,

I am looking for a simple and efficient way to set a variable to select either weekdays, weekends or both from a table.

I would much rather change the values of weekdays vs. weekends based on a variable than having to go throughout 4 pages of code to do it. The following is a scaled down example of what I'm doing now. Weekdays vs Weekends will be filtered or included depending on the changing values of the @wkend or @wkday variables:

declare @table table (CalendarDate date, WkDayType int)
insert into @table (CalendarDate)
values ('2011-01-01'), ('2011-01-02'), ('2011-01-03'),('2011-01-04'), ('2011-01-05'), ('2011-01-06'), ('2011-01-07'),
('2011-01-08'), ('2011-01-09'), ('2011-01-10'),('2011-01-11'), ('2011-01-12'), ('2011-01-13'), ('2011-01-14')

--//Use the dw values to update each row with a "weekday" vs "weekend" value
update @table
set WkDayType=case when DATEPART(DW, CalendarDate) between 2 and 6 then 1 else 2 end

--//Set up the variables that will be used to filter or include weekends vs weekdays
declare @wkday int
declare @wkend int
set @wkday=1 --//Set to 1 if you want to include weekdays, set to 0 if you want to exclude
set @wkend=2 --//Set to 2 if you want to include weekends, set to 0 if you want to exclude

select *
from @table
where WkDayType in (@wkday, @wkend)

I'm sure there's a more efficient, simple and elegant way to do this. Any help will be appreciated!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-06 : 18:20:58

What you have there seems fine to me. I would suggest one change though. Do the update statement like this:


UPDATE @table SET 
WkDayType = 
   CASE 
      WHEN DATEDIFF(dd,'19000101',CalendarDate)%7 < 5 THEN 1 
      ELSE 2 
   END;

The reason is that if the someone changes the DATEFIRST setting, then this would still work correctly to mark Saturday and Sunday as weekends; if you use DW, it would not.

As for selecting, you could do something like this using bit-wise AND, but it is probably not any more efficient, and may be less readable:

DECLARE @selection INT; --1=weekdays, 2=weekends, 3=both
SET @selection = 1;
SELECT * FROM @table WHERE WkDayType & @selection = WkDayType

Edit: Corrected to fix the reversal in logic - see flamblaster's comment below.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-06 : 18:59:25

Sunitabeck,

Thanks...I hadn't considered @@Datfirst changing. Sidenote, I think the selection is backwards. The return values of the wkdaytype you posted came out to be 1 for weekends and 2 for weekdays...unless I"m doing something wrong.

I'll try the bitwise end you mentioned. I've been trying to get a better understanding on bitwise, so this is a good excuse to get into it!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-07 : 01:12:19

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 SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
week_in_year SMALLINT NOT NULL, -- SQL Server is not ISO standard
holiday_type SMALLINT NOT NULL
CHECK(holiday_type IN ( ..), --
day_in_year SMALLINT 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.

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';

Derek Dongray came up with a classification of the public holidays and weekends he needed to work with in multiple countries. Heris his list with more added.
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.
10. Fixed date except Saturday slides to Friday, and Sunday slides to Monday.
11. Fixed date, but Tuesday slides to Monday, and Thursday to Friday. (Argentina celebrates 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.

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

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 02:38:23

Thanks Joe, but my requirements are not for business days versus holidays etc. Only to differentiate between "activated" work days in a table and they can either be weekdays or weekends.

The date info will be useful for some other things though. THanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-07 : 07:35:26

quote:
Originally posted by flamblaster

Sunitabeck,

Thanks...I hadn't considered @@Datfirst changing. Sidenote, I think the selection is backwards. The return values of the wkdaytype you posted came out to be 1 for weekends and 2 for weekdays...unless I"m doing something wrong.

I'll try the bitwise end you mentioned. I've been trying to get a better understanding on bitwise, so this is a good excuse to get into it!

You are very much correct. I had the logic reversed in what I posted yesterday. Edited and changed it.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 11:03:37

Ah, very cool Sunitabeck...thought I was going crazy :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources