TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
narasiman rao
NA
519
768.5k
Row number query
Feb 18 2016 12:23 AM
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Rankwisecourselist](@Rank varchar(50),@FromDate datetime,@ToDate datetime)
as
declare @SNo int,
@Course varchar(100),
@Code varchar(50),
@Descr varchar(20),
@Eligbility varchar(20),
@Days varchar(20),
@Startdt datetime,
@Enddt datetime
create table #TempTable(SNo int, Course varchar(10),Code varchar(100),
Descr varchar(20),Eligbility varchar(20),Days varchar(20),Startdt datetime,Enddt datetime)
begin tran
declare batchwise cursor FOR
select b.cmn_minor_code as Course,f.cmj_major_desc as Code,c.cmn_minor_desc as Description,e.eligibility as Eligbility,c.cmn_minor_day as Days,convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b,CO_MINOR_MASTER as c, Eligibility e,CO_MAJOR_MASTER as f
where a.course = b.cmn_minor_code and a.Rank =@rank and b.cbm_active <> 'd'
and b.cmn_minor_code = c.cmn_minor_code and e.Minorcode = b.cmn_minor_code and f.cmj_major_code = b.cmj_major_code
and a.Active <> 'd' and b.cbm_batch_start_dt between @FromDate and @ToDate
order by b.cmn_minor_code asc
SET @SNo = 0
open Batchwise
fetch next from Batchwise into @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt
While @@Fetch_status = 0
begin
SET @SNo = @SNo + 1
insert into #TempTable values(@SNo, @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt) --added
fetch next from Batchwise into @Course,@Code,@Descr,@Eligbility,@Days,@Startdt,@Enddt
END
commit tran
close Batchwise
deallocate Batchwise
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno,
CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), Course) ELSE '' END AS Course,
CASE WHEN RowNo =1 THEN [Code] ELSE '' END AS [Code],
CASE WHEN RowNo =1 THEN [Descr] ELSE '' END AS [Descr],
CASE WHEN RowNo =1 THEN [Eligbility] ELSE '' END AS [Eligbility],
[Startdt], [Enddt],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
When i execute the stored procedure output as follows
exec [Rankwisecourselist] 'choff', '2013-08-01 00:00:00.000' , '2014-12-30 00:00:00.000'
SNO Course Code Descr Eligbility Startdt Enddt Days
1 ASM CC Master Yes 01 May 2014 14 Jun 2014 5
2 ASM CC Master Yes 01 Nov 2013 16 Dec 2013 5
3 ASM CC Master Yes 01 Dec 2013 16 Dec 2013 5
4 SMS SC Officer Yes 16 Jun 2014 20 Jun 2014 10
5 SMS SC Officer Yes 21 Dec 2014 29 Dec 2014 10
6 SMS SC Officer Yes 16 Dec 2014 20 Dec 2014 10
i tried the row no concept but i want output as follows
SNO Course Code Descr Eligbility Startdt Enddt Days
1 ASM CC Master Yes 01 May 2014 14 Jun 2014 5
01 Nov 2013 16 Dec 2013 5
01 Dec 2013 16 Dec 2013 5
2 SMS SC Officer Yes 16 Jun 2014 20 Jun 2014 10
21 Dec 2014 29 Dec 2014 10
16 Dec 2014 20 Dec 2014 10
for getting a above output i tried the row no concept as follows
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno,
CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), Course) ELSE '' END AS Course,
CASE WHEN RowNo =1 THEN [Code] ELSE '' END AS [Code],
CASE WHEN RowNo =1 THEN [Descr] ELSE '' END AS [Descr],
CASE WHEN RowNo =1 THEN [Eligbility] ELSE '' END AS [Eligbility],
[Startdt], [Enddt],
CASE WHEN RowNo =1 THEN [Days] ELSE '' END AS [Days]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
but output is not coming correctly
Reply
Answers (
1
)
Make SQL querys on view faster on c# and SQL server 2014
display 3 digits before decimal and after decimal point 6dig