Databases
StackOverflow2013
Postsdbo
POCalculate substraction by a specific quarter of year
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCalculate substraction by a specific quarter of year
    primarykey
    data
    text
    <p>I have some records after SQL generating:</p> <pre><code>YEARS MONTHS SUMMONTH SUMQUARTER QTR ----- ------ -------- ---------- --- 2009 Jan 363639 855922 1 2009 Feb 128305 855922 1 2009 Mar 363978 855922 1 2009 Apr 376633 1058871 2 2009 May 299140 1058871 2 2009 Jun 383098 1058871 2 2009 Jul 435000 1063577 3 2009 Aug 266227 1063577 3 2009 Sep 362350 1063577 3 2009 Oct 449366 1017906 4 2009 Nov 280943 1017906 4 2009 Dec 287597 1017906 4 2010 Jan 418277 661083 1 2010 Feb 129895 661083 1 2010 Mar 112911 661083 1 2010 Apr 163593 685625 2 2010 May 228505 685625 2 2010 Jun 293527 685625 2 2010 Jul 451608 1044364 3 2010 Aug 356683 1044364 3 2010 Sep 236073 1044364 3 2010 Oct 247365 798925 4 2010 Nov 414100 798925 4 2010 Dec 137460 798925 4 24 rows selected </code></pre> <p>The SUMQUARTER column sum up each quarter of a year... The qtr specify it belongs to which quarter.</p> <p>The problem is how to have a subtraction of sumquarter between 2 different years to get the specific query result?</p> <p>The difference is not the: max value-min value. It is the user-defined value that he want to input...</p> <p>Let say... For example, the user want to see the substraction(sumquarter) between 2009 qtr=2 and 2010 qtr=2, user may change the parameter(years,qtr) of sql to view the record. </p> <p>This mean the result should be: (1058871 - 685625)</p> <p>Here is the SQL that I am currently using:</p> <pre><code>select years,months,summonth,sumquarter,qtr from( select years,months,summonth,sumhour,hours,to_char(ym, 'Q') qtr, sum(sumhour) over(partition by years || to_char(ym, 'Q') order by years || to_char(ym, 'Q')) sumquarter,ym, count(days) over(partition by years,months,hours) days_month from( select years, months, days, hours, mins, sumHour, SUM (sumHour) OVER (PARTITION BY years,months,days) sumDay, SUM (sumHour) OVER (PARTITION BY years,months) sumMonth, SUM (sumHour) OVER (PARTITION BY years) sumyear, to_date(years || months, 'YYYYMon', 'NLS_DATE_LANGUAGE=American') ym from ( SELECT x.years, x.months, x.days, x.hours, x.mins, sum(x.value) as sumHour FROM xmltest, XMLTABLE ('$d/cdata/name' passing doc as "d" COLUMNS years integer path 'year', months varchar(3) path 'month', days varchar(2) path 'day', hours varchar(2) path 'hour', mins varchar(2) path 'minute', value float path 'value' ) as X group by x.years, x.months, x.days, x.hours, x.mins order by x.years, x.months, x.days ) ) ) group by years,months,summonth,sumquarter,qtr,ym order by ym </code></pre> <p>The sql pattern maybe something like this:...??</p> <pre><code>select ((select sumquarter from table where years=2009 and qtr=2) - (select sumquarter from table where years=2010 and qtr=2)) from table </code></pre> <p>Actually, it doesn't work...</p> <p>The result maybe look in this view:</p> <pre><code>SUBTRACT ---------- 373246 </code></pre> <p>Thanks everyone helps!!:)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser1264222
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCalculate substraction by a specific quarter of year
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCalculate substraction by a specific quarter of year
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload