Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Assistance on SUM() statement

    I have a table #changes with two columns: familyID, Versiontime

    For each record in the #changes table I need to compute the income from another table FamilyIncome with colunms: familyID, IncomeType, EffectiveDate, Amt. Example Below:

    FamID, IncType, EffDate, Amt
    100, 10, 01/01/2003, $50.00
    100, 20, 01/01/2003, $50.00
    100, 30, 01/01/2003, $50.00
    100, 10, 02/02/3003, $100.00
    100, 20, 02/02/3003, $100.00
    100, 30, 02/02/3003, $100.00
    100, 40, 02/02/3003, $100.00
    100, 20, 03/03/3003, $75.00
    100, 30, 03/03/3003, $75.00
    100, 40, 03/03/3003, $75.00

    So if I'm looking for the Incomes on the following dates (which are in the #changes table) it should be:

    01/02/2003 - $150.00 (The three records effective on 01/01/2003)
    02/10/2003 - $400.00 (The four records effective on 02/02/2003)
    04/01/2003 - $325.00 (One record from 02/02/2003 is still effective (Type 10) plus the three records effective on 03/03/2003)

    Any help is greatly appreciated,

    Brent

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the DDL?

    But it seems like a join between the two and a GROUP by with a SUM

    something like

    SELECT EFF_DATE, SUM(AMT)
    FROM myTable1 a myTable2 b
    ON a.key = b.key
    GROUP BY EFF_DATE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Brett,
    I have tried a couple variations on this theme and so far they do not deliver the desired results:

    1. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid and EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
    from #changes
    group by familyid, cast(versiontime as datetime),#changes.versiontime
    order by familyid, versiontime desc
    THIS CODE WORKS FOR THE FIRST DATE AND RETURNS THE $150.00 DESIRED, BUT ON ANY FUTURE DATES IT ADDS THE NEW INCOME AND KEEPS A RUNNING TOTAL (I.E. $550.00 INSTEAD OF $400.00)

    2. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid
    HAVING EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
    from #changes
    group by familyid, cast(versiontime as datetime),#changes.versiontime
    order by familyid, versiontime desc
    THIS CODE ONLY RETURNS A RESULT SET FOR THE LAST DATE IN THE SEQUENCE AND THEN IT RETURNS $225.00 (ONLY THE RECORDS WITH A 03/03/2003) DATE)
    At this time I'm just trying to isolate the effdate calculations. Also whoever reads this all the dates should be 2003 the 3003 for the year is a typo.

    Thanks,

    Brent
    Originally posted by Brett Kaiser
    Can you post the DDL?

    But it seems like a join between the two and a GROUP by with a SUM

    something like

    SELECT EFF_DATE, SUM(AMT)
    FROM myTable1 a myTable2 b
    ON a.key = b.key
    GROUP BY EFF_DATE

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't understand your results...where do these dates come from:

    01/02/2003 - $150.00 (The three records effective on 01/01/2003)
    02/10/2003 - $400.00 (The four records effective on 02/02/2003)
    04/01/2003 - $325.00 (One record from 02/02/2003
    They don't exists in your data...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Originally posted by Brett Kaiser
    I don't understand your results...where do these dates come from:



    They don't exists in your data...
    Those dates come out of the #changes table which I only included the fields not an example. What I have is 43,000 records in the #changes table that are familyID's and Dates on which I need to perform several calculations (size of the family, income, fee schedule, etc) right now I'm hung up on getting the income which I need in order to determine the fee (fee is based on family size and income)

    Brent

Posting Permissions

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