1
Reply

Row number query

narasiman rao

narasiman rao

Feb 18 2016 12:23 AM
469
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 

Answers (1)