Reg: Date Inerval
-
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)
.
.
.
.
.
-
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.
-
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
-
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.
-
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';
[font="Arial Black"] And is that join ANSI compliant?[/font]
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.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) -
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply