This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes question reg. a stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "question reg. a stored procedure" Watch "question reg. a stored procedure" New topic
Author

question reg. a stored procedure

meera rao
Ranch Hand

Joined: Jun 30, 2005
Posts: 67
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
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
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
meera rao
Ranch Hand

Joined: Jun 30, 2005
Posts: 67
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"
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
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
meera rao
Ranch Hand

Joined: Jun 30, 2005
Posts: 67
I need the dates to by dynamic. like everyday it should have new set of last 7 days.
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: question reg. a stored procedure