Data that exists between two years listed in a table

Question

Data that exists between two years listed in a table

jkw

SSC Eights!

Points: 815
More actions
February 16, 2016 at 11:27 am

#324757

I've got two tables and need to obtain the data in the Records table based on the information in the RecordsNeeded table. For example:
Member 123 has records in the Records table between 2004 and 2015, but I only need the records that are defined in the RecordsNeeded table which would be Member 123 FromYear = 2013 and ToYear = 2015
Member 456 has records in the Records table between 2001 and 2015, but I only need the records that are defined in the RecordsNeeded table which would be Member 456 FromYear = 2008 and ToYear = 2015
CREATE TABLE [dbo].[Records](
[MemberID] VARCHAR(12),
[Year] SMALLINT,
[Field1] [VARCHAR](50) NULL,
[Field2] [VARCHAR](10) NULL,
[Field3] [VARCHAR](10) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[RecordsNeeded](
[MemberID] VARCHAR(12),
[FromYear] SMALLINT,
[ToYear] SMALLINT
) ON [PRIMARY]
I hope this makes sense, thanks..

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

jon.wilson (2/16/2016)
I've got two tables and need to obtain the data in the Records table based on the information in the RecordsNeeded table. For example:
Member 123 has records in the Records table between 2004 and 2015, but I only need the records that are defined in the RecordsNeeded table which would be Member 123 FromYear = 2013 and ToYear = 2015
Member 456 has records in the Records table between 2001 and 2015, but I only need the records that are defined in the RecordsNeeded table which would be Member 456 FromYear = 2008 and ToYear = 2015
CREATE TABLE [dbo].[Records](
[MemberID] VARCHAR(12),
[Year] SMALLINT,
[Field1] [VARCHAR](50) NULL,
[Field2] [VARCHAR](10) NULL,
[Field3] [VARCHAR](10) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[RecordsNeeded](
[MemberID] VARCHAR(12),
[FromYear] SMALLINT,
[ToYear] SMALLINT
) ON [PRIMARY]
I hope this makes sense, thanks..

This should give you a starting place:

SELECT R.MemberID

FROM Records R

INNER JOIN RecordsNeeded RN ON R.MemberID = RN.MemberID

AND R.Year BETWEEN RN.FromYear TO RN.ToYear