What you think about that? - 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 2005 Forums

Transact-SQL (2005)

What you think about that?

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-06-28 : 02:55:28

Hi I’m trying to do a consult that I think is putty complicated, so I though that you guys could give a bit of your experience. The idea is get the first and last occurrence of the same group, as in the forum that I’m doing that there is a table of threads and the page must show not only the user who created the thread but also the last user who made the modification. I could do it just creating a new field in the table, which I would update for every update in the thread and just consult it after. Or try to do a killer query to consult the group of the same thread ordered by date insert it into table exclude the middle ones, create a new table to put them in order. (I say “killer” because through this way would more operation than I mentioned, as while and a serie of selects)

I think that if I insert the new fields would worth the processing in the final, what you think?

Thank you very much.

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-06-28 : 03:38:57

I hadn't made the procedure until I had posted here, though it compile but I didn't tested, here is the code, which I think it's not worth than I add the new fields:

ALTER PROCEDURE [dbo].[solarForumThreads]
@forum_id int,
@PageIndex int,
@PageSize int,
@PageOrder int,
@PageType int,
@PageWay bit,
@Period datetime
AS
BEGIN

Declare @FirstRow int,
@LastRow int,
@Records int,
@Paginas float,
@Pages int,
@num int,
@grupo int,
@lastUser_id int,
@lastUser nvarchar(20),
@firstCreation datetime

set @FirstRow = ( @PageIndex - 1) * @PageSize + 1
set @LastRow = @PageSize + (@PageIndex - 1) * @PageSize

create table #threadsTemp
(
RowNumber int IDENTITY (1, 1) NOT NULL,
[user_id] int,
lastUser_id int,
nickname nvarchar(20),
lastUser nvarchar(20),
grupo int,
firstCreation datetime,
creation datetime,
title nvarchar(100),
answer bit,
rate int,
[views] int,
posts int,
pages int
);

--set IDENTITY_INSERT #threadsTemp on

insert into #threadsTemp Select top(@LastRow) t.user_id, 0,u.nickname, 'nothing',t.grupo, '1/1/2007',
t.creation, t.title, t.answer, t.rate as 'rate', t.views, t.posts, 0
from solarThreads t inner join solarUsers u on t.user_id = u.user_id
where (t.forum_id = @forum_id and t.father = 1 and t.type = @PageType and t.creation > @Period)
order by
case when @PageOrder = 0 and @PageWay = 1 then u.nickname end asc,
case when @PageOrder = 1 and @PageWay = 1 then t.creation end asc,
case when @PageOrder = 2 and @PageWay = 1 then t.posts end asc,
case when @PageOrder = 3 and @PageWay = 1 then t.title end asc,
case when @PageOrder = 4 and @PageWay = 1 then t.views end asc,
case when @PageOrder = 0 and @PageWay = 0 then u.nickname end desc,
case when @PageOrder = 1 and @PageWay = 0 then t.creation end desc,
case when @PageOrder = 2 and @PageWay = 0 then t.posts end desc,
case when @PageOrder = 3 and @PageWay = 0 then t.title end desc,
case when @PageOrder = 4 and @PageWay = 0 then t.views end desc

Set @Records = (Select Count(*) From solarThreads
where (forum_id = @forum_id and father = 1 and type = @PageType and creation > @Period))

Set @Paginas = (Convert(Float,@Records) / Convert(Float,@PageSize))

Set @Pages = Ceiling(@Paginas)

set @num = 0

while (@num <= (Select count(*) from #threadsTemp))
begin
set @grupo = (select grupo from #threadsTemp where RowNumber = @num)
set @lastUser_id = (select top(1) [user_id] from solarThreads
where thread_id = @grupo order by creation asc)
set @lastUser = (select top(1) nickname from solarUsers
where [user_id] = @lastUser_id)
set @firstCreation = (select top(1) creation from solarThreads
where thread_id = @grupo order by creation asc)
Update #threadsTemp
set lastUser_id = @lastUser_id, lastUser = @lastUser, firstCreation = @firstCreation, pages = @Pages
set @num = @num + 1
end

Select * from #threadsTemp where RowNumber between @FirstRow and @LastRow

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources