a sql query to compare results of follwing queries

Question

SELECT pd_end_dt,SUM(nrx_cnt) Total_Count
FROM wkly_lnd.lnd_wkly_plan_rx_summary
WHERE pd_end_dt >= '01-Sep-08' AND pd_end_dt < '30-Sep-08'
GROUP BY pd_end_dt

SELECT pd_end_dt,SUM(nrx_cnt) Total_Count
FROM wkly_lnd.lnd_wkly_plan_rx_summary
WHERE pd_end_dt >= '01-Sep-07' AND pd_end_dt < '30-Sep-07'
GROUP BY pd_end_dt

the result set on running each query will be like

09/28/2007 00:00:00 702,457.36
09/21/2007 00:00:00 703,604.59
09/07/2007 00:00:00 636,619.92
09/14/2007 00:00:00 698,082.03

similarly for previous year

I need to calculate the difference of units sold as compared to last year and also to add one column which will find percentage change

Answer 1

Hi Irveen,

if you want to compare the results of the query year-on-year (ie for each day with the day of the preceeding year), you can group by the day of the year to_char('dd-mon'):

SQL> WITH lnd_wkly_plan_rx_summary AS (
  2  SELECT DATE '2007-09-28' pd_end_dt, 702457.36 nrx_cnt FROM dual
  3  UNION ALL SELECT DATE '2007-09-21', 703604.59 FROM dual
  4  --
  5  UNION ALL SELECT DATE '2008-09-28' pd_end_dt, 702557.36 nrx_cnt FROM dual
  6  UNION ALL SELECT DATE '2008-09-21', 703404.59 FROM dual
  7  )
  8  SELECT to_char(pd_end_dt, 'dd-mon') pd_end_dt,
  9         SUM(CASE
 10                WHEN to_char(pd_end_dt, 'yyyy') = '2007' THEN
 11                 nrx_cnt
 12             END) Total_2007,
 13         SUM(CASE
 14                WHEN to_char(pd_end_dt, 'yyyy') = '2008' THEN
 15                 nrx_cnt
 16             END) Total_2008,
 17         SUM(CASE
 18                WHEN to_char(pd_end_dt, 'yyyy') = '2008' THEN
 19                 nrx_cnt
 20                ELSE
 21                 -nrx_cnt
 22             END) delta
 23    FROM lnd_wkly_plan_rx_summary
 24   WHERE ((pd_end_dt >= DATE '2007-09-01' AND pd_end_dt < DATE '2007-09-30') OR
 25         (pd_end_dt >= DATE '2008-09-01' AND pd_end_dt < DATE '2008-09-30'))
 26   GROUP BY to_char(pd_end_dt, 'dd-mon');

PD_END_DT    TOTAL_2007 TOTAL_2008      DELTA
------------ ---------- ---------- ----------
28-sep        702457,36  702557,36        100
21-sep        703604,59  703404,59       -200

Answer 2

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:

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.

And your original queries, slightly modified.

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.

And the query with which you can compare the 2007 and 2008 data:

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.

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:

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.

Hope this helps.

Regards, Rob.

Answer 3

Why dont you look at the analytic functions provided with Oracle? I am assuming you are using Oracle as you have tagged your question with the Oracle tag. You can refer to http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

I am simplifying your data set to look like this

09/08/2007 100

09/08/2008 200

09/09/2007 350

09/09/2008 400

09/10/2007 150

09/10/2008 175

These are your total counts on the 8,9 and 10th of September in the years 2007 and 2008

you can use the following query:

Assuming table to be T(end_date,cnt) (your names are too long! sorry )

Select end_date, cnt,
       LAG(cnt,1,0) over (order by to_number(to_char(end_dt,'dd')),to_number(to_char(end_dt,'mm'))) cntPrev,
       cnt - LAG(cnt,1,0) over (order by to_number(to_char(end_dt,'dd')),to_number(to_char(end_dt,'mm'))) cntDiff
from T

In simpler terms(this will not work if you copy, paste)

Let X=LAG(cnt,1,0) over (order by to_number(to_char(end_dt,'dd')),to_number(to_char(end_dt,'mm')))

your query is

Select end_date, cnt, X cntPrev, cnt-X cntDiff from T;