Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-14 : 09:48:15
|
| Wael writes "Hi all.i am using SQL server 2000 database and crystal reports XI.The purpose of this report is to measure the loyalty of the clients to buy and use a prepaid cards from a telephone company, the company concerned to know how it perform on a various range measures.in technical saying. we have 2 tables (CLL, CRD) the first one save a record about each call made by a certain card, including card no, source no(very important), destination no , date performed, duration and so on ...the second table contains information about card itself like card no, card price, type , pricing category, time open, date closed (very important), brand, and so on....as i mentioned there is only 2 fields in concern in our report.*the first is the source no as an indication of a client (assume that the client will always call from his home to outer line or whatever .... )*the second is the date closed as it says that a certain card finishes its value at this date, it will be the base date to build your periods on.the report should do the following:-1- prompt the user for a period range to analyze.2- prompt the user to specify how this range will be analyzed (yearly, quarterly, monthly, weekly, ....).3- cut the range into slices as determined in the second period.4- for each period, compare it with the previous periods (as whole from the start of the range to just before the current period) to get the following 3 counts: - new clients : didn't call at the previous period but called in the current period. - existing clients : called in previous period and still calling in the current period. - left clients : called in previous period and didn't in the current period .5- and so on till the last period in the range.6- this data preferred to be displayed graphically, if not as a crosstab.7- note that : i said certain client called in a certain period , if he used a card and call at least one time in a that period.please know that i am a new bie in stored procedures, so if there is a possibility for that report to run with stored procedure, i will be grateful if it is in some details.at the end , i am so sorry for that detailed explanation, but i want to clarify my poit. please any help, i will appreciate it.thanks for your time." |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-14 : 10:28:55
|
| You should do this through a stored procedure, using parameters for each of the filters that you want Crystal to prompt for.The SQL involved will be somewhat complex, so I encourage you to find a competent DBA to write it for you.If you want to take a stab at it, go ahead. If you have errors you can post your code on the forum and people will help you through it. Do not expect anybody on here to write the whole thing for you, though."I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!" |
 |
|
|
|
|
|
|
|
don't know how to get it work with stored precedures