All holidays in a year

Question

All holidays in a year

aselcukc

SSC Journeyman

Points: 75
More actions
February 21, 2008 at 4:43 am

#184224

Hello
This code returns me to the all workdays and holidays in a year:
create table Agenda
(
dt smalldatetime primary key clustered,
WeekDay as convert(bit, case when datepart(dw, dt) in (1) then 0 else 1 end),
WorkDay bit default 1
)
go
declare @dt smalldatetime;
set @dt = '20080101';
while @dt <= '20081231'
begin
insert Agenda(dt) select @dt;
set @dt = @dt + 1
end
update Agenda
set WorkDay = 0 where WeekDay = 0;
update Agenda
set WorkDay = 0 where WeekDay = 1
and dt in
--manually arranged special holidays:
(
'20080101',
'20080423',
'20080519',
'20080929',
'20080830',
'20080215',
'20080216',
'20080217'
)
How can this returns ONLY holidays (each sunday and manually arranged holidays) and NOT workdays(i mean not show workdays in table) and gives me a difference between sundays and special holidays (Because I want to do a special treatment in special holidays) in the SAME column? Every change in the code can be acceptable 🙂 Thanks...

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It's difficult to understand what you're trying to do. Please will you post a sample of the results you're getting, and then show how they should look?

John

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

I understand... if "every code change can be acceptable", then consider implementing that which is found at the following URL:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)