Complex join

[Guest_imported]

I want this to be a matrix at the end. It is
very limiting right now.

Any suggestions please help it should function and
you can view the end required result as is.



/*
CREATE TABLE #users (UID int, uname varchar(20))
insert #users values(1, 'john')
insert #users values(2, 'mary')
insert #users values(3, 'frank')

CREATE TABLE #events (EID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
UID int, sdesc varchar(50), sdate datetime)
insert #events values(1, 'email to client', '11/4/2001 8:30:00:00')
insert #events values(1, 'allow for dups', '11/4/2001 8:00:00:00')
insert #events values(1, 'went to client', '11/4/2001 11:00:00:00')
insert #events values(1, 'called client', '11/4/2001 13:00:00:00')
insert #events values(2, 'email to someone', '11/4/2001 9:00:00:00')
insert #events values(2, 'called someone', '11/4/2001 11:00:00:00')
insert #events values(2, 'do something', '11/4/2001 14:00:00:00')
insert #events values(3, 'went walking', '11/4/2001 8:00:00:00')
insert #events values(3, 'ate lunch', '11/4/2001 9:00:00:00')

CREATE TABLE #dayview (id int identity(1,1), sdate datetime)-- holds all possible time values

select * from #users
select * from #events
select * from #dayview
delete from #events
delete from #dayview

*/

--DECLARE ALL VARS
DECLARE @stime smalldatetime --office start date time
DECLARE @etime smalldatetime --office end time
DECLARE @timeh smalldatetime --variable
DECLARE @inc int --office increment
DECLARE @today smalldatetime --today
DECLARE @maxcol int --maximum headers

SET @stime= '11/4/2001 8:00:00:00'
SET @etime= '11/4/2001 17:00:00:00'
SET @inc=30
SET @today = '11/4/2001'

--SET TIME COLUMN
delete from #dayview
while @stime <= @etime
begin
insert into #dayview values (@stime)
set @stime = dateadd(mi, @inc ,@stime)
end

/*
--SET HEADER

--this is where I was going to increment the
--users table and then return the column headings
--something like select

*/



--CROSS TAB OF EVENTS
--this needs to be dynamic based on how many
--rows are in the users table
--not quite sure how to do this yet
SELECT convert(varchar(20),a.sdate, 108),
MIN( CASE b.UID WHEN 1 THEN b.sdesc END ),
MIN( CASE b.UID WHEN 2 THEN b.sdesc END ),
MIN( CASE b.UID WHEN 3 THEN b.sdesc END )
FROM #dayview a
LEFT JOIN #events b ON a.sdate=b.sdate
GROUP BY a.sdate
ORDER BY a.sdate

 

[guljar]

Here someway to solve this.

DECLARE @stime smalldatetime --office start date time
DECLARE @etime smalldatetime --office end time
DECLARE @timeh smalldatetime --variable
DECLARE @inc int --office increment
DECLARE @today smalldatetime --today
DECLARE @maxcol int --maximum headers
declare @UID as int,@UName as varchar(20)
Declare @strSql varchar(8000)
SET @stime= '11/4/2001 8:00:00:00'
SET @etime= '11/4/2001 17:00:00:00'
SET @inc=30
SET @today = '11/4/2001'

--SET TIME COLUMN
delete from #dayview

while @stime <= @etime
begin
insert into #dayview values (@stime)
set @stime = dateadd(mi, @inc ,@stime)
end

set @strSql=''
declare UCursor CURSOR for
select * from #Users order by UID
open UCursor
FETCH NEXT FROM UCursor
INTO @UID,@UName
WHILE @@FETCH_STATUS = 0
Begin
set @strSql= @strSql+'Min(CASE b.UID WHEN '+ convert(varchar(10),@UID) + ' THEN b.sdesc END) AS ' + @UName +','
FETCH NEXT FROM UCursor
INTO @UID,@UName
End
close UCursor
deallocate UCursor
if len(@strSql)>0
set @strSql=left(@strSql,len(@strSql)-1)
select @strSql
execute('SELECT convert(varchar(20),a.sdate, 108),'+@strSql+' FROM #dayview a LEFT JOIN #events b ON a.sdate=b.sdate GROUP BY a.sdate')


Only problem is if your user table contains a lot of row then the total string(@strSql) may exceed the length 8000(about 166 users can be held in this string). So I would say you turn the crosstab other way;i.e. place time as column header and user name as row header. That way as your data in #dayview is limited, you will get two benefit a) there would not be any posibility to exceed 8000
b) the scan for cursor also would take a few amount of time.
Hope it will help you.