PL/SQL -- 12-month range window count

[uninvitedm]

Heya

I've got a table of invoices, which have dates and customer_id's. What I need to get is the number of occurances for this customer for a 12-month range window. For example, if there's an invoice for october for a certain customer - i want to count how many other invoices appear are for this customer in the 12 months from that invoice date. Similarly, if there's an invoice in november, that should show the number of occurences 12 months from this november date too.

Also one further element is to only count 1 occurence by month-week. So if there are 3 occurences of a customer in the same week of month, it is counted as 1. I believe you can do something like this with a

count(distinct customer_id ||'~'|| trunc(invoice_date, 'IW')

-- to get the unique international week.

I think i need something like..

count ( distinct customer_id||'~'||trunc(invoice_date, 'IW') ) over (order by invoice_date asc range 100 preceding) customer_count1

But you're not allowed to but 'distinct' in there apparently.

Any suggestions? Any ideas are appreciated.

Thanks