Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reg: Date Inerval


Reg: Date Inerval

Author
Message
vs.satheesh
vs.satheesh
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 630
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-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7470 Visits: 15142
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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 37978
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45151 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22800
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