OraFAQ Forum: SQL & PL/SQL » sort data in date range

Home » SQL & PL/SQL » SQL & PL/SQL » sort data in date range (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sort data in date range [message #438024]

Wed, 06 January 2010 19:18

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

Report message to a moderator

Re: sort data in date range [message #438025 is a reply to message #438024]

Wed, 06 January 2010 19:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: sort data in date range [message #438083 is a reply to message #438024]

Thu, 07 January 2010 01:22

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: sort data in date range [message #438107 is a reply to message #438024]

Thu, 07 January 2010 02:59

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.

Report message to a moderator

Re: sort data in date range [message #438125 is a reply to message #438107]

Thu, 07 January 2010 03:57

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Wooh! good catch, your nickname should be "DeepHawkEyed".

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:44:49 CST 2024