narasiman rao

NA
519
771.8k

Row number query

Feb 18 2016 12:23 AM

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER proc [dbo].[Rankwisecourselist](@Rank varchar(50),@FromDate datetime,@ToDate datetime)

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