Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

subdivision on output query in server 2000 Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 12:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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 outputrank 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)
Post #1350700
Posted Tuesday, August 28, 2012 1:09 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
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 outputrank 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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1350709
Posted Tuesday, August 28, 2012 1:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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
Post #1350710
Posted Tuesday, August 28, 2012 3:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1350775
Posted Tuesday, August 28, 2012 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1350777
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse