StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
9331449
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2012-02-17T15:57:51.353
FavoriteCount
0
LastActivityDate
2012-02-17T15:57:51.353
LastEditDate
LastEditorUserId
0
OwnerUserId
1185457
ParentId
9325463
PostTypeId
2
Score
2
ViewCount
0
LastEditorDisplayName
text
Body
<p>I'm assuming <code>valid_from</code>/<code>valid_to</code> never spans multiple years; i.e., <code>TO_DATE(valid_from,'YYYY')</code> always <code>= TO_DATE(valid_to,'YYYY')</code>.</p> <pre><code>SQL> CREATE TABLE source ( 2 bid VARCHAR2(5) 3 , valid_from DATE 4 , valid_to DATE 5 , qty NUMBER 6 ); Table created. SQL> INSERT INTO source VALUES ('00001',TO_DATE('20010201','YYYYMMDD'),TO_DATE('20010630','YYYYMMDD'),132); 1 row created. SQL> INSERT INTO source VALUES ('00002',TO_DATE('20020301','YYYYMMDD'),TO_DATE('20021231','YYYYMMDD'),59); 1 row created. SQL> CREATE TABLE destination ( 2 bid VARCHAR2(5) 3 , jan NUMBER 4 , feb NUMBER 5 , mar NUMBER 6 , apr NUMBER 7 , may NUMBER 8 , jun NUMBER 9 , jul NUMBER 10 , aug NUMBER 11 , sep NUMBER 12 , oct NUMBER 13 , nov NUMBER 14 , dec NUMBER 15 ); Table created. SQL> COLUMN jan FORMAT 999 SQL> COLUMN feb FORMAT 999 SQL> COLUMN mar FORMAT 999 SQL> COLUMN apr FORMAT 999 SQL> COLUMN may FORMAT 999 SQL> COLUMN jun FORMAT 999 SQL> COLUMN jul FORMAT 999 SQL> COLUMN aug FORMAT 999 SQL> COLUMN sep FORMAT 999 SQL> COLUMN oct FORMAT 999 SQL> COLUMN nov FORMAT 999 SQL> COLUMN dec FORMAT 999 SQL> INSERT INTO destination 2 SELECT bid 3 , NVL(MAX(DECODE(r,01,split_qty)),0) jan 4 , NVL(MAX(DECODE(r,02,split_qty)),0) feb 5 , NVL(MAX(DECODE(r,03,split_qty)),0) mar 6 , NVL(MAX(DECODE(r,04,split_qty)),0) apr 7 , NVL(MAX(DECODE(r,05,split_qty)),0) may 8 , NVL(MAX(DECODE(r,06,split_qty)),0) jun 9 , NVL(MAX(DECODE(r,07,split_qty)),0) jul 10 , NVL(MAX(DECODE(r,08,split_qty)),0) aug 11 , NVL(MAX(DECODE(r,09,split_qty)),0) sep 12 , NVL(MAX(DECODE(r,10,split_qty)),0) oct 13 , NVL(MAX(DECODE(r,11,split_qty)),0) nov 14 , NVL(MAX(DECODE(r,12,split_qty)),0) dec 15 FROM 16 ( 17 SELECT x.bid 18 , x.month_abbr 19 , x.r 20 , x.rn 21 , x.total_months 22 , x.qty 23 , FLOOR(x.qty / x.total_months) 24 + DECODE(x.rn 25 , x.total_months, MOD(x.qty, x.total_months) 26 , 0) split_qty 27 FROM (SELECT s.bid 28 , months.r 29 , ROW_NUMBER() 30 OVER (PARTITION BY s.bid 31 ORDER BY months.r) rn 32 , COUNT(*) 33 OVER (PARTITION BY s.bid) total_months 34 , s.qty 35 FROM (SELECT ROWNUM r 36 FROM DUAL 37 CONNECT BY LEVEL <= 12) months 38 , source s 39 WHERE TO_CHAR(s.valid_from,'YYYY') = TO_CHAR(s.valid_to,'YYYY') 40 AND months.r BETWEEN TO_NUMBER(TO_CHAR(s.valid_from,'MM')) 41 AND TO_NUMBER(TO_CHAR(s.valid_to,'MM'))) x 42 ) 43 GROUP BY bid 44 ; 2 rows created. SQL> SELECT * 2 FROM destination 3 ; BID JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 00001 0 26 26 26 26 28 0 0 0 0 0 0 00002 0 0 5 5 5 5 5 5 5 5 5 14 SQL> </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POOracle timeframe conversion
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USTebbe
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POOracle timeframe conversion
  singulars
  PostTypePostTypeId
  PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
2. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTAcceptedByOriginator
3. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. COThankyou @Tebbe for your answer and your effort!
  singulars
  PostPostId
  PO
  UserUserId
  USmaephisto

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.