Hi,
I am not an expert in SQL.I have a table where data entered in a wrong patter.I have to exclude some records( i can't delete) i am trying to create a view.
Tried with sub queries but not worked for me
here is my data
stid Ayear Aterm Award Details
43 2014 fall
43 2015 fall HONOR Honor List
43 2015 spring good
43 2016 spring good
43 2016 spring HONOR Honor List
45 2014 fall
45 2015 fall PRESIDENT PresidentList
45 2015 spring good
45 2016 spring good
45 2016 spring HONOR Honor List
this is my data.i want to exclude the row with details 'Good' when there is Honor list in the same year and term
for Student 45 in 2016 spring we have good and honorlist
in this case we need to remove the record with 'good'
and keep the honorlist as such
This is the table structure
CREATE TABLE [dbo].[testawards](
[stid] [int] NULL,
[Ayear] [int] NULL,
[Aterm] [nchar](10) NULL,
[Award] [nvarchar](50) NULL,
[Details] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Sample data
insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2014,'fall','','')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2015,'fall','HONOR','Honor List')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2015,'spring','','good')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2016,'spring','','good')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2016,'spring','HONOR','Honor List')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2014,'fall','','')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2015,'fall','PRESIDENT','PresidentList')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2015,'spring','','good')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2016,'spring','','good')
insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2016,'spring','HONOR','Honor List')
What I have tried:
I tried with Subqueries but not worked .
just took the count of records with same term and year then if there is Honor ,but this is not worked for me