don't know what to use , complex query or sotred procedure

[WaelYassin]

hi all ,
i am using a SQL server database called NTCC.
it is a prepaid telephone company.
the main tables are:
1. cll - stores info about calls done (card no., source , distination , date , duration , cost).
2. CRD - contains information about cards used to perform the calls (card no., price , kind, pricing cat, ...)
*the company wants to analyze the customers usage of its cards.
*for a given period (start & end dates prompted), divided to a given sub periods(prompted ex: yearly, quarterly, monthly, weekly ...). from each sub period , we want to determine 3 counts (new customers (not called in the previous sub periods up to the start date of the range period) , existing customers (still using company cards from previous sub periods and still using in the current sub period) , left customers (customers used company cards in previous sub periods but not in the current sub period) ).
* we assume that the sender no. from the table call, is the main customer determiner as it by default is the home telephone name.
* when comparing the current sub period with the past sub periods, we deal with the past sub periods as a whole not indevidually.
* the result should look like a cross tab , Ex. :
for a yearly period

period new existing left
====================================
2005 70 115 35

2006 90 110 14


for a quarterly sub period

year quarter new existing left
=========================================
2005 Q1 20 222 17
2005 Q2 18 215 22
2005 Q3 25 225 16
2005 Q4 10 216 22
2006 Q1 30 228 11

and so on.

how to accomplish this , by a complex queries or what ever.
or i need a stored procedure?

thanks for your time

 

[mrdenny]

This sort of analysis is best done through Analysis Services. It will take your tables, summerize them and store them within Anslysis Services. This will allow users to query the data and drill down as they need to without placing attitional load on the SQL Server.

Failing that you'll need a few stored procedures to complete this directly off of the SQL Server's database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[WaelYassin]

thanks mardenny for your kind response.
but can you clarify points about which stored preocedures i need , and how to deal with analysis services?

thanks

 

[mrdenny]

Analysis Services is a whole new beast if you've never used it before. Start by reading the white papers and docs on microsoft.com.

These procedure will be extermly complex. Probably more than I can do based on info that can be posted in a forum.

Basicaly you'll need to create a temp table. That table will then need to be loaded with all the people who used the card in the previous period. Then you can use that table to figure out who are new users and who are existing users.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com