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,

BrentCan 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,
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|||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...|||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