Query Challenge of the day - 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)

Query Challenge of the day

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-16 : 09:47:08

create table managers (
fullname varchar (100) ,
area varchar (100) ,
role varchar (100)
)

Sample table data:

Bob jones, Maryland, District Manager
Ringo Starr , Maryland, Manager
Joan Jet, Maryland, Manager
Michael Jackson, Florida, District Manager
Dixie Chick, Florida, Manager
Sally Field, Florida, Manager

I need a query that will list the rows in order of state with the 'District Manager' appearing first

SELECT Fullname, Area, Role
FROM MyTable
ORDER BY Area, Role, Fullname

BUT - I need a row that appears before every new state (and is a leading row) that reads:
'Name', 'Organization', 'Role'

AND - just to make it a little more complicated - if there are no 'Managers' under a 'District Manager', I need to insert a single row under the 'District Manager' that reads:

'information', 'not', 'available'

(or 'information not available', '', '') but I think it'll fit better the other way.

Solve this and win a

Sam

Edited by - SamC on 03/16/2003 09:49:15

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-16 : 10:23:10

Why aren't doing this on the reporting side? It's a reporting function, not a query function.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-16 : 10:31:10

But for a beer.

select fullname, area, role
from
(
select *, st2 = area, srt1 = 1, srt2 = case when role = 'District Manager' then 0 else 1 end
from #managers
union all
select distinct 'Name', 'Organization', 'Role', Area, 0, 0
from #managers
union all
select distinct 'information', 'not', 'available', Area, 1,1
from #managers m where not exists (select * from #managers m2 where m.area = m2.area and m2.role = 'Manager')
) as a
order by st2, srt1, srt2, Role, Fullname

create table #managers (
fullname varchar (100) ,
area varchar (100) ,
role varchar (100)
)

insert #managers
select 'Bob jones', 'Maryland', 'District Manager'
insert #managers
select 'Ringo Starr' , 'Maryland', 'Manager'
insert #managers
select 'Joan Jet', 'Maryland', 'Manager'
insert #managers
select 'Michael Jackson', 'Florida', 'District Manager'
insert #managers
select 'Dixie Chick', 'Florida', 'Manager'
insert #managers
select 'Sally Field', 'Florida', 'Manager'
insert #managers
select 'me', 'UK', 'District Manager'

gives
fullname area role
-------------------- -------------------- --------------------
Name Organization Role
Michael Jackson Florida District Manager
Dixie Chick Florida Manager
Sally Field Florida Manager
Name Organization Role
Bob jones Maryland District Manager
Joan Jet Maryland Manager
Ringo Starr Maryland Manager
Name Organization Role
me UK District Manager
information not available

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/16/2003 10:33:23

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-16 : 10:38:32

This is done on the reporting side today. I may be misguided in my zeal to 'put it all in a proceedure', but I've eliminated lots of code by building the right procedure and filling a simple ASP or .NET datagrid with the results. The overall coding improvement has given me a lot of momentum to keep up this method where reasonable. I was hoping to knock this one out the same way. Maybe I've hit a wall.

If I've overstepped the bounds too much on this one I'll retract my beer offer.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-16 : 10:43:48

Well, by Rob's account, it not be the right way to go, but Nigel won the beer before the offer was retracted.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-16 : 10:59:52

Hey! No problems, if it works, it works.

As long as you keep your

promises unlike some ~~people~~ children who've posted on SQL Team in the past.

Edited by - robvolk on 03/16/2003 11:07:25

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-16 : 11:19:33

quote:

Hey! No problems, if it works, it works. As long as you keep your promises unlike some ~~people~~ children who've posted on SQL Team in the past.

Edited by - robvolk on 03/16/2003 11:07:25

he he he he he !

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-16 : 11:41:46

Nigel earned the beer, but delivery is an issue. Not to mention, warm flat beer is scarce in the US.

Did I omit the fine print that the winner must visit Metro DC area?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-16 : 11:51:23

Careful.
I might still take you up on it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources