SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reg: Date Inerval


Reg: Date Inerval

Author
Message
vs.satheesh
vs.satheesh
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 645
Hi

i am working examination schedule project.now my scenario is university already given two dates.examination start date and end date.i want out put is automated exam schedule.
The Condition is

1) Holiday should not occur
2) based on intervel should be work
3) Sunday should not occur

I have one table name called holiday master.All the holiday dates i enterd that table

for example

date Holidayname

07-11-2012 Local Holiday
17-11-2012 Deepavali
30-11-2012 Local Holiaday
12-12-2012 Pooja holiday


I want following Result
for example Examstart Date : 01-11-2012
Intervel day :1


output should be like this (Except sunday and public holiday)

01-11-2011
03-11-2011
05-11-2011
08-11-2011(06-11-2011 is intervel holiday 07-11-2011 is public holiday)
.
.
.
.
.
avdhut.k
avdhut.k
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 266
Hi,

I had tried ur doubt reg date Interval.

Follwing are the steps.

Step 1 : Create Table to Store Output,and Insert your First date that is start date.

Create Table #Test
(
Pk_Id Int Identity(1,1),
MyDate DateTime
)


Insert Into #Test
Values ('2012-11-01')



Step 2 : Create a Table For HolidayMaster Where you can Insert all the Dates Which has Holiday.

Create Table #HolidayMaster
(
Holiday DateTime
)

Insert Into #HolidayMaster
Values ('2012-11-05'),('2012-11-15'),('2012-11-23'),('2012-11-27')

Select * From #HolidayMaster


Step 3 : Just Execute the following command as a whole,you will get list of your required dates.
Assign the @StartDate And @EndDate

Declare @StartDate As Datetime = '2012-11-01'
Declare @EndDate As Datetime = '2012-11-30'


While (@StartDate < @EndDate)
Begin

If @StartDate = (Select Holiday From #HolidayMaster Where Holiday = @StartDate)
Begin
Set @StartDate = DateAdd(dd,1,@StartDate)
End
Else
Begin
Set @StartDate = DateAdd(dd,2,@StartDate)
End


If @StartDate = (Select Holiday From #HolidayMaster Where Holiday = @StartDate)
Begin
Select 'Holiday'
End
Else
Begin
Insert Into #Test
Select @StartDate
End

End


Step 4 : Check Table #Test for Output.

You can further modified it accordingly.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14269 Visits: 15974
CELKO (12/28/2012)
Here is atrick weith a calendar table.

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

That's got to be a maintenance nightmare, hasn't it? Every time you want to add a new holiday, you have to change the julian date for all subsequent rows. And is that join ANSI compliant?

How about this?
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
working_day bit NOT NULL,
);

INSERT INTO Calendar
VALUES ('2007-04-05', 1),
('2007-04-06', 0), -- good Friday
('2007-04-07', 1),
('2007-04-08', 0), -- Easter Sunday
('2007-04-09', 1),
('2007-04-10', 1); --Tuesday

SELECT SUM(CAST(working_day AS int))
FROM Calendar
WHERE cal_date >= '2007-04-05'
AND cal_date < '2007-04-10';



John
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39714 Visits: 38563
John Mitchell-245523 (12/28/2012)
CELKO (12/28/2012)
Here is atrick weith a calendar table.

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

That's got to be a maintenance nightmare, hasn't it? Every time you want to add a new holiday, you have to change the julian date for all subsequent rows. And is that join ANSI compliant?

How about this?
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
working_day bit NOT NULL,
);

INSERT INTO Calendar
VALUES ('2007-04-05', 1),
('2007-04-06', 0), -- good Friday
('2007-04-07', 1),
('2007-04-08', 0), -- Easter Sunday
('2007-04-09', 1),
('2007-04-10', 1); --Tuesday

SELECT SUM(CAST(working_day AS int))
FROM Calendar
WHERE cal_date >= '2007-04-05'
AND cal_date < '2007-04-10';



John


Yes, the join is ANSI compliant, ANSI-89, not ANSI-92. Hey, Mr. Celko, you really should start using the newer join syntax like the rest of us.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86978 Visits: 41107
John Mitchell-245523 (12/28/2012)
CELKO (12/28/2012)
Here is atrick weith a calendar table.

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

And is that join ANSI compliant?



BWAAA_HAAA!!!! SNORT! (oops... sorry... did that come out loud?):-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3129 Visits: 24107
CELKO (12/28/2012)
Here is atrick weith a calendar table.

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


SQL Server 2005 doesn't support the DATE datatype. Also '2007-04-10' is DATEFORMAT dependent and could be interpreted as either 10th April or 4th October.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search