| 12-month range window count [message #252581] |
Thu, 19 July 2007 04:26  |
m1ke
Messages: 10
Registered: July 2007
|
Junior Member |
|
|
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')
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
[Updated on: Thu, 19 July 2007 06:53] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: 12-month range window count [message #252594 is a reply to message #252586] |
Thu, 19 July 2007 05:54  |
m1ke
Messages: 10
Registered: July 2007
|
Junior Member |
|
|
Oops, thats meant to be 'range 365 proceding' -- to set the range as the number of days in a year! Which I assume is correct
The error I get when trying that line is..
'ORA-30487: ORDER BY not allowed here'
Not sure of oracle version i think its 8i but I could be wrong.
And yes, for each row. So every invoice that comes up, should have the number of times that customer appeared 12 months from that date..
Thanks
[Updated on: Thu, 19 July 2007 06:01] Report message to a moderator |
|
|
|
|
|
| Re: 12-month range window count [message #252621 is a reply to message #252613] |
Thu, 19 July 2007 07:49 |
m1ke
Messages: 10
Registered: July 2007
|
Junior Member |
|
|
Running select * from v$version
where banner like 'Oracle%';
I assume its 9i..
Thanks, I tried that code -- it initially came up with the error
'ORA-00923: FROM keyword not found where expected'
With the *, removed -- it gets further but this error appears
'ORA-30487: ORDER BY not allowed here' at the part where the order by is.
Thanks
|
|
|
|
|
|
| Re: 12-month range window count [message #252632 is a reply to message #252624] |
Thu, 19 July 2007 08:31 |
m1ke
Messages: 10
Registered: July 2007
|
Junior Member |
|
|
Ok I've got it working to this stage...
Select invoice_date,
count( to_char(invoice_date,'IW') )
over(partition by customer_id
order by invoice_date
range between 365 preceding
and current row
) cnt
from mytable
Had to change add_months to 365 as it kept throwing up error saying it was expecting a number. Its working to a point.. For example the output here is:
INVOICE_DATE CNT
14/08/2006 1
30/08/2006 2
13/09/2006 3
14/11/2006 4
15/11/2006 5
16/11/2006 6
The last 3 are in the same week of november (to_char(invoice_date, 'IW')) so these should be counted as 1, so it *should* look like..
14/08/2006 1
30/08/2006 2
13/09/2006 3
14/11/2006 4
15/11/2006 4
16/11/2006 4
My guess is there needs to be a 'distinct' on the to_char(document_date,'IW') part, however this throws an error that an order by is not allowed.
Thanks
[Updated on: Thu, 19 July 2007 08:32] Report message to a moderator |
|
|
|
|
|
| Re: 12-month range window count [message #252664 is a reply to message #252634] |
Thu, 19 July 2007 10:28 |
m1ke
Messages: 10
Registered: July 2007
|
Junior Member |
|
|
Try...
CREATE TABLE documents
(document_id NUMBER(10,0) NOT NULL,
customer_id NUMBER(10,0),
document_date DATE, NOT NULL
);
ALTER TABLE bmw_documents
ADD CONSTRAINT bmw_documents_pk PRIMARY KEY (document_id);
INSERT INTO bmw_documents
("DOCUMENT_ID")
VALUES
(0001, 221, to_date('14-08-2006', 'DD-MM-YYYY'));
/
INSERT INTO bmw_documents
("DOCUMENT_ID")
VALUES
(0002, 221, to_date('30-08-2006', 'DD-MM-YYYY'));
INSERT INTO bmw_documents
("DOCUMENT_ID")
VALUES
(0003, 221, to_date('13-09-2006', 'DD-MM-YYYY'));
INSERT INTO bmw_documents
("DOCUMENT_ID")
VALUES
(0004, 221, to_date('14-11-2006', 'DD-MM-YYYY'));
INSERT INTO bmw_documents
("DOCUMENT_ID")
VALUES
(0005, 221, to_date('15-11-2006', 'DD-MM-YYYY'));
INSERT INTO bmw_documents
("DOCUMENT_ID")
VALUES
(0006, 221, to_date('16-11-2006', 'DD-MM-YYYY'));
Thanks
|
|
|
|
| Re: 12-month range window count [message #252680 is a reply to message #252664] |
Thu, 19 July 2007 11:11 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> select b.*,
2 count(week)
3 over(partition by customer_id
4 order by document_date
5 range between document_date-add_months(document_date,-12) preceding
6 and current row) cnt
7 from ( select bmw_documents.*,
8 decode(lag(to_char(document_date,'IW'))
9 over (partition by customer_id
10 order by document_date),
11 to_char(document_date,'IW'), null,
12 to_char(document_date,'IW')) week
13 from bmw_documents ) b
14 /
DOCUMENT_ID CUSTOMER_ID DOCUMENT_DATE WE CNT
----------- ----------- ------------------- -- ----------
1 221 14/08/2006 00:00:00 33 1
2 221 30/08/2006 00:00:00 35 2
3 221 13/09/2006 00:00:00 37 3
4 221 14/11/2006 00:00:00 46 4
5 221 15/11/2006 00:00:00 4
6 221 16/11/2006 00:00:00 4
6 rows selected.
Regards
Michel
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
