Fetching sequential dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Fetching sequential dates

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2008-06-02 : 05:35:27

hai,

set dateformat dmy
create table tbl_sampemptable
(
employeeid int,
StartDate datetime
)

declare @employeeid int
set @employeeid=1
declare @startdate datetime,@enddate datetime
while(@employeeid<=1000)
begin
set @startdate='01/05/2008'
set @enddate='31/05/2008'
while(@startdate<=@enddate)
begin
if(@employeeid<>1 and @startdate<>'02/05/2008')
insert into tbl_sampemptable values (@employeeid,@startdate)
else if(@employeeid=1)
insert into tbl_sampemptable values (@employeeid,@startdate)z
set @startdate=dateadd(day,1,@startdate)

end
set @employeeid=@employeeid+1
end

select * from tbl_sampemptable
drop table tbl_sampemptable
set dateformat mdy

i have to select records depending on @count parameter to this table.Depending on this parameter value it should fetch sequential dates.For example

if @count=2then result should be like this,

EmployeeID FromDate ToDate

1 01/05/2008 02/05/2008

1 03/05/2008 04/05/2008

.

.

2 03/05/2008 04/05/2008 //note that here 01/05/2008 is not selected

because 02/05/2008 is missing

2 05/05/2008 06/05/2008

.

.

3 03/05/2008 04/05/2008 //note that here 01/05/2008 is not selected

because 02/05/2008 is missing

3 05/05/2008 06/05/2008

.

.

if @count=3 then result should be like this,

EmployeeID FromDate ToDate

1 01/05/2008 03/05/2008

1 04/05/2008 06/05/2008

.

.

2 03/05/2008 05/05/2008 //note that here 01/05/2008 is not selected

because 02/05/2008 is missing

2 06/05/2008 08/05/2008

.

.

3 03/05/2008 05/05/2008 //note that here 01/05/2008 is not selected

because 02/05/2008 is missing

3 06/05/2008 08/05/2008

.

.

how can i do this.please help me.thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 06:01:17

Can you tell what your requirement is? That makes more sense rather than query. Some sample data from source will also help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 06:08:53

[code]CREATE TABLE tbl_SampEmpTable
(
RowID INT IDENTITY(0, 1),
EmployeeID INT,
StartDate DATETIME
)

INSERT tbl_SampEmpTable
(
employeeid,
StartDate
)
SELECT e.Number,
d.Number
FROM Tally AS e
CROSS JOIN Tally AS d
WHERE e.Number BETWEEN 1 AND 1000
AND d.Number BETWEEN 39567 AND 39597
AND (
e.Number = 1 AND CAST(d.Number AS DATETIME) = '20080502'
OR
CAST(d.Number AS DATETIME) <> '20080502'
)
ORDER BY e.Number,
d.Number

DECLARE @Count INT
SET @Count = 3

SELECT EmployeeID,
MIN(StartDate),
MAX(StartDate)
FROM tbl_SampEmpTable
GROUP BY EmployeeID,
RowID / @Count
ORDER BY EmployeeID,
MIN(StartDate)

DROP TABLE tbl_SampEmpTable[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 06:11:00

And here is how you create and populate the Tally table.

SELECT		TOP 1000000
		IDENTITY(0, 1) AS Number
INTO		Tally
FROM		sysobjects AS s1
CROSS JOIN	sysobjects AS s2

CREATE UNIQUE CLUSTERED INDEX IX_Tally ON Tally (Number) WITH FILLFACTOR = 100

E 12°55'05.25"
N 56°04'39.16"

cool.mugil
Starting Member

32 Posts

Posted - 2008-06-02 : 06:22:47

thanks for your reply.
but if i execute this one

SELECT TOP 1000000
IDENTITY(0, 1) AS Number
INTO Tally
FROM sysobjects AS s1
CROSS JOIN sysobjects AS s2

CREATE UNIQUE CLUSTERED INDEX IX_Tally ON Tally (Number) WITH FILLFACTOR = 100

it throws error.the error message is incorrect syntax near '0' .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 06:24:03

Sorry...

SELECT TOP 1000000
IDENTITY(INT,0, 1) AS Number
INTO Tally
FROM sysobjects AS s1
CROSS JOIN sysobjects AS s2

CREATE UNIQUE CLUSTERED INDEX IX_Tally ON Tally (Number) WITH FILLFACTOR = 100

E 12°55'05.25"
N 56°04'39.16"

cool.mugil
Starting Member

32 Posts

Posted - 2008-06-02 : 06:25:54

i am using sql server 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 06:37:30

quote:
Originally posted by cool.mugil

i am using sql server 2000.

the above query should work fine in sql 2000. Are you still getting error?

cool.mugil
Starting Member

32 Posts

Posted - 2008-06-02 : 06:39:42

sorry to disturb again.but it still shows the same error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 06:44:59

First of all, you should always have a Tally table in your system.
I prefer using two Tally tables, one for numbers and one for dates.

This is how I create them

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TallyNumber')
	DROP TABLE	TallyNumber

SELECT		TOP 1000000
		IDENTITY(INT, 0, 1) AS Number
INTO		TallyNumber
FROM		sysobjects AS s1
CROSS JOIN	sysobjects AS s2
CROSS JOIN	sysobjects AS s3
CROSS JOIN	sysobjects AS s4

ALTER TABLE	TallyNumber
ADD CONSTRAINT	PK_TallyNumber PRIMARY KEY CLUSTERED
		(
			Number ASC
		)
WITH		FILLFACTOR = 100

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TallyDate')
	DROP TABLE	TallyDate

SELECT		TOP 65536
		CAST(Number AS SMALLDATETIME) AS Date
INTO		TallyDate
FROM		TallyNumber
ORDER BY	Number

ALTER TABLE	TallyDate
ALTER COLUMN	Date SMALLDATETIME NOT NULL

ALTER TABLE	TallyDate
ADD CONSTRAINT	PK_TallyDate PRIMARY KEY CLUSTERED
		(
			Date ASC
		)
WITH		FILLFACTOR = 100

Now you should confirm that the tables have been created and populated

SELECT	MIN(Number) AS minNumber,
	MAX(Number) AS maxNumber
FROM	TallyNumber

SELECT	MIN(Date) AS minDate,
	MAX(Date) AS maxDate
FROM	TallyDate

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 06:47:03

And then your solution could look something similar to this

CREATE TABLE	tbl_SampEmpTable
		(
			RowID INT IDENTITY(0, 1),
			EmployeeID INT,
			StartDate DATETIME
		)

INSERT		tbl_SampEmpTable
		(
			EmployeeID,
			StartDate
		)
SELECT		e.Number,
		d.Date
FROM		TallyNumber AS e
CROSS JOIN	TallyDate AS d
WHERE		e.Number BETWEEN 1 AND 1000
		AND d.Date BETWEEN '20080501' AND '20080531'
		AND	(
				e.Number = 1 AND d.Date = '20080502'
			OR
				d.Date <> '20080502'
			)
ORDER BY	e.Number,
		d.Date

DECLARE @Count INT
SET	@Count = 3

SELECT		EmployeeID,
		MIN(StartDate),
		MAX(StartDate)
FROM		tbl_SampEmpTable
GROUP BY	EmployeeID,
		RowID / @Count
ORDER BY	EmployeeID,
		MIN(StartDate)

DROP TABLE tbl_SampEmpTable

E 12°55'05.25"
N 56°04'39.16"

cool.mugil
Starting Member

32 Posts

Posted - 2008-06-02 : 07:18:23

thanks for your valuable help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 07:37:31

You're welcome.
I believe the query now is much faster?

My initial testing showed an improvement from 78 seconds to less than 1 second.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources