Say I have the following data set. I want to be able to group based upon the wrap up date. Wrap up signals the end of a group.   There will be multiple customers in the table with similar data.

In other words I want to produce the following data but I can't get the SQL right. I am using db2 DSN12015.  Any help to produce the output below using SQL would be helpful.  TIA!

For something like this, you should be able to do a subquery to count how many 'WRAP-UP' records were written before the current record.
Based on the data provided, im not 100% sure what fields control the order of records, but the following query is based on the date controlling the order of records.
If its customer and date, then add customer to the where clause.

select customer, 
           transaction_date,
           code,
           (select count(*)
            from qtemp/a as a
            where code = 'WRAP-UP'
              and b.transaction_date > a.transaction_date) + 1 as group
    from qtemp/a as b

This query produced these results from the temporary table i built.


If this table isn't keyed or has something to help control the order, then you may need to use the RRN function to provide a key value to each row.
I can provide an example of this if needed.

-Mike Z

Thanks much for the information Mike.  

Yes I think I will need the additional RRN coding as there are multiple customers and multiple transaction dates not necessarily in order in the source table.  The order by would be Customer and Transaction Date.  Thanks again.

Below is the example query using RRN.
We select every row from the table while applying a relative record number (RRN). This is essentially a temporary key so we know the exact order of records.
Once we have that temporary table, the final select is similar to the previous answer except it uses the row_num field to look for previous 'WRAP-UP' records.


with 
    row_numbers as (
        select RRN(a) row_num,
               customer,
               transaction_date,
               code
        from qtemp/a
    )
    
    select customer, 
           transaction_date,
           code,
           (select count(1) + 1
            from row_numbers b
            where code = 'WRAP-UP'
              and b.row_num < a.row_num) as group
    from row_numbers a;

-Mike Z