0

I have 2 tables with values stored as below.

Table1

ReferranceID   StatusNumber   ServiceType            T2OpenDt          T1OpenDT
162987          399519        Orthopaedic Surgery       NULL           2011-08-19 
162987          399525        Acupuncture               NULL           2011-08-19 
162987          413405        Anesthesiology            NULL           2011-09-28 
162987          517174        Chiropractic              NULL           2012-04-26

Table2

ReferranceID StatusNumber   Status     T2OpenDate
162987       256033         Closed     2010-11-17 
162987       488518         ReOpen     2012-02-22

The first table should be updated as below from the 2nd table. (i.e the Result values)

ReferranceID   StatusNumber   ServiceType               T2OpenDt          T1OpenDT
162987          399519        Orthopaedic Surgery       2010-11-17         2011-08-19 
162987          399525        Acupuncture               2010-11-17         2011-08-19 
162987          413405        Anesthesiology            2010-11-17         2011-09-28 
162987          517174        Chiropractic              2012-02-22         2012-04-26

'2010-11-17' will be updated in 3 rows since the T2OpenDate is less than T1Opendate and there is only one occurance of 2012-02-22 since this date is slightly greater than other 3 top T1OpenDate and less than the 4th T1OpenDate.

Could anybody suggest me the UPDATE sqlquery for the above. Thank you so very much for helping me.

desi
  • 466
  • 1
  • 4
  • 22
  • What if there are other rows in table2 for the same referral? E.g. the status has been closed and re-opened multiple times? – Aaron Bertrand Jul 09 '12 at 18:53
  • There can be any number of status like (closed/open/reopen). We have to pick the max of the date which should be less than the T1OpenDate. – desi Jul 09 '12 at 18:56

1 Answers1

0
UPDATE Table1
    SET T2OpenDate = (SELECT MAX(Table2.T2OpenDate)
                      FROM Table2 
                      WHERE Table2.T2OpenDate < Table1.T1OpenDate)                       
FROM Table1

EDITED according to Aaron Bertrand's commnet.

If you want to get better on this particular skill I suggest you read this: How do I UPDATE from a SELECT in SQL Server?

Community
  • 1
  • 1
daniloquio
  • 3,822
  • 2
  • 36
  • 56
  • This doesn't give the right answer for the 4th row. You can fix this by adding `DESC` to the `ORDER BY` or removing that and changing `TOP 1` to `MAX`. – Aaron Bertrand Jul 09 '12 at 19:00
  • Thanks Daniloquio. I tried your query and as suggest by Aaron Bertrand i have added order by 'DESC' in the script, the result is accurate what i wanted. Thanks so very much for the quick solution you have provided. – desi Jul 09 '12 at 19:10
  • I slightly modified the above script to get the actual values after testing. UPDATE T1 SET T2OpenDate = (SELECT TOP 1 Table2.T2OpenDate FROM Table2 WHERE Table2.T2OpenDate < T1.T1OpenDT ORDER BY Table2.T2OpenDate desc) FROM Table1 T1 – desi Jul 09 '12 at 19:18
  • @desi my pleasure ... and thanks to you because this is my 1000 points answer, finally I'm a stackoverflow stablished user :) – daniloquio Jul 09 '12 at 19:22