select rows where

I didn't know how to title this post because I can't think of a name for
what I am trying to do.

I have a table calltrak.calls which has a row for each call a customer
makes. I want to return all of the rows only for customers who have made
more than ten calls within a particular date period.

First of all, does this kind of query have a 'name'? I'd like to google it,
but I don't know what it's called!

Anyway, here is what I have:

select cust_nbr, acct_nbr, date, time, waive_chg_yn, multi_acct_call_ind,
bill_acct_typ, operator
from calltrak.calls
where cust_nbr in (
select cust_nbr
from calltrak.calls
where cust_nbr <> 0
and date >= '2007-06-01'
group by cust_nbr
having count(call_id) > 10
)
order by cust_nbr, acct_nbr, date, time;

Is this the appropriate way to do such a thing? It appears to give me the
results I am looking for.

Thanks,
Frank

Frank Swarbrick wrote:
> I didn't know how to title this post because I can't think of a name for
> what I am trying to do.
>
> I have a table calltrak.calls which has a row for each call a customer
> makes. I want to return all of the rows only for customers who have made
> more than ten calls within a particular date period.
>
> First of all, does this kind of query have a 'name'? I'd like to google it,
> but I don't know what it's called!
>
> Anyway, here is what I have:
>
> select cust_nbr, acct_nbr, date, time, waive_chg_yn, multi_acct_call_ind,
> bill_acct_typ, operator
> from calltrak.calls
> where cust_nbr in (
> select cust_nbr
> from calltrak.calls
> where cust_nbr <> 0
> and date >= '2007-06-01'
> group by cust_nbr
> having count(call_id) > 10
> )
> order by cust_nbr, acct_nbr, date, time;
>
> Is this the appropriate way to do such a thing? It appears to give me the
> results I am looking for.
You can also take a look at OLAP expressions.


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi Serge,
Please provide a link related to OLAP expressions. it will be a gr8
help.
Thanks
Sandy

sandeep.iitk-at-gmail.com wrote:
> Hi Serge,
> Please provide a link related to OLAP expressions. it will be a gr8
> help.
> Thanks
> Sandy
Google for "OLAP expression DB2" tons of links...
And then there is information center.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

SELECT *
FROM (SELECT cust_nbr, acct_nbr,
call_date, call_time, -- date and time are
reserved words
waive_chg_yn, multi_acct_call_ind, bill_acct_typ,
operator_id, -- operator is too vague (see
ISO-11179)
MAX() OVER (PARTITION BY cust_nbr) AS occurrences
FROM Calls
WHERE call_date >= '2007-06-01') AS X
WHERE X.occurrences > 10;

But this is going to give all of the calls made by the "heavy users"
with a ton of details. I would think that you want just cust_nbr and
acct_nbr for the output.

Looks like OLAP functions are working. I have a fairly complicated query
below. After that I will describe the requirements that caused me to need
this.


SELECT CASE
WHEN ROW_NBR = NBR_CALL_SEGS THEN 'Y'
ELSE ''
END AS BREAK
, ROW_NBR
, NBR_CALLS
, NBR_CALL_SEGS
, CASE MAX(CUST_NBR) OVER(ORDER BY CUST_NBR
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING)
WHEN CUST_NBR THEN ''
ELSE INSERT(
INSERT(
SUBSTR(CHAR(CUST_NBR),1,9)
,4,0,'-')
,7,0,'-')
END AS CUST_NUM
, CASE MAX(ACCT_NBR) OVER(ORDER BY CUST_NBR
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING)
WHEN ACCT_NBR THEN ''
ELSE CASE ACCT_TYPE
WHEN 'D' THEN 'DDA'
WHEN 'S' THEN 'SAV'
WHEN 'L' THEN 'LOAN'
WHEN ' ' THEN 'LOAN'
ELSE ACCT_TYPE
END
END AS ACCT_TYPE_TEXT
, CASE MAX(ACCT_NBR) OVER(ORDER BY CUST_NBR
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING)
WHEN ACCT_NBR THEN ''
ELSE INSERT(
INSERT(
SUBSTR(CHAR(ACCT_NBR),1,10)
,4,0,'-')
,8,0,'-')
END AS ACCT_NBR_TEXT
, CHAR(DATE, LOCAL) AS DATE
, CHAR(TIME, LOCAL) AS TIME2
, WAIVE_CHG_YN
, TERMID
, MULTI_ACCT_CALL_IND
, BILL_ACCT_TYP
, OPERATOR
FROM (
SELECT CUST_NBR
, ROW_NUMBER() OVER(PARTITION BY CUST_NBR
ORDER BY ACCT_NBR)
AS ROW_NBR
, COUNT(*) OVER(PARTITION BY CUST_NBR)
AS NBR_CALL_SEGS
, COUNT(CASE
WHEN MULTI_ACCT_CALL_IND IN ('B','N')
THEN 'Y'
ELSE NULL
END)
OVER(PARTITION BY CUST_NBR)
AS NBR_CALLS
, ACCT_TYPE
, ACCT_NBR
, DATE
, TIME
, WAIVE_CHG_YN
, TERMID
, MULTI_ACCT_CALL_IND
, BILL_ACCT_TYP
, OPERATOR
FROM CALLTRAK.CALLS
WHERE YEAR(DATE) = 2008
AND MONTH(DATE) = 07
AND CUST_NBR <> 0
)
WHERE NBR_CALLS >= 10
ORDER BY CUST_NBR, ACCT_NBR, DATE, TIME
FOR READ ONLY

I'll break it down to the inner select and then the outer select.

For the inner select, for the selected month (July 2008 in this case) I want
to retrieve all rows where the customer number is not zero. For each row I
need various columns, as well as:
- The row number (ROW_NBR).
- The total number of rows for this customer number (NBR_CALL_SEGS).
- The total number of rows where MULTI_ACCT_CALL_IND IN ('B','N')
(NBR_CALLS).

To clarify that, a "call" can consist of one or more "call segments". If
MULTI_ACCT_CALL_IND = 'B' this means this is the first 'segment' of a call,
with others being either 'C' (continue multi-segment call) or 'E' (end of
multi-segment call). A value of 'N' means it is not a multi-segment call.

I am converting an existing 'flat file' to the database, by the way, which
may or may not explain why a few things (calls versus call segments) might
be considered a bit odd.

The outer select needs, in cust_nbr, acct_nbr, date, time order, the fields
from the temp table created by the inner select, but only for those
customers with 10 or more calls.

For better or for worse I did some formatting of the data with my SQL.
Essentially, I want the customer number to be spaces for other than the
first row in which it appears. Same with the account type / account number.
So I used the OLAP function in order to determine if the current customer
number is the same as the one from the previous row.

I could have simplified at least the outer select by not formatting the data
and letting my application format the data. I'm not really sure which is
better. I kind of like this because it makes the application itself
simpler. But of course it makes the query itself harder to read.

Here's an example of what the final report looks like:

24 HOUR BANKING TRACKING REPORT - HIGH FREQUENCY
CUSTOMERS
FOR JULY 2008



CUSTOMER NBR/ ACCT ACCOUNT DATE / TIME WAIVE TERM MULT BILL 24
NAME TYPE NUMBER OF CALL CHG ID ACCT ACCT HOUR

100-00-1234 LOAN 000-123-4657 07-13 10:53 N WSZZ B N NBB
A CUSTOMER
SAV 987-555-1234 07-02 13:04 N WSZZ B Y MOC
07-16 12:45 N WSZZ B Y HRH

DDA 789-111-2222 07-01 17:25 N WSZZ B Y LDW
07-05 17:55 N WSZZ B Y JYZ
07-11 18:57 N WSZZ B Y ALO
07-17 22:46 N WSZZ B Y BDB
07-20 13:02 N WSZZ B Y OFA
07-27 20:43 N WSZZ B Y RCH

SAV 123-456-7890 07-17 22:58 N WSZZ B Y RCH

TOTAL CALLS FOR CUSTOMER - 10 (TOTAL CALL SEGMENTS - 10)

100-11-5678 DDA 987-654-3210 07-01 11:13 N WSZZ B Y BES
ANOTHER CUSTOMER
07-02 22:04 N WSZZ B Y DLH
07-08 11:01 N WSZZ B Y RAH
07-15 19:16 N WSZZ B Y LPT
07-16 07:10 N WSZZ C Y LRH
07-22 09:38 N WSZZ B Y RAH
07-29 20:35 N WSZZ B Y LPT
07-29 20:35 N WSZZ B Y LPT
07-29 20:35 N WSZZ B Y LPT
07-29 20:35 N WSZZ B Y LPT
07-29 20:35 N WSZZ B Y LPT
07-29 21:32 N WSZZ B Y ABP

DDA 298-402-1290 07-11 17:56 N WSZZ B Y DMK
07-16 07:10 N WSZZ B Y LRH

TOTAL CALLS FOR CUSTOMER - 13 (TOTAL CALL SEGMENTS - 14)

The customer number is used to look up the customer name in a separate,
non-relational, database, unfortunately.

(You may note that there are a lot of orphan B's that probably should be N's
and other weird things. I think the application that writes these records
is not working properly. Ah well.)

Thoughts?

Frank