SHARE
TWEET

Untitled

a guest Jun 15th, 2019 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Symbol  Exchange    Date        Year
  2. FLWS    NAS         2013-05-01  2015
  3. FLWS    NAS         2013-05-01  2014
  4. FLWS    NAS         2013-05-01  2013
  5. FCCY    NAS         2013-05-01  2014
  6. FCCY    NAS         2013-05-01  2013
  7. SRCE    NAS         2013-05-01  2014
  8. SRCE    NAS         2013-05-01  2013
  9. SRCE    NAS         2013-05-01  2012
  10. FNHC    NAS         2013-05-01  2014
  11. FNHC    NAS         2013-05-01  2013
  12. DDD     NYS         2013-05-01  2015
  13. DDD     NYS         2013-05-01  2014
  14. DDD     NYS         2013-05-01  2013
  15. DDD     NYS         2013-05-01  2012
  16. MMM     NYS         2013-05-01  2015
  17. MMM     NYS         2013-05-01  2014
  18. MMM     NYS         2013-05-01  2013
  19. MMM     NYS         2013-05-01  2012
  20. JOBS    NAS         2013-05-01  2014
  21. JOBS    NAS         2013-05-01  2013
  22.      
  23. Declare @Table table (Symbol varchar(25),Exchange varchar(25),Date Date,Year int)
  24. Insert Into @Table (Symbol,Exchange,Date,Year) values
  25. ('FLWS','NAS','2013-05-01',2015),
  26. ('FLWS','NAS','2013-05-01',2014),
  27. ('FLWS','NAS','2013-05-01',2013),
  28. ('FCCY','NAS','2013-05-01',2014),
  29. ('FCCY','NAS','2013-05-01',2013),
  30. ('SRCE','NAS','2013-05-01',2014),
  31. ('SRCE','NAS','2013-05-01',2013),
  32. ('SRCE','NAS','2013-05-01',2012),
  33. ('FNHC','NAS','2013-05-01',2014),
  34. ('FNHC','NAS','2013-05-01',2013),
  35. ('DDD','NYS','2013-05-01',2015),
  36. ('DDD','NYS','2013-05-01',2014),
  37. ('DDD','NYS','2013-05-01',2013),
  38. ('DDD','NYS','2013-05-01',2012),
  39. ('MMM','NYS','2013-05-01',2015),
  40. ('MMM','NYS','2013-05-01',2014),
  41. ('MMM','NYS','2013-05-01',2013),
  42. ('MMM','NYS','2013-05-01',2012),
  43. ('JOBS','NAS','2013-05-01',2014),
  44. ('JOBS','NAS','2013-05-01',2013)
  45.  
  46. Select A.*
  47.  From @Table A
  48.  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)
  49.  Order By Symbol,Year
  50.      
  51. Symbol  Exchange    Date        Year
  52. FLWS    NAS         2013-05-01  2013
  53. FLWS    NAS         2013-05-01  2014
  54. FLWS    NAS         2013-05-01  2015
  55.      
  56. select * from table
  57. where symbol in ( select Symbol from table
  58. where year in (2013, 2014, 2015)
  59. group by symbol
  60. having count (distinct year) = 3)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top