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)
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] '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
What I have tried:
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)
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] '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