SQL/MX Data Mining Guide

Preparing the Data
HP NonStop SQL/MX Data Mining Guide—523737-001
2-8
Aligning the Data
THEN m.year_month
END
FROM acct_history m
SEQUENCE BY m.account, m.year_month)
t (account, year_month, close_month1, close_month2)
GROUP BY t.account)
p (account, close_month1, close_month2, open_month));
The derived attribute Close_Month1 contains the month when a customer explicitly
closed their account (the Account Status is marked Closed). The first CASE expression
in the inner query uses the OFFSET sequence function to determine the month when
an account is closed explicitly.
The derived attribute Close_Month2 contains the month when a customer implicitly
closed their account (maintained a zero balance for three months). The second CASE
expression in the inner query uses the OFFSET sequence function and the ROWS
SINCE INCLUSIVE sequence function to determine the month when an account has a
zero balance for three months.
The derived attribute Open_Month is the month when the account was opened. In the
CASE expression of the outer query, this month is adjusted to be the month in the
middle of the account history. The account history interval is defined to start with the
first month the account is open up to the date 1999-12-01.
The derived attribute Close_Month in the Close_Temp table is set to either
Close_Month1 (when a customer explicitly closed their account), Close_Month2 (when
a customer maintained a zero balance for three months), or the month in the middle of
the Account History interval (when an account is open).
The derived attribute Cust_Left is set to Y if a customer has a zero balance for three
months or if the Account Status is marked Closed.
In queries that use sequence functions, note the use of the SEQUENCE BY clause.
See SEQUENCE BY Clause and Sequence Functions in the SQL/MX Reference
Manual for more information.
Here are the contents of the Close_Temp table after the preceding row insertion:
Account Number Close Month Cust_Left
1000000 1999-03-01 N
1234567 1999-12-01 Y
2300000 1999-11-01 Y
2400000 1998-12-01 Y
2500000 1999-10-01 Y
2900000 1999-06-01 N
3200000 1999-05-01 Y
3900000 1998-10-01 Y
4098124 1998-10-01 Y