Running Sum Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yanick - 04 Apr 2006 15:17 GMT

I already tried to tweak many of the examples of running some on this group
without success. Hopping someone will be able to help me. Here is my problem.

I created a performance query that calculates the number of days between the
Open date and close date of request (CAR). Now I would like to add a running
sum on the number of days.

Here is my current SQL:

SELECT CAR.Type, DateDiff("d",[DateIn],[ClosedDate]) AS
NumberOfDaysOpenBeforeClose, CAR.DateIn, CAR.ClosedDate, CAR.DueDate
FROM CAR
WHERE (((CAR.Type) Like "*Drawing*Change*") AND (Not (CAR.ClosedDate) Is
Null And (CAR.ClosedDate)<#1/1/2006#));

Any ideas? Help will be very appreciated.

Yanick

Reply to this Message

Ofer - 04 Apr 2006 15:35 GMT

The best solution I can provide you with is to create a report based on this
query, and add another text box that is bound to NumberOfDaysOpenBeforeClose
field, with the Running sum property set to Yes.

Signature

\\// Live Long and Prosper \\//
BS"D

> I already tried to tweak many of the examples of running some on this group
> without success. Hopping someone will be able to help me. Here is my problem.
[quoted text clipped - 14 lines]
>
> Yanick

Reply to this Message

Yanick - 04 Apr 2006 16:10 GMT

Thanks for your reply but I already know how to do that. I realy need it in a
query cause I will link a chart to it after.

Thanks again,

Yanick

> The best solution I can provide you with is to create a report based on this
> query, and add another text box that is bound to NumberOfDaysOpenBeforeClose
[quoted text clipped - 18 lines]
> >
> > Yanick

Reply to this Message

Tom Ellison - 04 Apr 2006 16:17 GMT

Dear Yanick:

Ofer's suggestion is simplest and easiest. If you require this from a
query, it is also possible.

SELECT Type,
DateDiff("d", DateIn, ClosedDate) AS NumberOfDaysOpenBeforeClose,
DateIn, ClosedDate, DueDate,
(SELECT SUM(DateDiff("d", DateIn, ClosedDate))
FROM CAR C1
WHERE C1.ClosedDate <= C.ClosedDate)
AS RunningSum
FROM CAR C
WHERE Type Like "*Drawing*Change*"
AND ClosedDate IS NOT NULL
AND ClosedDate < #1/1/2006#

The running sume has to be based on some ordering of the rows. I have done
so on the ClosedDate column. I don't know if this is what you were wanting,
just a guess. You can substitute any other column you wish. For multiple
column ordering, it's a bit more complex.

If you had an ORDER BY clause in this, I would have used that ordering, so
the running sum would be in the order displayed. That only makes sense in
most cases. You may want to add the ordering.

Tom Ellison

>I already tried to tweak many of the examples of running some on this group
> without success. Hopping someone will be able to help me. Here is my
[quoted text clipped - 17 lines]
>
> Yanick

Reply to this Message

Yanick - 04 Apr 2006 16:43 GMT

I add to tweak your SQL a bit but it is working. Here is what it is looking
like:

SELECT C.Type, DateDiff("d",DateIn,ClosedDate) AS
NumberOfDaysOpenBeforeClose, C.DateIn, C.ClosedDate, C.DueDate, (SELECT
SUM(DateDiff("d", DateIn, ClosedDate)) FROM CAR C1 WHERE C1.ClosedDate <=
C.ClosedDate And c1.Type like "*Drawing*Change*") AS RunningSum
FROM CAR AS C
WHERE (((C.Type) Like "*Drawing*Change*") AND ((C.ClosedDate) Is Not Null
And (C.ClosedDate)<#1/1/2006#))
ORDER BY C.ClosedDate;

Thanks a lot!

> Dear Yanick:
>
[quoted text clipped - 45 lines]
> >
> > Yanick

Reply to this Message