How do I combine this data?

• Lee Zard

  SSC Veteran

  Points: 293

  More actions

  June 12, 2007 at 10:17 am

  #196284

  I have a table containing Meeting data and a table containing Review data.

  I need to determine which “FileReview” is the “MeetingComittee” a part of. (per FileNo)

   

  I can look at the data below and know that the “FileReview” = “First” should be associated with the “MeetingCommittee” = “CAS” because the 5/25/1993 MeetingDate is > the 5/5/1993 FileDate but < the next FileDate of 8/2/1994.

   

  Meetings

   

  MeetingFileNo	MeetingCommittee	MeetingDate
  S70039	CAS	5/25/1993
  S70039	CSS	8/3/1994
  S70039	CP	5/7/1999
  S70039	CSS	8/1/1999

   

   

   

   

   

   

  Reviews

  FileNo	FileReview	FileDate
  S70039	First	5/5/1993
  S70039	Second	8/2/1994
  S70039	Model	5/4/1999
  S70039	Begin	7/27/1999

   

   

  This is what I need to output

  FileNo	FileReview	MeetingCommittee	MeetingDate
  S70039	First	CAS	5/25/1993
  S70039	Second	CSS	8/3/1994
  S70039	Model	CP	5/7/1999
  S70039	Begin	CSS	8/1/1999

  Any ideas how to do this?

  Thanks!

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  June 12, 2007 at 1:31 pm

  #712388

  I'm sure there is a better way but the following works until I can optimize it:

  if object_id('Meetings_') is not null drop table Meetings_;

  if object_id('Reviews_') is not null drop table Reviews_;

  create table Meetings_ (

   MeetingFileNo_ char(6),

      MeetingCommittee_ char(3),

      MeetingDate_ datetime

  );

  Create table Reviews_ (

   FileNo_ char(6),

   FileReview_ char(10),

   FileDate_ datetime

  );

  insert into Meetings_

   select 'S70039','CAS','5/25/1993' union all

   select 'S70039','CSS','8/3/1994' union all

   select 'S70039','CP','5/7/1999' union all

   select 'S70039','CSS','8/1/1999';

  insert into Reviews_

   select 'S70039','First','5/5/1993' union all

   select 'S70039','Second','8/2/1994' union all

   select 'S70039','Model','5/4/1999' union all

   select 'S70039','Begin','7/27/1999';

  select r.fileno_, r.filereview_,m.meetingcommittee_,m.meetingdate_

     from meetings_ m join reviews_ r on (m.meetingfileno_ = r.fileno_ and m.meetingdate_ > r.filedate_ and

                                          m.meetingdate_ <= coalesce( (select min(r2.filedate_)

                                                              from reviews_ r2

                                                              where r2.filedate_ > r.filedate_ ),m.meetingdate_))

  --James

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  June 12, 2007 at 2:06 pm

  #712394

  The following also works with the example I provided above.  Not sure if it's any better but it does make use of a CTE to make it a little easier on the eyes and maybe easier to follow the logic

  with ReviewRange_(fileno_, filereview_, filedate_, nextdate_) as

  (

   select r1.fileno_,

             r1.filereview_,

       r1.filedate_ ,

       coalesce((select min(filedate_) from reviews_ r2 where r1.fileno_ = r2.fileno_ and r2.filedate_ > r1.filedate_),dateadd(year,1,r1.filedate_))

      from reviews_ r1

  )

  select rr.fileno_, rr.filereview_,m.meetingcommittee_,m.meetingdate_

     from reviewrange_ rr join meetings_ m on (m.meetingfileno_ = rr.fileno_ and m.meetingdate_ between rr.filedate_ and rr.nextdate_)