SQL Partition By alternating groups of rows

Question

I have a data table that kind of looks like this.

|Key|LotId|TransactionType|Quantity|Destination
|1  |A    |Transform      |NULL    |Foo
|2  |A    |Transform      |NULL    |Bar
|3  |A    |Consume        |100     |NULL
|4  |B    |Transform      |NULL    |Bob
|5  |B    |Transform      |NULL    |Fred
|6  |B    |Consume        |75      |NULL
|7  |B    |Consume        |50      |NULL
|8  |B    |Transform      |NULL    |Sally
|9  |B    |Transform      |NULL    |Fred
|10 |B    |Consume        |60      |NULL
|11 |C    |Transform      |NULL    |Bar
|12 |C    |Transform      |NULL    |Fred
|13 |C    |Consume        |25      |NULL

The transform lines tell me where my quantity went and the consume line tells me how much quantity was use. The consume line applies to all previous transform lines for that LotId up to either the previous LotId or if it is the same LotId the previous transform & consume grouping. And to throw one added wrench the number of transform and consume lines within a group are variable. The one thing I do have working for me is that transform lines come first, then consume, the next time I encounter transforms I know a new grouping has started.

|Key|LotId|TransactionType|Quantity|Destination|Grouping
|1  |A    |Transform      |NULL    |Foo        |A1
|2  |A    |Transform      |NULL    |Bar        |A1
|3  |A    |Consume        |100     |NULL       |A1
---------------------------------------------------------
|4  |B    |Transform      |NULL    |Bob        |B1
|5  |B    |Transform      |NULL    |Fred       |B1
|6  |B    |Consume        |75      |NULL       |B1
|7  |B    |Consume        |50      |NULL       |B1
---------------------------------------------------------
|8  |B    |Transform      |NULL    |Sally      |B2
|9  |B    |Transform      |NULL    |Fred       |B2
|10 |B    |Consume        |60      |NULL       |B2
---------------------------------------------------------
|11 |C    |Transform      |NULL    |Bar        |C1
|12 |C    |Transform      |NULL    |Fred       |C1
|13 |C    |Consume        |25      |NULL       |C1

(for the purposes of this example we'll just assume that the quantity is split evenly across all parties)

• Group A1 there was 100 split between Foo & Bar
• Group B1 there was 125 split between Bob and Fred
• Group B2 there was 60 split between Sally & Fred
• Group C1 there was 25 split between Bar and Fred

Using the sql RANK(), DENSE_RANK(), & ROW_NUMBER() windowing I am trying to work out a query which will give me this grouping. Once I'm able to get this grouping I should then be able to join the data back onto itself and ultimately determine how much each one of my destinations received.

This is on SQL2008.

Answer 1

Using a combination of a common table expression, outer apply(), and dense_rank()

note: I changed the column Key to tKey so I would not have to use square brackets around it.

;with cte as (
  select *
    , PrevTransactionType=isnull(x.Prev_TransactionType,'Consume')
  from t
  outer apply (
      select top 1
        Prev_TransactionType = TransactionType
      from t as i
      where i.tKey < t.tKey
      order by i.tKey desc 
      ) as x
)
select  t.tKey, t.LotId, t.TransactionType, t.Quantity, t.Destination
 , Grouping = LotId + convert(varchar(10),dense_rank() over (
    partition by LotId 
    order by GroupNumber
    )
  )
from cte as t
outer apply (
  select top 1 
    GroupNumber = i.tKey
    from cte as i
    where i.tKey <= t.tKey
      and i.TransactionType = 'Transform'
      and i.PrevTransactionType = 'Consume'
    order by i.tKey desc
    ) x

test setup: http://rextester.com/LWV40248

results:

+------+-------+-----------------+----------+-------------+----------+
| tKey | LotId | TransactionType | Quantity | Destination | Grouping |
+------+-------+-----------------+----------+-------------+----------+
|    1 | A     | Transform       | NULL     | Foo         | A1       |
|    2 | A     | Transform       | NULL     | Bar         | A1       |
|    3 | A     | Consume         | 100      | NULL        | A1       |
|    4 | B     | Transform       | NULL     | Bob         | B1       |
|    5 | B     | Transform       | NULL     | Fred        | B1       |
|    6 | B     | Consume         | 75       | NULL        | B1       |
|    7 | B     | Consume         | 50       | NULL        | B1       |
|    8 | B     | Transform       | NULL     | Sally       | B2       |
|    9 | B     | Transform       | NULL     | Fred        | B2       |
|   10 | B     | Consume         | 60       | NULL        | B2       |
|   11 | C     | Transform       | NULL     | Bar         | C1       |
|   12 | C     | Transform       | NULL     | Fred        | C1       |
|   13 | C     | Consume         | 25       | NULL        | C1       |
+------+-------+-----------------+----------+-------------+----------+