The Mining Data Mart - Data Mining Techniques in CRM: Inside Customer Segmentation - page 163

For example, the following SQL queries can identify if calls have been made
during working days and working hours respectively:
case when datename(weekday,CALL_START_DATE) in
('Saturday','Sunday') then 'F' else 'T' end
case when datename(hour,CALL_START_DATE) between 8 and 17
then 'T' else 'F' end
The breakdown of calls according to the call hour (working vs. non-working)
can be extracted by using the IBM SPSS Modeler Derive node as shown in
Figure 4.5.
The following SQL query can be applied to extract the first layer of the
''network'' of each MSISDN (telephone number/SIM card), also referred to as
the outgoing community. The outgoing community of each MSISDN is defined as
the count of distinct telephone numbers (B-numbers) that have been called by the
MSISDN:
Select MSISDN,count(distinct B_NUMBER) as
DISTINCT_CALLED_NUMBERS
From dbo.CDR
Group by MSISDN
Finally, the following aggregation summarizes data at an MSISDN level and
on a monthly basis by calculating the total number and total duration of calls by
call type (the month of call has already been extracted from the field denoting the
date/time of call):
Select MSISDN,CALL_TYPE,YEAR,MONTH
count(*) as NUM_CALLS,sum(CALL_DURATION) as DUR_CALLS
from dbo.CDR
group by MSISDN,CALL_TYPE,YEAR,MONTH
The settings for extracting the same information by using the respective IBM
SPSS Modeler command (node) are shown in Figure 4.6.
Although things are more complicated in real-world situations, the method of
creating useful information from the CDRs is similar to the one presented above.
CURRENT INFORMATION
Current tables refer to all customers and lines, including ex-customers and closed
lines. These tables contain the most recent updates of the relevant information. For
example, a current table can be used to denote the most recent status information