date recurse with associated orders

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

SQL Server Development (2000)

date recurse with associated orders

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-22 : 09:40:14

Sean writes "I am trying to write a sproc that lists all times with an increment value ie. 8:00, 9:00, 10:00 (inc=60), or 8:30, 9:30, 10:00 (inc=30). At each increment it should list linked orders that fall between each time zone. I also want to color code the time span. Here is my initial sproc.

use MTC
go
/* DROP STATEMENT */
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spdates_sel')
DROP PROCEDURE spdates_sel
GO

/* BEGIN STORED PROCEDURE */
CREATE PROCEDURE spdates_sel

/* SECURITY IS YOUR RESPONSIBILITY */
--WITH ENCRYPTION
as

/* VARS USED */
DECLARE @sdate smalldatetime
DECLARE @edate smalldatetime
DECLARE @sdateh smalldatetime
DECLARE @inc int
DECLARE @sqlString varchar(100)

/* SET INITIAL VALUES */
SET @sdate = '10/12/2001 8:00:00'
SET @edate = '10/12/2001 17:00:00'
SET @inc = 30

set nocount on

/* CREATE THE TEMP TABLE */
create table #temp (
sdate smalldatetime,
edate smalldatetime,
item varchar(30)
)

/* INSERT FROM ORDERS VALUES SDATE, EDATE, ITEM */
SET @sqlString = 'select sdate, edate, item from orders'

insert into #temp
Exec (@sqlString)
while @sdate <= @edate
begin
set @sdateh = @sdate

if exists (select sdate from #temp where sdate between @sdate and (@sdate +@inc))
begin
set @sdate = dateadd(mi, @inc ,@sdate)

end
else
begin
insert into #temp (sdate) values (@sdate)

end

end

--select * from #temp order by sdate

/* SELECT DISTICT TO RETURN */
select distinct convert(varchar(30),sdate,108)as starttime, convert(varchar(30),sdate,11)as startdate,convert(varchar(30),edate,11)as enddate, convert(varchar(30),edate,108)as endtime , (datediff(mi, sdate, edate)/@inc) as diff, item FROM #temp order by starttime

/* KILL THE TEMP TABLE */
drop table #temp

GO

/* Grant Permisstions */
--GRANT EXECUTE ON spdates_sel TO "IUSR_WAREZ"

GO"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources