Re: Conditional Running Sum

On Fri, 28 Mar 2008 05:42:33 -0700 (PDT), mezzanine1974
wrote:

>What If I ask such a question? I have Query1 which is extracted from
>two tables where "SortedDate" and "Start" fields are linked. It gives
>following result.
>
>Query1
>***
>SortedDate Money Start Finish
>01-01-2008 1$ 01-01-2008 04-01-2008
>02-01-2008 NULL NULL NULL
>03-01-2008 9$ 03-01-2008 05-01-2008
>04-01-2008 3$ 04-01-2008 05-01-2008
>05-01-2008 NULL NULL NULL
>
>By another query (say that Query2), I need to sum up "Money" values
>for all "SortedDate" in such a way that "SortedDate" will take place
>between Start and Finish of Query1 row where "Money" values being
>added up.
>
>Output will be as below.
>For any hint, i will be appriciated.
>
>Query2
>***
>SortedDate Money
>01-01-2008 1$ /1$ only (Because, 01-01-2008 is between Start-
>Finish of corresponding Query1 row)
>02-01-2008 1$ /1$ only (Because, 02-01-2008 is between Start-
>Finish of corresponding Query1 row)
>03-01-2008 10$ /1$+9$ (Because, 03-01-2008 is between Start-
>Finish of corresponding Query1 rows)
>04-01-2008 12$ /9$+3$ (Because, 04-01-2008 is between Start-
>Finish of corresponding Query1 rows)
>05-01-2008 0$ /0$ (Because, 05-01-2008 is not between any Start-
>Finish of corresponding Query1 row)

Your example data are inconsistent.

For your first example line, you say that the running sum should be 1.
But, 01-01-2008 does not fall between that Start and Finish dates of
any of the example records.

For your fifth example, you state the correct running sum is 0 because
05-01-2008 in not between any of the Start Finish pairs.

By "between" do you mean inclusive of the Start date but not the
Finish date? That is what is suggested by your sample output.