Home » SQL & PL/SQL » SQL & PL/SQL » sort data in date range (oracle 10g)
sort data in date range [message #438024] Wed, 06 January 2010 19:18 Go to next message
chirutha
Messages: 1
Registered: January 2010
Junior Member
Hi All,
I have an interesting and unsolved problem.

My cursor fetches data in the following format.
qty customer startdate enddate
100 smith 10-Jan-2010 10-Jan-2020
-100 chris 10-Jan-2010 31-May-2011
200 ruan 01-Jan-2011 25-Jul-2016


I now have to sort out this data and insert into a table in such a way that when i enter 2 dates (say 10-Jan-2010 to 31-May2013), i should get sum(qty) for which the dates entered falls under the ranges given in the question at day wise.

Suppose if i give the dates 01-Jun-2011 till 30-Jun-2011,
i should get 30 rows with each qty as 100+200. (as second period is not falling in this )

Please help
Re: sort data in date range [message #438025 is a reply to message #438024] Wed, 06 January 2010 19:30 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: sort data in date range [message #438083 is a reply to message #438024] Thu, 07 January 2010 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Suppose if i give the dates 01-Jun-2011 till 30-Jun-2011,
i should get 30 rows with each qty as 100+200. (as second period is not falling in this )

Why 30 and not 50 or 10?

Post a workingTest case: create table and insert statements along with the result you want with these data.
Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: sort data in date range [message #438107 is a reply to message #438024] Thu, 07 January 2010 02:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm a little unclear about what you're trying to do, but you definitely want some sort of RowGenerator

You use the Row generator to return one row for each date between the two ranges, and then join to your original table using this date to get the rows that you want.
Re: sort data in date range [message #438125 is a reply to message #438107] Thu, 07 January 2010 03:57 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wooh! good catch, your nickname should be "DeepHawkEyed".

Regards
Michel
Previous Topic: Passing a parameter into a variable place holder
Next Topic: blob data in oracle 10g
Goto Forum:
  


Current Time: Sun Sep 25 21:31:09 CDT 2016

Total time taken to generate the page: 0.17153 seconds