Getting value from C and A table with a max date in A table A value

• pcq0125

  SSCrazy

  Points: 2890

  More actions

  July 13, 2006 at 1:56 pm

  #366781

  I have 3 tables named RBL, s_RAB, RAB that I need to get the s_RABID.TAGID and RAB.TAGID values that has the MAX s_RAB.ChangedDate that is smaller than the RBL.ChangedDate and  s_RABID.TAGID <> RAB.TAGID. Below is my example. Please advise. Thanks. s_RAB Table ----------- RABID TAGID ChangedDate 1111 0011 01/01/06 1111 0033 01/02/06 --> I need to get this value 1111 0066 01/04/06 RAB Table ----------- RABID TAGID ChangedDate 1111 0044 01/03/06 --> and this value 1112 0033 01/05/06 RBL Table ----------- RABID ChangedDate 1111 01/03/06 1112 01/05/06 Below is my script and I am not getting any value back. SELECT s_RAB.RABID, RBL.ItemType, s_RAB.ChangedDate, s_RAB.TAGID, RAB.TAGID FROM RBL, s_RAB, RAB WHERE s_RAB.RABID = RBL. RABID AND s_RAB.RABID = RAB.RABID AND s_RAB.ChangedDate IN (SELECT MAX(s_RAB.ChangedDate) FROM s_RAB WHERE s_RAB.RABID = RBL.RABID) AND s_RAB.ChangedDate < RBL.BALChangedDate AND s_RAB.TAGID <> RAB.TAGID AND RBL.ItemType ='R'

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 13, 2006 at 2:34 pm

  #649098

  You are missing the ItemType in your table definitions. Also you have renamed changeddate to balchangeddate in the query.

  With the expected sample data above, what is your complete expected output?

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Edwin Frydendall

  SSCrazy

  Points: 2361

  More actions

  July 13, 2006 at 2:42 pm

  #649099

  pcq0125:

  Select

     srab1.TAGID,

     rab.TAGID

  From

     s_RAB srab1

     Inner Join RAB rab

       on rab.RABID = srab1.RABID

  Where

     srab1.ChangedDate =

        (

         Select

            Max(srab2.ChangedDate)

         From

            s_RAB srab2

            Inner Join RBL rbl

              on rbl.RABID = srab2.RABID

         Where

            rbl.ItemType = 'R'

            and

            srab2.ChangedDate < rbl.ChangedDate

        )

     and

     srab1.TAGID <> rab.TAGID

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 13, 2006 at 3:09 pm

  #649103

  Another alternative:

  create table dbo.RBL (

      RABID       int,

      ChangedDate datetime,

      ItemType    char(1)

  )

  create table dbo.RAB (

      RABID       int,

      TAGID       char(4),

      ChangedDate datetime

  )

  create table dbo.s_RAB (

      RABID       int,

      TAGID       char(4),

      ChangedDate datetime

  )

  insert into dbo.RBL values (1111, '2006-01-03', 'R')

  insert into dbo.RBL values (1112, '2006-01-05', 'R')

  insert into dbo.RAB values (1111, '0044', '2006-01-03')

  insert into dbo.RAB values (1112, '0033', '2006-01-05')

  insert into dbo.s_RAB values (1111, '0011', '2006-01-01')

  insert into dbo.s_RAB values (1111, '0033', '2006-01-02')

  insert into dbo.s_RAB values (1111, '0044', '2006-01-06')

  select

      rbl.ItemType,

      srab.RABID,

      srab.ChangedDate,

      srab.TAGID,

      rab.TAGID,

      (select max(rab1.ChangedDate) from dbo.RAB rab1 where rab1.RABID = rab.RABID) as Test

  from

      dbo.RBL rbl

      inner join dbo.RAB rab

          on (rbl.RABID = rab.RABID)

      inner join dbo.s_RAB srab

          on (rbl.RABID = srab.RABID)

  where

      rbl.ItemType = 'R'

      and srab.ChangedDate = (select

                                  max(srab1.ChangedDate)

                              from

                                  dbo.s_RAB srab1

                              where

                                  srab1.RABID = rab.RABID

                                  and srab1.ChangedDate <= rab.ChangedDate)

      and srab.TAGID <> rab.TAGID

  drop table dbo.RBL

  drop table dbo.RAB

  drop table dbo.s_RAB