You must Sign In to post a response.
  • Category: SQL Server

    Using Row Number Query

    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
     ELSE '' END AS 
    ,
    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'

    Course Eligbility Startdt Enddt
    REO CHIEF ENGINEER 10 Aug 2015 21 Aug 2015
    REO CHIEF ENGINEER 24 Aug 2015 04 Sep 2015
    REO CHIEF ENGINEER 14 Dec 2015 25 Dec 2015


    Course Eligbility Startdt Enddt
    REO CHIE ENGINEER 10 Aug 2015 21 Aug 2015
    24 Aug 2015 04 Sep 2015
    14 Dec 2015 25 Dec 2015


    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
     ELSE '' END AS 
    ,
    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
  • #764681
    Only Row_Number is not enough to accomplish your task, I think you need to take support of PARTITION BY.
    see below snippet
    SELECT
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY Course, Eligbility ) = 1
    THEN Course ELSE NULL END AS 'Course'
    , Eligbility
    , Startdt, enddt
    FROM table1
    ORDER BY Course, Eligbility
    OR
    you can use 'COALESCE' to accomplish your task, check out below link
    http://www.rajapet.com/2010/04/suppressing-repeated-column-value-in.html

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments