counting movements from stage to stage

[atthevilla]

Crystal 10, Oracle 10.
I have a database of potential donors at different stages of cultivation-A,B,C,D,E. There are multiple cultivation rows per donor each with a date. For example, if someone moves from stage A dated 1/1/06 to stage B dated 9/1/06, that is a current month move, How can I count the number of donors who moved from one stage to another this month, and the number who moved last month? This would not be the total number of A’s compared to the total number of B’s in this month and last month, but the number of donors who changed from A to B, B to C, C to D, etc. this month and last month. We may have to create a view for this, but is it possible with the current structure? Any help is appreciated.

 

[IdoMillet]

In your view open the Cultivation table twice.
Give the second copy an alias of "Cultivation_2"

join on:
Cultivation.Donor_ID = Cultivation_2.Donor_ID AND Cultivation.Stage <> Cultivation_2.Stage

Restrict the records in both aliases to the last 2 months.

In Crystal, add a CrossTab with
rows by Cultivation.Stage
columns by rows by Cultivation2.Stage
Summary: Count of Cultivation.Donor_ID

Cheers,
- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

You should provide a visual representation of the expected output. In general giving examples of the data and then the expected output eliminates guesswork.

I always suggest doing the real work on the database, however you might handle this in Crystal with a series of formulas.

So sorting by donor and then the stage allows for the proper ordering.

Then a formula of

whileprintingrecords;
numbervar CurrAtoB;
numbervar PrevAtoB;
numbervar CurrBtoC;
numbervar PrevBtoC;
If {table.stage} = "B"
and
month(Table.date}) = month(currentmonth) then
CurrAtoB:=CurrAtoB+1
else
If {table.stage} = "B"
and
month(Table.date}) = month(currentmonth)-1 then
PrevAtoB:=PrevAtoB+1
else
If {table.stage} = "C"
and
month(Table.date}) = month(currentmonth) then
CurrBtoC:=CurrBtoC+1
else
If {table.stage} = "C"
and
month(Table.date}) = month(currentmonth)-1 then
PrevBtoC:=PrevBtoC+1
else
...you get the idea...

Then you can display in the report footer using:

Previous month A to B:
whileprintingrecords;
numbervar PrevAtoB

Current Month A to B:
whileprintingrecords;
numbervar CurrAtoB

Previous month B to C:
whileprintingrecords;
numbervar PrevBtoC

Current Month B to C:
whileprintingrecords;
numbervar CurrBtoC

so you need to add the other variables and extend the logic, but this should work based on the limited information you shared.

-k