Brian D Brian D - 4 months ago 8
SQL Question

Extracting subset of data with inner join to itself

I have data in the following format and want to extract the records that have year = 2013 and 2014 and 2015 for a Symbol. (ie those 3 years, not 2 of, not 4)

The first one below (FLWS) fits but the next one (FCCY) only has 2013 and 2014 but not 15 so i don't want it in the end set. Further down DDD and MMM have 2012 to 2105 and I just want the 2013-15 data from them.

I can't figure out the inner join needed to extract the sets of 3 records for 2013, 14 and 15. Any help appreciated
Thanks

Symbol Exchange Date Year
FLWS NAS 2013-05-01 2015
FLWS NAS 2013-05-01 2014
FLWS NAS 2013-05-01 2013
FCCY NAS 2013-05-01 2014
FCCY NAS 2013-05-01 2013
SRCE NAS 2013-05-01 2014
SRCE NAS 2013-05-01 2013
SRCE NAS 2013-05-01 2012
FNHC NAS 2013-05-01 2014
FNHC NAS 2013-05-01 2013
DDD NYS 2013-05-01 2015
DDD NYS 2013-05-01 2014
DDD NYS 2013-05-01 2013
DDD NYS 2013-05-01 2012
MMM NYS 2013-05-01 2015
MMM NYS 2013-05-01 2014
MMM NYS 2013-05-01 2013
MMM NYS 2013-05-01 2012
JOBS NAS 2013-05-01 2014
JOBS NAS 2013-05-01 2013

Answer

I think I understood your question

Declare @Table table (Symbol varchar(25),Exchange varchar(25),Date Date,Year int)
Insert Into @Table (Symbol,Exchange,Date,Year) values
('FLWS','NAS','2013-05-01',2015),
('FLWS','NAS','2013-05-01',2014),
('FLWS','NAS','2013-05-01',2013),
('FCCY','NAS','2013-05-01',2014),
('FCCY','NAS','2013-05-01',2013),
('SRCE','NAS','2013-05-01',2014),
('SRCE','NAS','2013-05-01',2013),
('SRCE','NAS','2013-05-01',2012),
('FNHC','NAS','2013-05-01',2014),
('FNHC','NAS','2013-05-01',2013),
('DDD','NYS','2013-05-01',2015),
('DDD','NYS','2013-05-01',2014),
('DDD','NYS','2013-05-01',2013),
('DDD','NYS','2013-05-01',2012),
('MMM','NYS','2013-05-01',2015),
('MMM','NYS','2013-05-01',2014),
('MMM','NYS','2013-05-01',2013),
('MMM','NYS','2013-05-01',2012),
('JOBS','NAS','2013-05-01',2014),
('JOBS','NAS','2013-05-01',2013)

Select A.* 
 From @Table A
 Join (Select Symbol,Hits=count(Distinct Year),MinYear=min(Year) From @Table Group By Symbol) B on (A.Symbol=B.Symbol and B.Hits=3 and B.MinYear=2013)
 Order By Symbol,Year

Returns

Symbol  Exchange    Date        Year
FLWS    NAS         2013-05-01  2013
FLWS    NAS         2013-05-01  2014
FLWS    NAS         2013-05-01  2015