Results 1 to 11 of 11

Thread: Help with query

  1. #1
    Join Date
    Jan 2003
    Location
    MN
    Posts
    7

    Unanswered: Help with query

    I need a way to comb through a large recordset (>1,000,000, fields: member id, date, amount1, amount2) and achieve the following:

    For every member, determine the date that sum of [amount2] hits or exceeds $2000, then start adding up [amount1]. I have spent many hours on this, and haven't found a way to do this (other than using VB with Excel, which takes a couple of days to run

    Any insights would be highly appreciated!

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    Do not know if it would speed up your process, but you might try eating the elephant bit by bit:

    - First design a group-by query on the date field, and let it calculate the sum for amount2, and let it select where sum>2000;

    - If satisfied with the select query, turn it into a make-table query to give you a stable intermediate.

    - than do whatever you want to do with amount1, although I do not exactly understand where you want to add amount1 to.

    hth

  3. #3
    Join Date
    Jan 2003
    Location
    MN
    Posts
    7
    Marion,
    thanks for your response. I have not been able to get "let it calculate the sum for amount2, and let it select where sum>2000" in one query. Putting the >2000 into the criteria field for sumofamount2 will not return any records since the criteria applies to the individual amounts, and not the sum.

    The goal is to calculate (sum up) how much each member paid (amount1) after the total of amount2 hits $2000.

    Your thoughts?





    Originally posted by marion
    Do not know if it would speed up your process, but you might try eating the elephant bit by bit:

    - First design a group-by query on the date field, and let it calculate the sum for amount2, and let it select where sum>2000;

    - If satisfied with the select query, turn it into a make-table query to give you a stable intermediate.

    - than do whatever you want to do with amount1, although I do not exactly understand where you want to add amount1 to.

    hth

  4. #4
    Join Date
    Jan 2003
    Location
    MN
    Posts
    7

    MORE INFO

    Here is some sample data and its expected outcome:

    MBR_ID date amount1 amount2
    1234567 01/05/2002 $3 $250
    1234567 02/05/2002 $9 $1,500
    1234567 02/15/2002 $6 $250
    1234567 02/23/2002 $6 $650
    1234567 08/24/2002 $9 $400
    3636363 01/11/2002 $6 $1,650
    3636363 04/11/2002 $12 $350
    3636363 04/22/2002 $6 $400
    3636363 05/12/2002 $12 $300
    3636363 05/26/2002 $9 $450

    The output should like like this:
    1234567 $15 (adding 6 and 9 after 250+1500+250 hit 2000)
    3636363 $27 (adding 6 + 12 + 9 after 1650+350 hit 2000)

    I tried running totals, but that only works for summing by month or year,
    which makes determining the cutoff date impossible.

    THANKS!

  5. #5
    Join Date
    Feb 2003
    Posts
    14
    try this:
    SELECT Table1.Field1, Sum(Table1.Field2) AS SumOfField2, Sum(Table1.Field3) AS SumOfField3
    FROM Table1
    GROUP BY Table1.Field1
    HAVING (((Sum(Table1.Field2))>2000))
    ;

    where field1 is your member number, field2 is the amount you are validating against the 2000 and field3 is the one you will sum...

    HTH
    Mike
    Last edited by Mickael; 02-03-03 at 14:28.

  6. #6
    Join Date
    Jan 2003
    Location
    MN
    Posts
    7

    Unhappy

    Mike,
    thanks for your reply. I used this query based on your suggestion:

    SELECT mbr_id, Sum(inv) AS sumofinv, Sum(copay) AS SumOfcopay
    FROM data
    GROUP BY mbr_id
    HAVING (((Sum(inv))>2000))

    Unfortunately, it sums up copays for ALL records, not just when the 2000 max is hit.

  7. #7
    Join Date
    Feb 2003
    Posts
    14

    Cool

    I got it now: add an identity (like autonum) to your table and then run the following queries:

    query 1: This table does a self join in order to create a running total through comparing the identity field to itself

    SELECT data.mbr_id, Sum(data_1.inv) AS SumOfinv, data.id
    FROM data INNER JOIN data AS data_1 ON data.mbr_id = data_1.mbr_id
    WHERE (((data_1.id)<=[data].[id]))
    GROUP BY data.mbr_id, data.id;

    query 2: This query uses the previous query (which I called runningtotal in my test) and grabs the minimum id for each member when the running total is equal to 2000. This will provide you with a list of members who hit this minimum as well as the record number at which they reach it

    SELECT runningtotal.mbr_id, Min(runningtotal.id) AS MinOfid
    FROM runningtotal
    WHERE (((runningtotal.SumOfinv)>=2000))
    GROUP BY runningtotal.mbr_id;


    query 3: This query sums the copay amounts for the records greater or equal to the minimum record identified above.

    SELECT [data].[mbr_id], Sum([data].[copay]) AS SumOfcopay
    FROM data INNER JOIN minid ON [data].[mbr_id]=[minid].[mbr_id]
    WHERE ((([data].[id])>=[minid].[minofid]))
    GROUP BY [data].[mbr_id];


    You could probably merge those queries into one statement by using sub queries.

    Mike

  8. #8
    Join Date
    Feb 2003
    Posts
    24

    Re: Help with query

    Originally posted by mhamach
    I need a way to comb through a large recordset (>1,000,000, fields: member id, date, amount1, amount2) and achieve the following:

    For every member, determine the date that sum of [amount2] hits or exceeds $2000, then start adding up [amount1]. I have spent many hours on this, and haven't found a way to do this (other than using VB with Excel, which takes a couple of days to run

    Any insights would be highly appreciated!
    I wonder if you shouldn't use some nested loops on the recordset sent by this query: SELECT * FROM <table> ORDER BY MBR_ID,date.

    MySum = 0
    While Not(end of recordset)
    While (same MBR_ID)
    While (sum(Amount2<2000))
    recordset.Next
    End while
    MySum = MySum + Amount1
    recordset.Next
    End while
    (do whatever you want with MySum)
    MySum = 0
    End while

    I'm sorry this just a theoritical algorithm I've imagined while reading your request. It is surely not written in any programming language but it might give you some ideas if you're stuck with doing your job just in SQL... which seems very likely to me!
    Good luck anyway!

  9. #9
    Join Date
    Jan 2003
    Location
    MN
    Posts
    7
    Hey thorgnole,
    I get your idea.... I hadn't thought of doing it in VB, and think that might prove to be a smart solution.

    However, I tested Mickael's 3 queries, and they work fantastically, so I will stop pursuing other options.

    Thank you, though!
    Michael

  10. #10
    Join Date
    Jan 2003
    Location
    MN
    Posts
    7

    Thumbs up

    Mickael,
    THANK YOU so much. You have been persistent and very helpful! Your 3 queries work like a charm. I tried them on about 500 records, and got correct results for the 8 members I tested.

    Michael

  11. #11
    Join Date
    Feb 2003
    Posts
    14

    Talking

    You are welcome. I always enjoy a SQL challenge
    Mickael

Posting Permissions

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