unduplicated count

[JStrittmatter]

I need to calcuate an unduplicated count of new people every month throughout the fiscal year which is July - June. Can anyone please help me I would greatly appreciate it.

 

[Ngolem]

you use a formula (please read my tag line)

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[JStrittmatter]

I know I need to split the previous month services and the current month services and then compare them. and count those that do not match. i am not that great of a programer so any help would work greatly. I am fairly good with crystal just not the programming structure needed.

 

[Ngolem]

you still haven't described your report well enough for an answer....why make us guess what you are doing. Show us the layout either actual or hoped for with an example of what you want the report to look like....tell us how the report is grouped...details...a formula can do what you want but I have nothing to base it on

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[JStrittmatter]

I have a SQL database. There is a service delivery table which holds all data about which services were delivered to which consumers and the number of units to go along with it. It records service delivery on a monthly basis e.g. 07/2003 the month and year are seperate fields {Service_Month} and {Service_Year}. Each consumer has an unique identifier {Conumser_Uuid}. I need to know which consumers received service for the current month but did not received any services in any of the previous months. So if Jane Doe received service in July of this year she would not be counted. But John Doe on the other in October was the first time he has received service this fiscal year so he would be counted. All I need is the count and the consumer_uuid. If could get crystal to create two arrays one for current month and one for all previous months then compare the two arrays and count those that do not match I would be able to do it. But I don't know how to seperate the data into two arrays. This is where I tried to start but to to luck.

while {SERVICE_DELIVERY.SERVICE_MONTH} in [7,8] and {SERVICE_DELIVERY.SERVICE_YEAR} in [2003] do
(stringVar array prevmonth := [{SERVICE_DELIVERY.CONSUMER_UUID}]; );

I hope this is a better description of the problem and what I am looking for. If not I will contnue to supply you with whatever I can.

 

[Ngolem]

the report itself is just the numbers that are counted??

can't you just do that in SQL?

How far does your search for previous service go??? to the begining of time on the database?

You can perhaps do a report of users for the current service month...and count them as you process the report as well as storing their ID's in an array that will be shared with a subreport.
Then in a subreport in the report footer just look for all unique consumer ID's in your database and count them for all data except for the current month after eliminating ID's that are passed to the report in the shared Array.

Then in another section of the report footer you would calculate and display this statistic

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Turkbear]

Hi,
Depending on what SQL database you have ( SQL is a Language, not a product) yo can use Set operators to create
a view of your data( or use sql in the show sql window) :
Very, Very psedocode follows:

Select consumer_uuid from SERVICE_DELIVERY where 
SERVICE_MONTH = <currentmonth> and SERVICE_YEAR=<currentyear>
MINUS
Select consumer_uuid from SERVICE_DELIVERY where 
(SERVICE_MONTH <> <currentmonth> and   
   SERVICE_YEAR<><currentyear>);

That type of query will return ONLY those consumer_uuid
who have never received service except for this month/year.

hth,

 

[JStrittmatter]

I know SQL is a language. I am using Microsoft SQL Server 2000. If anyone could help with the SQL code that would be great. I appreciate everyones help.