i'm working sybase 16. have orders spanning length of time start , end date (ie, 7/24/16-8/6/16 - 14 day span). constitutes 1 record. need create multiple rows 1 row. example above turn 14 records.
basically this:
000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/24/2016 would turn this:
000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/24/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/25/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/26/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/27/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/28/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/29/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/30/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 7/31/2016 000000000020517 000490000002318 7/24/2016 8/1/2016 2 0.071428571 1 1 8/1/2016 my digging has led me believe need write procedure , create temporary table make happen. that's need help. code, can tell i'm bit of novice... gentle.
i know need set/declare variables, that's i'm lost. below i'm working with. thank you.
begin declare local temporary table @posubset ( patientid char(15), patordersid char(15), lowvisits integer, highvisits integer, postartdate date, postopdate date, frequency integer, visitsperday integer, orderstring char, podate date ); while po.startdate <= po.stopdate loop insert @posubset (patientid, patordersid, startdate, stopdate, frequency, visitsperday, lowvisits, highvisits, podate ) select po.patientid, po.patvisitordersid, po.startdate, po.stopdate, po.frequency, (po.lowvisits/po.frequency) visitsperday, po.lowvisits, po.highvisits, podate <--- should incrementing value patientorders po; set podate = podate+1 end loop; end
Comments
Post a Comment