Conditional Running Sum

[mezzanine1974]

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)

[mezzanine1974]

Re: Conditional Running Sum

Hello,
Thanks for posting.
But you know what, we dont need to compare SortedDate to SortedDate,
but we need to compare SortedDate to (Start-Finish) pair. Because if
it is between Start-Finish, we will sum up.
Is it clear please?

Regards.

[Salad]

Re: Conditional Running Sum

mezzanine1974 wrote:

Hello,
Thanks for posting.
But you know what, we dont need to compare SortedDate to SortedDate,
but we need to compare SortedDate to (Start-Finish) pair. Because if
it is between Start-Finish, we will sum up.
Is it clear please?
>
Regards.

Since you snipped my code, I'll wing it. Can't you add a " And " in the
dsum? Date1 <= DateFrom And Date2 <= DateTo

[mezzanine1974]

Re: Conditional Running Sum

Salad,
Please see Query2 output above. I can not get correct output with your
advice.
I dont know what to do
Thanks for your posting anyway

[mezzanine1974]

Re: Conditional Running Sum

BTW, your snipping of the posts...especia lly the important stuff, wastes

my time and would waste the time of anyone that would want to respond to
you.  If you want help, think.

I did not ask an easy question.Becaus e i have tried several methods
including your advice too, before sending my post. Since i know that
the question is not so easy, i tried to explain it as in detail as i
can.

Thank you

[Arch]

Re: Conditional Running Sum

On Fri, 28 Mar 2008 05:42:33 -0700 (PDT), mezzanine1974
<savas_karaduma n@yahoo.comwrot e:

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

[Arch]

Re: Conditional Running Sum

On Sun, 30 Mar 2008 22:55:30 -0700 (PDT), mezzanine1974
<savas_karaduma n@yahoo.comwrot e:

>Hello Arch,
>You are right that i did a mistake in the first line.
>
>your statement CORRECT! <By "between" do you mean inclusive of the
>Start date but not the
>Finish date?>
>

It probably can be done in SQL, but I'm not good enough in SQL to do
it. This function works as you requested, however:


Public Function CalcSum(Tdate As Date)
Dim SQL As String
Const StartDate As String = "Sdate"
Const FinishDate As String = "Fdate"
SQL = "Select sum(money) from q1 " & _
"where #" & Tdate & "# >= " & StartDate & _
" AND #" & Tdate & "# < " & FinishDate
CalcSum = CurrentProject. Connection.Exec ute(SQL).GetStr ing
CalcSum = Left(CalcSum, Len(CalcSum) - 1)
End Function



SELECT SortedDate,
CalcSum(SortedD ate) as RunSum
FROM Query1 ;

SortedDate RunSum
1/1/2008 1
2/1/2008 1
3/1/2008 10
4/1/2008 12
5/1/2008

-Arch

[mezzanine1974]

Re: Conditional Running Sum

There is no vocabulary to explain how i am happy ))
You gave me very powerfull tool as well for my further problems.
I spent almost whole day to fix this problem by SQL but i failed.
Thank you very much.