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

I have since refined it some. For one thing, dividing weeksSpent by weeks can made a divide by zero error when authorizedDate and duedate are less than a week apart, so I put in an IIF to get rid of that. For the second problem, I should have used days instead of weeks anyway, for greater accuracy, but it doesn't matter much. Also, the intermediate queries should include the subtask name so that everything groups properly and nothing is accidentally eliminated in UNION queries or anywhere else.