Row number query

Question

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