question reg. a stored procedure

I am trying to write a stored procedure , where I want the data for the last 6 days from a table irrespective of whether the data is present or not.
and I want to count the no. of times an ID appears for each of the dates.

Example :

ID 7/27/2005 7/28/2005 7/29/2005 7/30/2005 7/31/2005 8/1/2005

AA 1 5 4 2 1
BB 3 1 1 1 1 4

Please help

HI
Just check this query and let me know if that helps you. I am a java developer but have a little bit idea abt Oracle. So just check the query.

What ever front end you are using, u can calculate the current date and last 6 date and pass them as a input to the query.
Lets say
date 1 - 08/02/2005
date 2 - 07/27/2005

So your query can be
select date, id, count(id) from trncust where date in (date1, date2)
group by date

Just try this out and let me know.

Regards
Makarand Parab

Thank you.

I wrote a query which works for my situation using left join but it is not completely correct.

suppose there a date is missing ( from the last 7 days ) in the table.
example: between 8/2/2005 and 7/26/2005 , if data is not available for 7/28/2005, It should appear as blank. Is this possible??

Another question, in java, how do i convert a string into date, i used

tmpDate=DateFormat.parse("7/21/2005"

;

but it says "The method parse(String) from the type DateFormat is not static"

Hi,
To answer your first question all I can think of is in the stored procedure create a temp table with all the dates you need & then use this in a left outer join with the data table, this would ensure that all dates from the temp table are in the results

For date format use:

SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
Date date = (Date)formatter.parse(value)
Make sure that your date format matches the format specified in the SimpleDateFormat constructor

Padma

I need the dates to by dynamic. like everyday it should have new set of last 7 days.

Right, that is why it needs to be a temp table, where you will have to use logic to populate the last 7 dates or whatever you need into the temp table in the stored procedure whenever it is run

Padma