Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Question Unanswered: Query to retrieve records that sum to a field in another table

    I really need help creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)

    dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00

    dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
    dbo.CorpAdv.AcctNum is text field
    dbo.CorpAdv.AdvAmt is numeric (dollar amounts)

    Here is sample data from dbo.CorpAdv

    pID AcctNum DatePaid TranCode AdvAmt
    1278 0023 1/1/2011 R 500.00
    1299 0023 2/1/2011 N 10.00
    1367 0024 2/2/2011 R 200.00
    1399 0025 2/3/2011 R 100.00
    1405 0023 3/1/2011 R 200.00
    1505 0024 3/2/2011 N 300.00
    1648 0025 3/3/2011 R 400.00
    1649 0025 3/4/2011 R 500.00
    1778 0024 3/5/2011 T 600.00
    1840 0023 4/1/2011 R 300.00
    1850 0024 4/2/2011 T 100.00
    1962 0025 4/3/2011 N 200.00
    1963 0023 5/1/2011 R -700.00
    1964 0023 5/1/2011 R 500.00
    2011 0024 5/2/2011 R 300.00
    2089 0025 5/3/2011 R 400.00
    2200 0023 6/1/2011 T 20.00
    2289 0024 6/2/2011 R 500.00
    2290 0025 6/3/2011 R 600.00
    2378 0025 6/4/2011 R 100.00
    2578 0024 6/5/2011 N 200.00
    2614 0023 7/1/2011 R 1200.00
    2657 0024 7/2/2011 R 300.00
    2887 0025 7/3/2011 R 400.00
    2991 0023 8/1/2011 R -50.00
    2992 0023 9/1/2011 T 50.00
    3222 0024 9/2/2011 N 500.00
    3245 0025 9/3/2011 T 600.00
    3246 0023 10/1/2011 R 200.00
    3377 0024 10/2/2011 R 100.00

    These are the records the query should return

    1964 0023 5/1/2011 R 500.00
    2614 0023 7/1/2011 R 1200.00
    2991 0023 8/1/2011 R -50.00
    3246 0023 10/1/2011 R 200.00

    Any help would be greatly appreciated

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with
        CTE_RowNo as
        (
            select t.*, ROW_NUMBER() OVER(ORDER BY pID DESC) AS RowNo
            from CorpAdv as t
            where (AcctNum = 0023) and (TranCode = 'R')
        ),
        CTE_Result as
        (
            select top 1 
                n.pID, 
                n.AcctNum, 
                n.DatePaid, 
                n.TranCode, 
                n.AdvAmt, 
                n.RowNo, 
                ((select Total from TotalAdv) - n.AdvAmt) as Total
            from CTE_RowNo as n
            order by RowNo
            
            union all
            
            select 
                n.pID, 
                n.AcctNum, 
                n.DatePaid, 
                n.TranCode, 
                n.AdvAmt, 
                n.RowNo, 
                (r.Total - n.AdvAmt) as Total
            from CTE_Result as r
            join CTE_RowNo as n on n.RowNo = r.RowNo + 1
            where r.Total > 0
        )
        
    select * from CTE_Result
    order by pID
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Can this be done without creating temporary tables?

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    I believe that is possible with a Stored Procedure, but in this case I prefer to use CTE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •