Reg: Date Inerval

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Development » Reg: Date Inerval

Add to briefcase

Reg: Date Inerval

Expand / Collapse

Author

Message

Posted Wednesday, November 21, 2012 10:22 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 AM
Points: 53, Visits: 296

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)
.
.
.
.
.

Post #1387692

Posted Thursday, December 27, 2012 5:08 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:48 AM
Points: 56, Visits: 136

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.

Post #1400565

Posted Friday, December 28, 2012 8:13 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

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

Post #1400932

John Mitchell-245523

Posted Friday, December 28, 2012 8:52 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 4,428, Visits: 7,201

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

Post #1400945

Posted Friday, December 28, 2012 9:05 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617, Visits: 27,450

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

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)

Post #1400947

Posted Friday, December 28, 2012 11:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792

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?) Tongue

Tongue

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1400989

Posted Friday, December 28, 2012 11:51 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 1,500, Visits: 18,191

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.

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1400996

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse