0

I'm working with Sybase 16. I have orders spanning a length of time with a start and end date (ie, 7/24/16-8/6/16 - 14 day span). This constitutes one record. I need to create multiple rows from this one row. The example above would turn into 14 records.

Basically this:

000000000020517 000490000002318 7/24/2016   8/1/2016    2   0.071428571 1   1   7/24/2016

Would turn into 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 to believe that I need to write a procedure and create a temporary table to make this happen. That's where I need the help. From my code, you can probably tell I'm a bit of a novice... be gentle.

I know I probably need to set/declare variables, but that's where I'm lost. Below is what 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 INTO @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) AS VisitsPerDay,
PO.LowVisits,
PO.HighVisits,
PODate <--- This should be the incrementing value

FROM PatientOrders PO;

SET PODate = PODate+1

END LOOP;
END
G. Larscheid
  • 1
  • 1
  • This is usually done by joining to a calendar table using `calendardate between startdate and enddate` – dnoeth Aug 01 '16 at 18:44
  • http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query or http://stackoverflow.com/questions/32982372/how-to-generate-all-dates-between-two-dates if cte supported – xQbert Aug 01 '16 at 18:46
  • I'm struggling to figure out how joining a calendar table would help me create the records I need. Can you give me an example? – G. Larscheid Aug 01 '16 at 19:12

0 Answers0