4

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.

NA Slacker
  • 843
  • 6
  • 12
  • 24
  • Is Key a sequential value which could be used to apply an order? – S3S Feb 23 '17 at 21:21
  • Yes they key is auto_ident. Here I just put them all sequentially because it was easy. But I can order by that key value to get the sequence that the records were inserted into the table. – NA Slacker Feb 23 '17 at 21:22
  • What in the data indicates the change from B1 to B2? – Zohar Peled Feb 23 '17 at 21:27
  • B1 transitions to B2 because there is a new transform line inserted after a consume line. – NA Slacker Feb 23 '17 at 21:28
  • I've been wracking my brain for a while on this one trying to do it in SQL without resorting to a loop/cursor. If it turns out that there really isn't a way to do it without iterating that's ok. – NA Slacker Feb 23 '17 at 21:34

1 Answers1

3

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       |
+------+-------+-----------------+----------+-------------+----------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59