setting group numbers - 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)

setting group numbers

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-21 : 13:52:58

I have a table like the one below:

resid closer
----------- --------------------
1 skillile-holland
1 skillile-holland
1 skillile-holland
5 mschmidt-grandha
11 green room
12 room 12
12 room 12
6 mlawless-holland

I want to insert it into another table with the
resid or another field added to the table that
increments based on the change. How can I do this
without a cursor.

End result

resid closer
----------- --------------------
1 skillile-holland
1 skillile-holland
1 skillile-holland
2 mschmidt-grandha
3 green room
4 room 12
4 room 12
5 mlawless-holland
6 jsmith-holland
6 jsmith-holland

slow down to move faster...

izaltsman
A custom title

1139 Posts

Posted - 2001-12-21 : 14:18:13

I suspect order isn't important... If that's the case, then something like this should work:


SELECT 
	 1 + (SELECT COUNT(DISTINCT closer) FROM #mytable as a WHERE a.closer < mt.closer) as resid, 
	mt.closer
FROM 
	#mytable as mt

Edited by - izaltsman on 12/21/2001 14:19:37

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-21 : 14:42:50

This is what I am trying to do now.

I added your solution as (test int).

declare @closings table (
clsident int primary key identity(1,1),
closeid int,
ordid int,
resid int,
cldate smalldatetime,
cldateend smalldatetime,
closer varchar(20),
priority int,
location varchar(20),
propadd varchar(50),
metfileid varchar(20),
test int
)

insert @closings
select a.closeid,
a.ordid,
a.resid,
a.cldate,
a.cldateend,
b.label as closer,
c.priority,
d.label as location,
e.propadd,
e.metfileid,
(SELECT 1 + (SELECT COUNT(DISTINCT closer) FROM @closings as a WHERE a.closer < mt.closer)
mt.closer FROM @closings mt )

from tblorder_close a
inner join tblresource b on a.resid=b.resid
inner join tblresource_join c on a.resid=c.resid and c.oid=@oid
inner join tbllocation d on a.locid=d.locid
inner join tblorder e on a.ordid=e.ordid
where a.moid=@oid
AND (datepart(dd,a.cldate) = datepart(dd,@starttime)
AND datepart(mm,a.cldate) = datepart(mm,@starttime)
AND datepart(yyyy,a.cldate) = datepart(yyyy,@starttime)
)

These closers all have a unique resid I want to keep the uniqueness
but start from 1 and go to max# of users.

Thanks

slow down to move faster...

izaltsman
A custom title

1139 Posts

Posted - 2001-12-21 : 15:48:23

Well, first of all, you were missing a comma. Secondly, you need to populate the table before you query it. Finally for some reason SQL2k does not seem to treat table variables the same as temporary tables within a subquery, so I replaced table variable with a temp table. Will this do the trick for you?


create table #closings  (
	clsident int primary key identity(1,1), 
	closeid int,
	ordid int,
	resid int,
	cldate smalldatetime,
	cldateend smalldatetime,
	closer varchar(20),
	priority int,
	location varchar(20),
	propadd varchar(50),
	metfileid varchar(20),
	test int
	)


insert #closings
select a.closeid,
	a.ordid,
	a.resid,
	a.cldate,
	a.cldateend,
	b.label as closer,
	c.priority,
	d.label as location,
	e.propadd,
	e.metfileid,
 	NULL
from tblorder_close a
inner join tblresource b on a.resid=b.resid
inner join tblresource_join c on a.resid=c.resid and c.oid=@oid
inner join tbllocation d on a.locid=d.locid
inner join tblorder e on a.ordid=e.ordid
where a.moid=@oid
	AND (datepart(dd,a.cldate) = datepart(dd,@starttime) 
	AND datepart(mm,a.cldate) = datepart(mm,@starttime)
	AND datepart(yyyy,a.cldate) = datepart(yyyy,@starttime)	
	)


update #closings
set 
	test = x.resid
FROM 
	#closings 
	INNER JOIN 
		(SELECT 
		    1 + (SELECT COUNT(DISTINCT closer) FROM #closings as a WHERE a.closer < mt.closer) as resid, 
			mt.closer
		FROM 
			#closings as mt) as x ON #closings.closer = x.closer

select * from #closings order by test

Edited by - izaltsman on 12/21/2001 15:49:40

Edited by - izaltsman on 12/21/2001 15:50:28

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-21 : 16:21:08

yeah,

we must be thinking alike thats what I was thinking with you.

But let me back up. I may be up a creek here.
If you see my fields I also have priority column.

My last statement in this proc is to select as many closers(columns) in
priority asc.

ie.

/*
resid closer priority
----------- ----------------- ---------
1 skillile-holland 6
1 skillile-holland 6
1 skillile-holland 6
2 mschmidt-grandha 5
3 green room 5
4 room 12 4
4 room 12 3
5 mlawless-holland 4
6 jsmith-holland 2
6 jsmith-holland 2

*/

SET @x=1
SET @columncount=(select count(distinct resid) from @closings)

--this is my last statement to return as many select statement
--as I have columns --by priority by user

while @x <= @columcount
begin
select * from @closings
where resid=@x
order by priority --this will not work that is where I am getting messed up
end

the priority is not unique reason the closers are not always active
I am only chosing the ones with activity but the priority still needs
to be asc.

END RESULT: I need all my closers with a unique key and sorted by priority

slow down to move faster...

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-21 : 18:29:20

Here is what I think I need. I added another table
to come up with the priority id's this will inc
the resids as they came in.

thank you izaltsman for all your help. I will use your
solution with the backside to another sproc.

Thanks for the time..

ALTER PROCEDURE s_cal_office_matrix2
(
@ddate smalldatetime='12/17/2001',
@oid int=36
--@starttime smalldatetime output,
--@endtime smalldatetime output,
--@columncount int output,
--@minuteview int output,
--@totalsteps int output
)

as

SET NOCOUNT ON

--declare vars
declare @closedate smalldatetime
declare @starttime smalldatetime
declare @endtime smalldatetime
declare @columncount int
declare @minuteview int
declare @x int

declare @closings table (
clsident int primary key identity(1,1),
closeid int,
ordid int,
resid int,
cldate smalldatetime,
cldateend smalldatetime,
closer varchar(20),
priority int,
location varchar(20),
propadd varchar(50),
metfileid varchar(20)
)

declare @priority table (
priority int identity(1,1),
prior_cls int,
resid int
)

--set vars and table
SET @closedate=@ddate --save the original date with no time
SET @starttime= convert(smalldatetime,(convert(varchar(20), @ddate, 101) + ' 6:00:00'), 101)
SET @endtime= convert(smalldatetime,(convert(varchar(20), @ddate, 101) + ' 20:00:00'), 101)
SET @minuteview=1 --set the minutes to 1 hour

--get daily closings for that office
--and place values into variable table
insert @closings
select a.closeid,
a.ordid,
a.resid,
a.cldate,
a.cldateend,
b.label as closer,
c.priority,
d.label as location,
e.propadd,
e.metfileid
from tblorder_close a
inner join tblresource b on a.resid=b.resid
inner join tblresource_join c on a.resid=c.resid and c.oid=@oid
inner join tbllocation d on a.locid=d.locid
inner join tblorder e on a.ordid=e.ordid
where a.moid=@oid
AND (datepart(dd,a.cldate) = datepart(dd,@starttime)
AND datepart(mm,a.cldate) = datepart(mm,@starttime)
AND datepart(yyyy,a.cldate) = datepart(yyyy,@starttime)
)

--columncount
SET @columncount=(select count(distinct resid) from @closings)

--minuteview
if exists (select cldate from @closings where datepart(mi, cldate)=30 or datepart(mi,cldateend) = 30)
SET @minuteview=30

if exists (select cldate from @closings where datepart(mi, cldate)=15 or datepart(mi,cldateend) = 15)
SET @minuteview=15

if exists (select cldate from @closings where datepart(mi, cldate)=45 or datepart(mi,cldateend) = 45)
SET @minuteview=15

--insert @priority and set the ranking order of the column
insert @priority
select distinct priority, resid from @closings
order by priority, resid

--set inc var
set @x=1

--return rs

while @x <= @columncount
begin

select a.priority,
b.closeid,
b.ordid,
b.cldate,
b.cldateend,
b.closer,
b.location,
b.metfileid

from @priority a
inner join @closings b on a.resid=b.resid
where a.priority=@x
order by a.priority, b.cldate

set @x=@x+1
end

--test s_cal_office_matrix2

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources