Home » SQL & PL/SQL » SQL & PL/SQL » 12-month range window count
12-month range window count [message #252581] Thu, 19 July 2007 04:26 Go to next message
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')
-- 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

[Updated on: Thu, 19 July 2007 06:53] by Moderator

Report message to a moderator

Re: 12-month range window count [message #252586 is a reply to message #252581] Thu, 19 July 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why "range 100 preceding"?

Do you want this for each row or for a precise one?
That is: does your application give you the customer_id and you display all rows for this customer or does it give the customer_id and date?

What was the error you got?
What is your Oracle version?

Regards
Michel
Re: 12-month range window count [message #252594 is a reply to message #252586] Thu, 19 July 2007 05:54 Go to previous messageGo to next message
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 #252613 is a reply to message #252594] Thu, 19 July 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which 8i?
Analytic functions were introduced in 8.1.6.
More were added in 8.1.7.

Select *,
       count(distinct to_char(invoice_date,'IW'))
         over(partition by customer_id 
              order by invoice_date
              range between add_months(invoice_date,-12) preceding 
                        and current row) cnt
from mytable
/

Regards
Michel

Re: 12-month range window count [message #252621 is a reply to message #252613] Thu, 19 July 2007 07:49 Go to previous messageGo to next message
m1ke
Messages: 10
Registered: July 2007
Junior Member
Running
select * from v$version
where banner like 'Oracle%';
says 'Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production' so
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 #252624 is a reply to message #252621] Thu, 19 July 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix it or post a test case that I can test my query.

Regards
Michel
Re: 12-month range window count [message #252632 is a reply to message #252624] Thu, 19 July 2007 08:31 Go to previous messageGo to next message
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 #252634 is a reply to message #252632] Thu, 19 July 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I could more help you if you post a test case (create table and insert statements).

Regards
Michel

[Updated on: Thu, 19 July 2007 08:40]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: 12-month range window count [message #252830 is a reply to message #252680] Fri, 20 July 2007 03:20 Go to previous messageGo to next message
m1ke
Messages: 10
Registered: July 2007
Junior Member
Excellent! Yeah that got it thanks.. oops, i wasnt supposed to type the 'bmw_' part, habit. And even then its supposed to be 'bmm'. Thanks alot, Michel.
Re: 12-month range window count [message #252836 is a reply to message #252830] Fri, 20 July 2007 04:01 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer Porsche. Cool

Regards
Michel
Previous Topic: can we revert value of boolean column in single statement
Next Topic: Numeric and alphabetic data operations
Goto Forum:
  


Current Time: Thu Dec 08 12:41:58 CST 2016

Total time taken to generate the page: 0.08104 seconds