Select Calling Cards which are expired !!!

[Malik1969]

Hi All,

I have a report where I have to describe how many prepaid calling cards has been expired (30 days to count after first use). In my Database I have card SERIAL number and other fields like DATE, AMOUNT, SALE etc. This means I have first to loop on each SERIAL, then check 1st day (min Date)it was used for making call and then last date (max Date), take the difference (last day - first day) and if it => 30 days, then say "Expired" else "In Use".

I am using CR 10.0 with SQL2000.

Any help will be greatly apprciated.

Kitto

 

[synapsevampire]

Group by the Serial and then use the following formula:

if max({table.date},{table.serial})-min({table.date},{table.serial}) > 30 then
"Expired"
else
"In use"

-k

 

[Malik1969]

Hi SynapseVampire,

Thanks for the tip. However I want to go further by giving following presentation: (Using Cross TAB if possible)

Date Cards Expired Card in Use BREAKAGE
01/12/04 251 658 $25
02/12/04 362 363 $12$
...
...
...

BEAKAGE is my Formula field which shows AMOUNT in SERIAL Left when card expires (for instance, a card expired with Used Amount $4.8 means, $5 (Card Face Value)- 4.8= $0.20 which is additonal revenue) .... Card Face Value is my constant (5).

I hope I am able to explain what I want to achieve. Thanks a lot for your possible help.

Regards
Kitto

 

[synapsevampire]

Unfortunately I don't have Crystal installed so I can't really test right now.

I think that you're going to have difficulties using a cross-tab since you're using summaries, but you can roll your own. You would be best served to let the database do the majoity of this for you since it's rather complicated now.

Also your requirements changed quite a bit, so rather than post possible solutions, I'll request that you flesh out the requirements more fully and give you another shot at any other changes you might have come up with:

Are you allowed to create database objects?
Example data
Expected output (based on the example data)

And what is the date field, the date that the card expired, or?

I would think that a view returning the expiration dates would simplify this greatly, and allow for a cross-tab.

-k

 

[Malik1969]

Hi SynapseVampire,

I understand your diappointment and I feel information provided earlier was not sufficient to propose any solution. Firs of all, this is my Fields/ data:

Table.Cards
SERIAL
DATE (Date/ time field)
CLI (Caller Line Identification)
DESTINATION (Telephone number dialed)
DURATION (in seconds)
AMOUNT (Communication use in value)
CREDIT ($5 - AMOUNT * CARD VALUE IS ALWAYS 5 DOLLARS)
DATA:

SERIAL DATE CLI DESTINATION DURATION AMOUNT CREDIT
10 2004-11-07 9192363312 19362558544 200 $2.15 $2.85
11 2004-12-01 9192363374 21236323663 652 $2.60 $2.40
11 2004-12-03 9192363612 8802532315 60 $1.00 $1.40
12 2004-11-23 9192963312 60366323158 58 $2.25 $2.75

Now from Above data, I want to run a report based on (DATE= LastFullWeek) in following presentation:

TOTAL CARDS IN USE :-: TOTAL CARD EXPIRED :-: PROFIT

I should brief you all fileds in table report;

TOTAL CARDS IN USE = Distinct Count SERIAL (EXCLUDING EXPIRED)
TOTAL CARD EXPIRED = IF MAX DATE-MIN DATE >= 30 DAYS
PROFIT = CREDIT left in card which are expired

I will tell you some background of prepaid calling card. Usually providers offer very cheap cards with validity of 30 days from first use. Now if Customer has left CREDIT in his card and it elasped over 30 days, then card get expired. There is no field which shows that it is expired, expect to calculate last date of use (-) first date of use. As you asked that if there is only on DATE field, yes it is only DATE field we have to check for each distinct SERIAL to see what is first time and last time usage.

I hope you have now clear picture. Nevetheless I am available to explain it further.

Thanks for your time and accepting this challange.

Kitto

 

[lbass]

As an aside, I don't understand why you wouldn't calculate the expiration date as:

dateadd("d",30,minimum({table.date},{table.serial}))
//i.e., first date + 30 days

-LB