0

Bit of a strange question. Entries in my table have three relevant columns:

drug
date_1
date_2
seq_id

I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the date_1 of the drug with the second lowest seq_id (the second lowest will always be lowest + 1) with the same drug name.

Does this make sense?

I don't have any real clue how to go about doing this. I've managed to make a query that returns the date_1 and seq_id of every second-lowest record, and now I want to make it such that every row with a seq_id exactly one lower than any seq_id in this set of records has date_2 = date_1 of the record with the 1-higher seq-id.

God this is really confusing, I hope it comes through.

0

1 Answer 1

2

The SQLServer version of this could look like this.

First we want the drug table and some values

create table #drugs (drug nvarchar(10),date_1 date, date_2 date, seq_id int)
 insert into #drugs values ('sildenafil',NULL,NULL,1)
                          ,('sildenafil',NULL,NULL,2)
                          ,('sildenafil',NULL,NULL,3)
                          ,('tadalafil',NULL,NULL,1)
                          ,('tadalafil',NULL,NULL,2)
                          ,('tadalafil',NULL,NULL,3)
                          ,('vardenafil',NULL,NULL,1)
                          ,('vardenafil',NULL,NULL,2)
                          ,('vardenafil',NULL,NULL,3)

Then we need to update the next lowest seq_id with a date

update #drugs 
  set date_1 = getdate() 
   where seq_id = 
   (select min(seq_id)+1 
    from #drugs d2 
    where #drugs.drug = d2.drug)

And the update the lowest date_2 value with the date_1 value from the second lowest seq_id - This is update with a join with the strange looking SQL-Server syntax and is

update D1
  set D1.date_2 = d2.date_1
  From #drugs D1
   inner join 
    (select drug,date_1,seq_id
     from #drugs d1 
     where seq_id = (select min(seq_id)+1 from #drugs)
    ) as d2
     on d1.drug = d2.drug and d1.seq_id = d2.seq_id-1

And Clean up

Drop Table #drugs

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.