Find sequential child rows that have amounts which add up to parent row's amount

Question

I have a table like following

ID  DATE       ACCT  TYPE  AMOUNT    SEQ  CHK#   TRC
1   6/5/2014   1234   C    10,000     1          1001
2   6/5/2014   3333         3,000     2   123    1002
3   6/5/2014   4444         5,000     3   234    1003
4   6/5/2014   5555         2,000     4   345    1004
5   6/5/2014   2345   C     3,000     1          1007
6   6/5/2014   5555         2,500     2   255    1008
7   6/5/2014   7777           500     3   277    1009
8   6/6/2014   1234   C     5,000     1          2001
9   6/6/2014   7777         3,000     2   278    2002
10  6/6/2014   8888         2,000     3   301    2003

The rows with TYPE = C are parent rows to the child rows that follow sequentially. The parent rows do not have CHK# and child rows do have CHK#. Each parent row has seq# = 1 and child rows have sequential numbers. (if it matters) From above table, row ID 1 is the parent row to the rows with ID 2 ~ 4. The AMOUNT on the child rows add up to the parent row's amount.

Querying for transaction for date of '6/5/2014' on account # 2345 with the amount of 3,000 - result should be rows with ID 6 and 7.

Is such query possible using MS-SQL 2008? If so, could you let me know?

Answer 1

Well, based on the data that you have, you can use the id column to find the rows that you want. First, look for the one that has the check in that amount. The look for the subsequent ids with the same group. How do you define the group? That is easy. Take the difference between id and seq. This difference is constant for the parent and child rows.

So, here is goes:

select t.*
from table t
where (t.id - t.seq) = (select t2.id - t2.seq
                        from table t2
                        where t2.type = 'C' and
                              t2.acct = '2345' and
                              t2.date = '6/5/2014'
                       ) and
      t.type is null;