table variables - 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

SQL Server Development (2000)

table variables

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-14 : 08:34:57

Ok everything I have read said this is possible. (joining on table variables)

SQL2K

SET NOCOUNT ON
CREATE TABLE #users (UID int, PID int, uname varchar(20))
insert #users values(1,1, 'john')
insert #users values(2,2, 'mary')
insert #users values(3,3, 'frank')
insert #users values(4,4, 'julie')
insert #users values(5,5, 'lasalle')

CREATE TABLE #events (EID int PRIMARY KEY IDENTITY(1,1) NOT NULL, UID int,
sdesc varchar(50), sdate smalldatetime, edate smalldatetime, clblock char(10))
insert #events values(1, '1:1 called someone', '11/4/2001 11:00:00:00', '11/4/2001 12:30:00:00','whitesmoke')
insert #events values(1, '1:2 do something', '11/4/2001 14:00:00:00', '11/4/2001 15:00:00:00', 'whitesmoke')
insert #events values(1, '1:3 went walking', '11/4/2001 12:30:00:00', '11/4/2001 13:30:00:00', 'whitesmoke')
insert #events values(1, '1:4 ate lunch', '11/4/2001 9:30:00:00', '11/4/2001 10:30:00:00', 'whitesmoke')
insert #events values(2, '2:1 email to someone', '11/4/2001 9:00:00:00', '11/4/2001 10:30:00:00', 'whitesmoke')
insert #events values(2, '2:2 called someone', '11/4/2001 11:00:00:00', '11/4/2001 12:30:00:00', 'whitesmoke')
insert #events values(2, '2:3 do something', '11/4/2001 14:00:00:00', '11/4/2001 15:30:00:00', 'whitesmoke')
insert #events values(3, '3:1 went walking', '11/4/2001 8:00:00:00', '11/4/2001 9:30:00:00', 'whitesmoke')
insert #events values(3, '3:2 ate lunch', '11/4/2001 9:00:00:00', '11/4/2001 10:30:00:00', 'whitesmoke')
insert #events values(4, '4:1 called someone', '11/4/2001 11:00:00:00', '11/4/2001 12:30:00:00', 'whitesmoke')
insert #events values(4, '4:2 do something', '11/4/2001 9:00:00:00', '11/4/2001 15:30:00:00', 'whitesmoke')
insert #events values(4, '4:3 went walking', '11/4/2001 8:00:00:00', '11/4/2001 9:30:00:00', 'whitesmoke')
insert #events values(4, '4:4 ate lunch', '11/4/2001 9:30:00:00', '11/4/2001 10:30:00:00', 'whitesmoke')
insert #events values(5, '5:1 lasalle', '11/4/2001 9:00:00:00', '11/4/2001 15:30:00:00', 'red')
insert #events values(5, '5:2 lasalle', '11/4/2001 8:00:00:00', '11/4/2001 9:30:00:00', 'red')
insert #events values(5, '5:3 lasalle', '11/4/2001 9:30:00:00', '11/4/2001 10:30:00:00', 'red')

declare @sdate smalldatetime
declare @edate smalldatetime
declare @diff int
declare @minuteview int
declare @x int
declare @timevalue table (step int, sdate smalldatetime)

SET @sdate='11/04/01 8:00:00:00'
SET @edate='11/04/01 17:00:00:00'
SET @x=1
SET @minuteview=30
SET @diff = datediff(n,@sdate, @edate)

while @sdate <= @edate
begin
insert @timevalue
values (@x, @sdate)
set @x=@x+1
set @sdate=dateadd(n,@minuteview, @sdate)
end

select *
FROM #events
INNER JOIN @timevalue ON #events.sdate=@timevalue.sdate

drop table #users
drop table #events

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources