StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are a lot of things unsaid. I hope you receive clearer requirements in your daily work ... Anyway, here is a simulation of your situation. It's based on the assumption that the days with data (one per week) are the same in 2007 as in 2008:</p> <pre><code>SQL> create table lnd_wkly_plan_rx_summary (pd_end_dt,nrx_cnt) 2 as 3 select date '2008-09-07', 100000 from dual union all 4 select date '2008-09-07', 536619.92 from dual union all 5 select date '2008-09-14', 698082.03 from dual union all 6 select date '2008-09-21', 403604.59 from dual union all 7 select date '2008-09-21', 200000 from dual union all 8 select date '2008-09-21', 100000 from dual union all 9 select date '2008-09-28', 702457.36 from dual union all 10 select date '2007-09-07', 400000 from dual union all 11 select date '2007-09-14', 450000 from dual union all 12 select date '2007-09-21', 500000 from dual union all 13 select date '2007-09-28', 550000 from dual union all 14 select date '2007-09-28', 100000 from dual 15 / Tabel is aangemaakt. </code></pre> <p>And your original queries, slightly modified.</p> <pre><code>SQL> SELECT pd_end_dt 2 , SUM(nrx_cnt) Total_Count 3 FROM lnd_wkly_plan_rx_summary 4 WHERE pd_end_dt >= date '2008-09-01' 5 AND pd_end_dt < date '2008-09-30' 6 GROUP BY pd_end_dt 7 / PD_END_DT TOTAL_COUNT ------------------- ----------- 07-09-2008 00:00:00 636619,92 14-09-2008 00:00:00 698082,03 21-09-2008 00:00:00 703604,59 28-09-2008 00:00:00 702457,36 4 rijen zijn geselecteerd. SQL> SELECT pd_end_dt 2 , SUM(nrx_cnt) Total_Count 3 FROM lnd_wkly_plan_rx_summary 4 WHERE pd_end_dt >= date '2007-09-01' 5 AND pd_end_dt < date '2007-09-30' 6 GROUP BY pd_end_dt 7 / PD_END_DT TOTAL_COUNT ------------------- ----------- 07-09-2007 00:00:00 400000 14-09-2007 00:00:00 450000 21-09-2007 00:00:00 500000 28-09-2007 00:00:00 650000 4 rijen zijn geselecteerd. </code></pre> <p>And the query with which you can compare the 2007 and 2008 data:</p> <pre><code>SQL> select to_char(pd_end_dt,'dd-mm') day_and_month 2 , sum(case trunc(pd_end_dt,'yyyy') when date '2007-01-01' then nrx_cnt end) sum2007 3 , sum(case trunc(pd_end_dt,'yyyy') when date '2008-01-01' then nrx_cnt end) sum2008 4 , sum(case trunc(pd_end_dt,'yyyy') when date '2008-01-01' then nrx_cnt end) 5 - sum(case trunc(pd_end_dt,'yyyy') when date '2007-01-01' then nrx_cnt end) difference 6 , ( sum(case trunc(pd_end_dt,'yyyy') when date '2008-01-01' then nrx_cnt end) 7 - sum(case trunc(pd_end_dt,'yyyy') when date '2007-01-01' then nrx_cnt end) 8 ) / sum(case trunc(pd_end_dt,'yyyy') when date '2008-01-01' then nrx_cnt end) * 100 percentage_difference 9 from lnd_wkly_plan_rx_summary 10 where ( ( pd_end_dt >= date '2007-09-01' 11 and pd_end_dt < date '2007-09-30' 12 ) 13 or ( pd_end_dt >= date '2008-09-07' 14 and pd_end_dt < date '2008-09-30' 15 ) 16 ) 17 group by to_char(pd_end_dt,'dd-mm') 18 / DAY_A SUM2007 SUM2008 DIFFERENCE PERCENTAGE_DIFFERENCE ----- ---------- ---------- ---------- --------------------- 07-09 400000 636619,92 236619,92 37,1681615 14-09 450000 698082,03 248082,03 35,5376617 21-09 500000 703604,59 203604,59 28,9373595 28-09 650000 702457,36 52457,36 7,46769313 4 rijen zijn geselecteerd. </code></pre> <p>Although rather verbose, I think it speaks for itself. You may like the following rewrite, since it doesn't repeat the aggregate functions as much as in the query above:</p> <pre><code>SQL> select day_and_month 2 , sum2007 3 , sum2008 4 , sum2008-sum2007 difference 5 , 100*(sum2008-sum2007)/sum2008 percentage_difference 6 from ( select to_char(pd_end_dt,'dd-mm') day_and_month 7 , sum(case trunc(pd_end_dt,'yyyy') when date '2007-01-01' then nrx_cnt end) sum2007 8 , sum(case trunc(pd_end_dt,'yyyy') when date '2008-01-01' then nrx_cnt end) sum2008 9 from lnd_wkly_plan_rx_summary 10 where ( pd_end_dt >= date '2007-09-01' 11 and pd_end_dt < date '2007-09-30' 12 ) 13 or ( pd_end_dt >= date '2008-09-07' 14 and pd_end_dt < date '2008-09-30' 15 ) 16 group by to_char(pd_end_dt,'dd-mm') 17 ) 18 / DAY_A SUM2007 SUM2008 DIFFERENCE PERCENTAGE_DIFFERENCE ----- ---------- ---------- ---------- --------------------- 07-09 400000 636619,92 236619,92 37,1681615 14-09 450000 698082,03 248082,03 35,5376617 21-09 500000 703604,59 203604,59 28,9373595 28-09 650000 702457,36 52457,36 7,46769313 4 rijen zijn geselecteerd. </code></pre> <p>Hope this helps.</p> <p>Regards, Rob.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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