CODEWITHSUNDEEP
sqlsql-server
user3614477
user3614477

Reputation: 25

Fetch Date based on another date

Table 1:

Temp    ResID     Code      Date
11       1         SPR       
12       1         SPG       2009-10-05
13       1         SPR 
14       1         SPG       2011-10-08

Table 2:

TempID    Res ID     InDate       Out Date
21         1.         2009-10-05    2010-11-15
22         1.         2011-10-08    2011-11-09

Table 3: (Desired Result)

ResID     Code      Date
1         SPR       2010-11-15
1         SPG       2009-10-05
1         SPR       2011-11-09 
1         SPG       2011-10-08

I have two tables as above.

I need to update the Table 1 Date column for every ID for every row where the code is SPR. The SPG value in table 1 date column is equal to InDate column value for the same resident

Please advice with the query. How do I achieve this with a query joining the two tables, table 1 and table 2 to get table 3

Upvotes: 1

Views: 58

Answers (3)

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

If table2 has value for every table one then you can just use table2, kind of weird but you can always join with the main table to get appropriate result if you want to

select Convert(varchar, indate, 102) as date, 'SPG' as code, resid from table2
union all
select convert(varchar, outdate, 102) as date, 'SPR' as code, resid from table2

SQL Fiddle

Upvotes: 0

Josh Jay
Josh Jay

Reputation: 1250

Edited from a select statement to an Update

I think this will work for you. See the SqlFiddle

Update
  Table1
set
  Table1.[Date] = Table2.OutDate
from
  Table1
  inner join
  (
    select
      Temp,
      ResID,
      Code,
      Date,
      row_number() over(partition by ResID order by Temp) as RowId
    from
      Table1
    where
      Code = 'SPR'
  ) as SPR on Table1.Temp = SPR.Temp
  inner join
  (
    select
      ResID,
      Code,
      Date,
      row_number() over(partition by ResID order by Temp) as RowId
    from
      Table1
    where
      Code = 'SPG'
   ) as SPG on SPG.RowId = SPR.RowId and SPG.ResID = SPR.ResID
  inner join
  Table2 on Table2.InDate = SPG.Date and Table2.ResID = SPG.ResID

Upvotes: 1

Andomar
Andomar

Reputation: 238296

You could delete all the SPR rows and then generate them:

delete  Table1
where   code = 'SPR';

insert  Table1
        (ResID, Code, [Date])
select  t1.ResID
,       'SPR'
,       OutDate
from    Table1 t1
join    Table2 t2
on      t1.ResID = t2.ResID
        and t1.[Date] = t2.InDate
where   t1.Code = 'SPG';

Example at SQL Fiddle.

Upvotes: 1

Related Questions