subdivision on output query in server 2000

Question

subdivision on output query in server 2000

raghuldrag

Hall of Fame

Points: 3160
More actions
August 28, 2012 at 12:23 am

#265940

alter procedure Goals_history
(
@stuid nvarchar(22),
@startyear nvarchar(22),
@endyear nvarchar(22),
)
as
begin
select name,class,noofsubject,rank,test from @tab1
union
select name,class,subject,rank,test from @tab2
where rank is not null
and studentname=@stuid
and accodomicyear>=@startyear
and accodomicyear<=@endyear
end
OUTPUT:
class noofsubject rank test name
2 5 1 revision ravi
2 5 - monthly raju
2 5 3 midterm ravi
6 4 - monthly somu
2 5 - revision ravi
expectin output:(rank is null means show first)
class noofsubject rank test name
6 4 - monthly somu
2 5 - monthly raju
2 5 - revision-II ravi
2 5 1 revision ravi
2 5 3 midterm ravi
(if he have rank means the output comes on subdivision how to make the query)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

raghuldrag (8/28/2012)
alter procedure Goals_history
(
@stuid nvarchar(22),
@startyear nvarchar(22),
@endyear nvarchar(22),
)
as
begin
select name,class,noofsubject,rank,test from @tab1
union
select name,class,subject,rank,test from @tab2
where rank is not null
and studentname=@stuid
and accodomicyear>=@startyear
and accodomicyear<=@endyear
end
OUTPUT:
class noofsubject rank test name
2 5 1 revision ravi
2 5 - monthly raju
2 5 3 midterm ravi
6 4 - monthly somu
2 5 - revision ravi
expectin output:(rank is null means show first)
class noofsubject rank test name
6 4 - monthly somu
2 5 - monthly raju
2 5 - revision-II ravi
2 5 1 revision ravi
2 5 3 midterm ravi
(if he have rank means the output comes on subdivision how to make the query)

Hi raghuldrag,

Please provide the DDL of tables with sample data.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

raghuldrag Hall of Fame Points: 3160 More actions · Answer 2

create table tab1(name varchar,class int(22),noofsubject numeric(12),rank int(10),test varchar(22),stuid int(11),accodomicyear nvarchar(22))

create table tab2(name varchar,class int(22),noofsubject numeric(12),rank int(10),test varchar(22)),stuid int(11),accodomicyear nvarchar(22))

insert into tab1 values(' ravi',2 ,5 ,' ' ,'revision-II',002244,2011)

insert into tab2 values('ravi',2 ,5 ,' 1' ,'revision',002244,2011)

expecting output:

class noofsubject rank test name

2 5 - revision-II ravi

(sub division)2 5 1 revision ravi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

raghuldrag (8/28/2012)
alter procedure Goals_history
...

What version of SQL Server is your database?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

ChrisM@Work (8/28/2012)
raghuldrag (8/28/2012)
alter procedure Goals_history
...
What version of SQL Server is your database?

OP answered in a PM, he's using SQL Server 2000.

Raghuldrag, please post in the SQL Server 7, 2000 forum section. Posting in the SS2K8 section wastes people's time as the solutions may be different and incompatible with your antique version.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden