Running Sum Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Ofer said:
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.


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


Yanick said:
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
Click to expand...
Click to expand...
 
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 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!
Tom Ellison said:
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


Yanick said:
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
Click to expand...
Click to expand...
 
Back
Top