Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reg: Date Inerval Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 10:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:28 AM
Points: 61, Visits: 401
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:50 AM
Points: 136, Visits: 260

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,945, Visits: 3,025
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
Posted Friday, December 28, 2012 8:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,386, Visits: 9,962
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
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.



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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
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?)


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1400989
Posted Friday, December 28, 2012 11:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,678, Visits: 19,552
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