>I actually need this. I have been asked to graph budgets in two different ways. So it's really two query challenges. I don't know if they can be done with queries at all.
>
>Subtask records have a name field, authorized date field, due date field, and an authorized hours field. Queries turn these fields into a weekly budget field by subtracting the due date from the authorized date, turning that into a "weeks remaining", and dividing that into the hours. The weekly budget for a task containing these subtasks is defined as the sum of the individual weekly budgets at a given time.
>
>Let's say my subtask table looks like this:
>
>A 2000 1/1/98 6/1/98
>B 3200 3/1/98 7/1/98
>
>and the weekly budgets, calculated in the query, are:
>
>A 100
>B 200
>
>
>...assuming exactly 4 weeks/month for simplicity.
>
>One way to represent the above example is to graph a query which would look like this:
>
>1/1/98 100
>3/1/98 300
>6/1/98 200
>7/1/98 0
>
>
>so I would need to figure out how to make such a cursor. Another way I have been asked to represent these amounts is to show the total expenditure that the budget allows over time:
>
>1/1/98 0
>3/1/98 800
>6/1/98 4400
>7/1/98 5200
>
>
>The above queries contain, I think, the minimum necessary points for graphing. Writing code that scans the tables and writes cursors can be done, but it would be cool to do it with a SELECT - SQL.
>
>Thank you.

I figured them out, I think. The idea is that for every existing authorized date and due date, there must be a data point. So, for both of them, I made this query:

SELECT DISTINCT approvaldate AS DatePoint FROM Subtasks UNION SELECT DISTINCT duedate FROM Subtasks INTO CURSOR SubDates ORDER BY DatePoint

...and, for both of them, I made this query to start the calculations:

SELECT subtaskname, authorizedHours, approvaldate, duedate, INT((duedate-approvaldate)/7) AS weeks, authorizedHours/weeks AS weeklybudget FROM Subtasks INTO CURSOR SubWeeks ORDER BY approvaldate

For the first one:

SELECT subtaskname, weeks, approvaldate], authorizedHours, duedate, SubDates.DatePoint, weeklybudget
FROM SubDates INNER JOIN SubWeeks ON SubDates.DatePoint >= SubWeeks.approvalDate AND SubDates.DatePoint <= SubWeeks.duedate
INTO CURSOR weekparts ORDER BY DatePoint, subtaskname

...followed by

SELECT DatePoint, Sum(weeklybudget) AS SumOfweeklybudget
FROM weekparts INTO CURSOR eachweeksbudget GROUP BY DatePoint

For the second one, make a query showing the amount to have been spent on each subtask for each "date point":

SELECT weeks, approvaldate, authorizedHours, duedate, SubDates.DatePoint, INT((datepoint-approvaldate)/7) AS WeeksSpent, authorizedHours*(WeeksSpent/weeks) AS AuthSpent
FROM SubDates INNER JOIN Subweeks ON SubDates.DatePoint >= Subweeks.approvaldate AND SubDates.DatePoint <= Subweeks.Duedate INTO CURSOR sublink ORDER BY DatePoint

...then make another query adding points showing 100% spent for each subtask already completed at a given "date point":

SELECT authorizedHours, duedate, SubDates.DatePoint FROM SubDates INNER JOIN Subweeks ON SubDates.DatePoint > Subweeks.duedate INTO CURSOR subdone ORDER BY DatePoint

...then stick the two together:

SELECT DatePoint, AuthSpent FROM SubLink UNION SELECT DatePoint, authorizedHours FROM SubDone INTO CURSOR suball ORDER BY DatePoint

...then sum over each "date point":

SELECT DatePoint, Sum(AuthSpent) AS SumOfAuthSpent FROM suball INTO CURSOR cumbudget GROUP BY DatePoint

...and that makes a running total of what should be spent on the whole task, over time.

Some of the intermediate queries may have some unnecessary fields which helped me figure out what I was doing.

(*whew*) thank you for your patience if you actually read this far.