Hi Bob,
If all you truly want is the number of items entered, by day, for a given
date range, you can do this in a single query. I run several Oracle
reports each month that do exactly this.
select to_char(cust_adddate, 'yy/mm/dd'), count(*)
from customer
where cust_adddate
between to_date(&start_mmddyyyy,'MMDDYYYY')
and to_date(&end_mmddyyyy)
group by to_char(cust_adddate,'yy/mm/dd')
The trick is to select "to_char", and group by "to_char".
The other trick is to realize the end date in the between clause will be
interpreted as that day at time 00:00.00000
That is, if you want the full month September, use a start_mmddyyyy of
'09012005' and end_mmddyyyy of '10012005'.
Hope that helps,
Mike
On 10/3/2005, "Bob Sawyer" wrote:
>
>I have a table in Oracle that includes a date field (set up as DATE,
>7). I'm trying to create a table showing the number of items entered on
>that date in a range of dates. I figure the best way to do this is get
>all the DISTINCT dates and check the counts for those dates in a
>separate query.
>
>However, I can't seem to get the distinct dates. I've tried:
>
>select distinct(CUST_ADDDATE) from CUSTOMER where CUST_ADDDATE >=
>to_date('$start', 'DD-MON-YY') and CUST_ADDDATE 'DD-MON-YY') order by
>CUST_ADDDATE asc
>
>but didn't get distincts. Figuring perhaps the TIME the item was
>entered on that date made a difference, I tried:
>
>select distinct(to_date(CUST_ADDDATE, 'DD-MON-YY')) from CUSTOMER
>[etc...]
>
>But got an error to the effect of "not a SELECTable item" or somesuch
>as that.
>
>SO --- how do I get distinct dates from an Oracle table?
>
>Thanks,
>_Bob
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/HKFolB/TM
--------------------------------------------------------------------~->
Community email addresses:
Post message: [email protected]
Subscribe: [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
List owner: [EMAIL PROTECTED]
Shortcut URL to this page:
http://groups.yahoo.com/group/php-list
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php-list/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
