Counter? - 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

Transact-SQL (2000)

Counter?

Author

Topic

Genesis
Starting Member

6 Posts

Posted - 2008-04-24 : 12:11:06

Hey guys I was wondering if its possible to declare some sort of counter or implement a counter in a huge select statement? I know its possible in a stored procedure, but not sure if its possible in a very long select statement.

For example the problem that I'm running into is that say I need to add X supervisor names to the manifest of each department. So department A has 4 names, I would need to add 3 supervisor names to the end of department A. Department B has 2 names and I would need to add 1 supervisor name to department b and so on.

-- Department A
(case when....) as 'Name1'
(case when....) as 'Name2'
(case when....) as 'Name3'
(case when....) as 'Name4'
(case when....) as 'Name5' -- Supervisor Name
(case when....) as 'Name6' -- Supervisor Name
(case when....) as 'Name7' -- Supervisor Name

-- Department B
(case when....) as 'Name1'
(case when....) as 'Name2'
(case when....) as 'Name3' -- Supervisor Name

Since I'll never know how many names are in the Department, I won't know when to start adding the 1st supervisor. By using a counter I can say when the list of employees end, start at counter 1 and add the first supervisor, counter 2 add second supervisor so on. So counter would make this problem of when to add the supervisor name so much easier. Thanks for your help and I hope I didn't make this too confusing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 12:25:22

Can you provide some sample data for your scenario. That should make it more clear.

Genesis
Starting Member

6 Posts

Posted - 2008-04-24 : 14:49:42

Yeah, for example what the current sql script is suppose to do is take the names of the Employee's in the dept and assign them into fields EMPGROUP1-20. When you reach the end of the names of employees, you add the supervisors of that dept to the end of that list.

So the general SQL statement to add the employees to Department A would be

select COMPANY, DEPARTMENT,

(case when (select count(employee) from HRTABLE where department = 'A') > 2 then
(select top 1 EMPLOYEE
from
(select top 2 EMPLOYEE
from HRTABLE
where department = 'A'
order by EMPLOYEE asc)EE order by EMPLOYEE desc)
else ''
end) as 'empgroup2',

(case when (select count(employee) from HRTABLE where department = 'A') > 3 then
(select top 1 EMPLOYEE
from
(select top 3 EMPLOYEE
from HRTABLE
where department = 'A'
order by EMPLOYEE asc)EE order by EMPLOYEE desc)
else ''
end) as 'empgroup3'

from test.project
where STATUS = 'FULL TIME'

RESULTS
-------
empgroup1 = EMPLOYEE NAME1
empgroup2 = EMPLOYEE NAME2
empgroup3 = EMPLOYEE NAME3

...and this code goes on until empgroup20. Now a list of supervisor names needs to be added to empgroup fields not being used by EMPLOYEE NAMES. This is where I think a counter would make things easier to implement the SUPERVISOR portion. So in essence...

RESULTS
-------
empgroup1 = EMPLOYEE NAME1
empgroup2 = EMPLOYEE NAME2
empgroup3 = EMPLOYEE NAME3
empgroup4 = SUPERVISOR NAME1
empgroup5 = SUPERVISOR NAME2
etc

I hope that clarifies things. Thanks again for you guys help!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-25 : 14:12:25

Are you looking for something like this?


use northwind
go

create table #temp
(
Employee varchar(15),
Supervisor tinyint null,
rowcnt [int] IDENTITY (1, 1) NOT NULL ,
)

insert into #temp
select top 10 Lastname, null from employees where reportsto is not null order by lastname
insert into #temp select top 5 LastName, 1 from employees where reportsto is null order by lastname

select 'emgroup' + cast(rowcnt as varchar(3)), Employee, case Supervisor when 1 then 'Supervisor' Else 'Employee' end
from #temp

drop table #temp

An infinite universe is the ultimate cartesian product.

Genesis
Starting Member

6 Posts

Posted - 2008-04-28 : 12:02:47

Actually I never thought of like that, yeah that would work, much thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources