Home » SQL & PL/SQL » SQL & PL/SQL » Purchased Items summary
Purchased Items summary [message #265500] Thu, 06 September 2007 08:01 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi,

I do have a table with data in the following way.

 PDATE            NOP
--------- ----------
14-JAN-07          3
12-FEB-07          2
17-FEB-07          1
16-MAR-07          3
13-APR-07          2

PDATE:             Purchased Date.
NOP:               No of purchases.

I need to summarize the NOP to for every 14th of every month to have a output in this manner. How to solve this query?

Thanks.
Yashora

PDATE            NOP
--------- ----------
14-JAN-07          3
12-FEB-07          2
14-FEB-07          5
17-FEB-07          1
14-MAR-07          6
16-MAR-07          3
13-APR-07          2
14-APR-07          11

[Updated on: Thu, 06 September 2007 08:38] by Moderator

Report message to a moderator

Re: Purchased Items summary [message #265509 is a reply to message #265500] Thu, 06 September 2007 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to summarize the NOP to for every 14th of every month

Quote:
12-FEB-07
17-FEB-07
16-MAR-07
13-APR-07

How is that 14th?

Regards
Michel
Re: Purchased Items summary [message #265582 is a reply to message #265500] Thu, 06 September 2007 12:07 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi,

Since the cut-off date for every month is 14th, the output should represent that too, though date 14th is available or not with the table.

Regards,
Yashora

[Updated on: Thu, 06 September 2007 12:30] by Moderator

Report message to a moderator

Re: Purchased Items summary [message #265996 is a reply to message #265582] Sat, 08 September 2007 14:06 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one way to explore:
SQL> select * from t order by pdate;
PDATE       NOP
--------- -----
14-JAN-07     3
12-FEB-07     2
17-FEB-07     1
16-MAR-07     3
13-APR-07     2

5 rows selected.

SQL> select pdate, nop
  2  from t
  3  model
  4    return all rows
  5    reference end_period on ( 
  6      select rownum-1 dim, period
  7      from ( select unique 
  8                    case 
  9                      when extract (day from pdate) < 14 
 10                        then to_date(to_char(pdate,'"14"/MM/YYYY'),'DD/MM/YYYY')
 11                      else last_day(pdate)+14
 12                    end period
 13             from t
 14             order by 1 desc )
 15      )
 16      dimension by (dim) measures (period)
 17    dimension by (pdate) measures (nop)
 18    rules upsert all iterate (3)
 19      ( nop[period[iteration_number]] = sum(nop)[pdate<cv(pdate)] )
 20  order by pdate
 21  /
PDATE       NOP
--------- -----
14-JAN-07     3
12-FEB-07     2
14-FEB-07     5
17-FEB-07     1
14-MAR-07     6
16-MAR-07     3
13-APR-07     2
14-APR-07    11

8 rows selected.

Regards
Michel
Previous Topic: combining output of two cursors.
Next Topic: map the oracle user name
Goto Forum:
  


Current Time: Mon Dec 05 08:57:21 CST 2016

Total time taken to generate the page: 0.04838 seconds