select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd' and
b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and '2015-12-30 00:00:00.000'
order by b.cbm_batch_start_dt asc
When i run the above query output as follows
Startdt Enddt Course
10 Aug 2015 21 Aug 2015 REO
24 Aug 2015 04 Sep 2015 REO
14 Dec 2015 25 Dec 2015 REO
But i want excepted output as follows
REO
10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015
for getting above excepted output i written the query as follows
declare @t table(id int,startdt varchar(50),enddt varchar(50),course varchar(max))
Insert into @t
select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd'
and a.Active <> 'd' and b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and
'2015-12-30 00:00:00.000' order by b.cbm_batch_start_dt asc
SELECT [REO],[REO]
FROM @t
PIVOT (MAX(startdt) FOR [course] IN ([REO])) AS Pvt
But when i run the above query ouput i get as follows
REO
24 Aug 2015 24 Aug 2015
10 Aug 2015 10 Aug 2015
14 Dec 2015 14 Dec 2015
in the above output both date are same.
please help me what is the mistake i made in the above pivot query
What I have tried:
select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd' and
b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and '2015-12-30 00:00:00.000'
order by b.cbm_batch_start_dt asc
When i run the above query output as follows
Startdt Enddt Course
10 Aug 2015 21 Aug 2015 REO
24 Aug 2015 04 Sep 2015 REO
14 Dec 2015 25 Dec 2015 REO
But i want excepted output as follows
REO
10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015
for getting above excepted output i written the query as follows
declare @t table(id int,startdt varchar(50),enddt varchar(50),course varchar(max))
Insert into @t
select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd'
and a.Active <> 'd' and b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and
'2015-12-30 00:00:00.000' order by b.cbm_batch_start_dt asc
SELECT [REO],[REO]
FROM @t
PIVOT (MAX(startdt) FOR [course] IN ([REO])) AS Pvt
But when i run the above query ouput i get as follows
REO
24 Aug 2015 24 Aug 2015
10 Aug 2015 10 Aug 2015
14 Dec 2015 14 Dec 2015
in the above output both date are same.
please help me what is the mistake i made in the above pivot query
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd' and
b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and '2015-12-30 00:00:00.000'
order by b.cbm_batch_start_dt asc
When i run the above query output as follows
Startdt Enddt Course
10 Aug 2015 21 Aug 2015 REO
24 Aug 2015 04 Sep 2015 REO
14 Dec 2015 25 Dec 2015 REO
But i want excepted output as follows
REO
10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015
for getting above excepted output i written the query as follows
declare @t table(id int,startdt varchar(50),enddt varchar(50),course varchar(max))
Insert into @t
select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd'
and a.Active <> 'd' and b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and
'2015-12-30 00:00:00.000' order by b.cbm_batch_start_dt asc
SELECT [REO],[REO]
FROM @t
PIVOT (MAX(startdt) FOR [course] IN ([REO])) AS Pvt
But when i run the above query ouput i get as follows
REO
24 Aug 2015 24 Aug 2015
10 Aug 2015 10 Aug 2015
14 Dec 2015 14 Dec 2015
in the above output both date are same.
please help me what is the mistake i made in the above pivot query
What I have tried:
select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd' and
b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and '2015-12-30 00:00:00.000'
order by b.cbm_batch_start_dt asc
When i run the above query output as follows
Startdt Enddt Course
10 Aug 2015 21 Aug 2015 REO
24 Aug 2015 04 Sep 2015 REO
14 Dec 2015 25 Dec 2015 REO
But i want excepted output as follows
REO
10 Aug 2015 21 Aug 2015
24 Aug 2015 04 Sep 2015
14 Dec 2015 25 Dec 2015
for getting above excepted output i written the query as follows
declare @t table(id int,startdt varchar(50),enddt varchar(50),course varchar(max))
Insert into @t
select 1, convert(char(12),b.cbm_batch_start_dt,106) as Startdt,
convert(char(12),b.cbm_batch_end_dt,106) as Enddt,a.course as Course from tb_rankwise_coursesettings as a,CO_BATCH_MASTER as b
where a.course = b.cmn_minor_code and a.Rank = 'CHENGR' and b.cbm_active <> 'd'
and a.Active <> 'd' and b.cbm_batch_start_dt between '2015-08-01 00:00:00.000' and
'2015-12-30 00:00:00.000' order by b.cbm_batch_start_dt asc
SELECT [REO],[REO]
FROM @t
PIVOT (MAX(startdt) FOR [course] IN ([REO])) AS Pvt
But when i run the above query ouput i get as follows
REO
24 Aug 2015 24 Aug 2015
10 Aug 2015 10 Aug 2015
14 Dec 2015 14 Dec 2015
in the above output both date are same.
please help me what is the mistake i made in the above pivot query
Riddhi ValechaPosted Feb 21, 2016, 4:23 AM