How can i get records from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How can i get records from table

Author

Topic

muzaffar_ali99
Starting Member

33 Posts

Posted - 2010-08-11 : 03:53:51

Hi
I have a table @tbl1 that has years and grades
Year Grade
2007 Grade1
2007 Grade 2

2008 Grade 1
2008 Grade 2
2008 Grade 3

2009 Grade 1
2009 Grade 2
2009 Grade 4

Now my problem is i need to know the distinct grades that were missing in previous year and introduced in new years.
Like in 2007 grade 3 and grade 4 was not introduced.
My resultant query result would look like

2008 Grade 3
2009 Grade 4
And then i need to insert the missing grades in previous years like in 2007 grade 3 and grade 4 would be inserted and in 2008 grade 4 would be inserted.
Any help would appreciated.

Thanks

Muzaffar Ali

Sachin.Nand

2937 Posts

Posted - 2010-08-11 : 05:08:23

What happens if year 2009 has grade 5?


declare @tbl as table(yr int,grade varchar(30))
insert into @tbl
select 2007,'Grade 1' union all
select 2007,'Grade 2'union all
select 2008 ,'Grade 1' union all
select 2008 ,'Grade 2' union all
select 2008 ,'Grade 3' union all
select 2009 ,'Grade 1'union all
select 2009 ,'Grade 2'union all
select 2009 ,'Grade 4'
--insert into @tbl

select * from @tbl

;with cte
as
(
select t1yr ,t1grade from
(
select t1.yr as t1yr,t2.yr as t2yr,t1.grade as t1grade,t2.grade as t2grade from @tbl t1
left join @tbl t2 on t1.yr=t2.yr+1 and t1.grade=t2.grade
)t where t1yr not in(select top 1 yr from @tbl order by yr)
and t2yr is null)

insert into @tbl
select distinct yr,t1grade
 from @tbl cross apply(select top 100 percent * from cte where t1grade<>grade and t1yr>=yr+1 order by yr)t
 
 select * from @tbl order by yr,grade

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

muzaffar_ali99
Starting Member

33 Posts

Posted - 2010-08-11 : 06:30:49

Thanks for the quick reply.
If 2009 has grade 5 that will be added in 2008 and 2007 too and any other year if in data.

also i found some problems in the solution you provided if you can fix please.
declare @tbl as table(yr int,grade varchar(30))
insert into @tbl
select 2007,'Grade 1' union all
select 2007,'Grade 2'union all
select 2007 ,'Grade 5' union all
select 2008 ,'Grade 1' union all
select 2008 ,'Grade 2' union all
select 2008 ,'Grade 3' union all
select 2009 ,'Grade 1'union all
select 2009 ,'Grade 2'union all
select 2009 ,'Grade 4' union all
select 2009 ,'Grade 5'
--insert into @tbl
--Select * from @tbl
--select * from @tbl

;with cte
as
(
select t1yr ,t1grade from
(
select t1.yr as t1yr,t2.yr as t2yr,t1.grade as t1grade,t2.grade as t2grade
from @tbl t1 left join @tbl t2 on t1.yr=t2.yr+1 and t1.grade=t2.grade
)t where t1yr not in(select top 1 yr from @tbl order by yr)
and t2yr is null
)

insert into @tbl
select distinct yr,t1grade
from @tbl cross apply(select top 100 percent * from cte where t1grade<>grade and t1yr>=yr+1 order by yr)t

select * from @tbl order by yr,grade

Try running this script it has Grade 5 added 2 times in 2007 that should not be happening.
In simple words any year that has missing grade of other years will be added to that year.

Thanks
Muzaffar Ali

Sachin.Nand

2937 Posts

Posted - 2010-08-11 : 06:36:08

quote:
Originally posted by muzaffar_ali99

In simple words any year that has missing grade of other years will be added to that year.

Thanks
Muzaffar Ali

Then why Grade 3 should not get added for year 2009 with the underlying sample data???


declare @tbl as table(yr int,grade varchar(30))
insert into @tbl
select 2007,'Grade 1' union all
select 2007,'Grade 2'union all
select 2008 ,'Grade 1' union all
select 2008 ,'Grade 2' union all
select 2008 ,'Grade 3' union all
select 2009 ,'Grade 1'union all
select 2009 ,'Grade 2'union all
select 2009 ,'Grade 4'
select * from @tbl

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

muzaffar_ali99
Starting Member

33 Posts

Posted - 2010-08-11 : 07:17:49

It should get added in the end result if it does not exist in 2009 if exists then ok.

Thanks

PavanKK
Starting Member

32 Posts

Posted - 2010-08-11 : 07:51:21

try this

---------

DECLARE @Tbl TABLE (Year INT,Grade VARCHAR(10))

INSERT INTO @Tbl
select 2007,'Grade 1' union all
select 2007,'Grade 2'union all
select 2007 ,'Grade 5' union all
select 2008 ,'Grade 1' union all
select 2008 ,'Grade 2' union all
select 2008 ,'Grade 3' union all
select 2009 ,'Grade 1'union all
select 2009 ,'Grade 2'union all
select 2009 ,'Grade 4' union all
select 2009 ,'Grade 5'

DECLARE @Min INT,@Max INT
SELECT @Min = MIN(Year),@Max = MAX(Year) FROM @Tbl

;WITH cte AS
(
SELECT DISTINCT @Min Year,Grade
FROM @Tbl

UNION ALL

SELECT Year+1,Grade
FROM cte
WHERE Year+1<=@Max
)

SELECT C.Year,C.Grade
FROM cte C
LEFT JOIN @Tbl T ON C.Year = T.Year AND C.Grade = T.Grade
WHERE T.Year IS NULL

----------------

KK

muzaffar_ali99
Starting Member

33 Posts

Posted - 2010-08-11 : 08:58:21

Thank you so much that really helped solve my problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources