hello, I've been struggling to solve the following. I have large dataset ordered by acct ID, from date, to date (sorted descending), and amount (added the last two dummy columns as shown below). **Editing table for clarity. The first record will always contain a negative amount, with a to date [eff_to_dt0] of 31dec9999. Trying to obtain only, by customer ID, each (i) consecutive record(s) where (ii) the amount is negative. The consecutive record has a TO DATE column, which would always be 1 day prior, to preceding record's FROM DATE column. Data have: ID eff_from_dt0 eff_to_dt0 crnt_bal_amt0 GROUP_ID ROW 000000090 22-Jun-08 31-Dec-99 -3.62 8 1731 000000079 22-Jun-08 31-Dec-99 -1926.52 9 1732 000000002 4-Oct-18 31-Dec-99 -17.11 10 1733 000000002 4-Sep-18 3-Oct-18 -18.57 10 1734 000000002 4-Aug-18 3-Sep-18 -20.03 10 1735 000000002 4-Jul-18 3-Aug-18 -21.49 10 1736 000000002 28-Dec-17 3-Jul-18 -22.95 10 1737 000000002 27-Dec-17 27-Dec-17 -56.19 10 1738 000000002 18-Nov-17 26-Dec-17 -51.19 10 1739 000000002 24-Apr-17 17-Nov-17 -49.38 10 1740 000000002 27-Mar-17 23-Apr-17 -79.38 10 1741 000000002 18-Mar-17 26-Mar-17 101.5 10 1742 000000002 6-Mar-17 17-Mar-17 99.38 10 1743 000000002 9-Feb-17 5-Mar-17 79.38 10 1744 000000002 30-Jan-17 8-Feb-17 47.7 10 1745 000000069 20-Jun-09 31-Dec-99 -6819.53 11 1888 000000069 22-Jun-08 19-Jun-09 6164.97 11 1889 000000013 12-Nov-17 31-Dec-99 -23.96 12 1890 000000013 12-Oct-17 11-Nov-17 -16.62 12 1891 000000013 28-Sep-17 11-Oct-17 -20 12 1892 000000013 12-Sep-17 27-Sep-17 460.74 12 1893 000000013 29-Aug-17 11-Sep-17 449.34 12 1894 000000013 17-Aug-17 28-Aug-17 949.34 12 1895 Data want: ID eff_from_dt0 eff_to_dt0 crnt_bal_amt0 GROUP_ID ROW 000000090 22-Jun-08 31-Dec-99 -3.62 8 1731 000000079 22-Jun-08 31-Dec-99 -1926.52 9 1732 000000002 4-Oct-18 31-Dec-99 -17.11 10 1733 000000002 4-Sep-18 3-Oct-18 -18.57 10 1734 000000002 4-Aug-18 3-Sep-18 -20.03 10 1735 000000002 4-Jul-18 3-Aug-18 -21.49 10 1736 000000002 28-Dec-17 3-Jul-18 -22.95 10 1737 000000002 27-Dec-17 27-Dec-17 -56.19 10 1738 000000002 18-Nov-17 26-Dec-17 -51.19 10 1739 000000002 24-Apr-17 17-Nov-17 -49.38 10 1740 000000002 27-Mar-17 23-Apr-17 -79.38 10 1741 000000069 20-Jun-09 31-Dec-99 -6819.53 11 1888 000000013 12-Nov-17 31-Dec-99 -23.96 12 1890 000000013 12-Oct-17 11-Nov-17 -16.62 12 1891 000000013 28-Sep-17 11-Oct-17 -20 12 1892 *** As suggested, i'm adding a large sample in the attached csv file, already sorted. VAR Group_ID marks each new distinct ID, which with first record always have a negative balance, and the TO DATE of 31dec9999 (ie, current). Thanks We tried using LAG but as indicated elsewhere it led to missing records. Any help is appreciated. Thanks,
... View more