| Help: Histogram using Width_Bucket Function [message #317793] |
Sat, 03 May 2008 11:51  |
spsonkusare
Messages: 3
Registered: May 2008
|
Junior Member |
|
|
Oracle Version: Oracle 9i Release 2
Operating system: Sun Solaris
I have table of item sales
(This is just an example set to describe my requirement)
Table sales
===============
Order_No char(10) not null,
Item_no char(10) not null,
Uom char(6),
No_of_Units number(12,4),
price number(12,4)
date_of_sale date
I want to generate histogram through a single query based on date bracketing (weekly)
SELECT COUNT(*) NO_IF_ITEMS, (PRICE * NO_OF_UNITS) VALUE,
WIDTH_BUCKET(DATE_OF_SALE, FROM_DATE, TO_DATE,
TO_NUMBER(TO_CHAR(TO_DATE,'DD-MON-YYYY') - TO_CHAR(FROM_DATE,'DD-MON-YYYY')) / 7) BUCKET_NO
FROM SALES
WHERE DATE_OF_SALE BETWEEN &FROM_DATE AND &TO_DATE
This query returns bucket_no for each record through which I can group it using other super-query
But this query distributes records in uneven ways.
For example, for period of 01-jan-2008 to 31-jan-2008 for from_date and to_date resp.,
it distributes first 8 days in bucket 1, 7 days in bucket 2, 8 days in bucket 3,
7 days in bucket 4 and last date ie. 31-jan-2008 in bucket 5.
This pattern continues for other intervals also.
I want distribution precisely in bucket of 7 days (or interval days i specify)
no matter what the data is or interval is.
How should i modify this query or what should i do to achieve desired result?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
