need sql query to create join on the same table

Question

I have a table lnd_wkly_plan_rx_summary and columns pd_end_dt, nrx_cnt containing data

PD_END_DT    NRX_CNT
12/26/08    1,178.75
12/19/08    2,027.12
12/12/08    1,907.08
12/05/08    2,092.90
11/28/08    1,236.44
11/21/08    1,857.82
11/14/08    1,817.55
11/07/08    1,800.54
10/31/08    1,985.13

i need to put a join on same table lnd_wkly_plan_rx_summary
and compare data of PD_END_DT,NRX_CNT weekly    
so dat i can get difference of NRX_CNT as zero

Final table will contain following columns

PD_END_DT NRX_CNT DELTA

---

snapshot of the table is

   MKT_ID   PROD_GRP_NBR CHNL_CD PERIOD_FILE_TYPE PD_END_DT NRX_CNT NRX_QTY
    02601   00000020          1 W 10/12/07 2,041.64 9,706.23
    02601   00000020          1 W 10/05/07 2,122.89 10,593.91
    02601   00000020          1 W 09/28/07 2,072.10 10,247.32
    02601   00000020          1 W 09/21/07 2,070.78 10,042.91
    02601   00000020          1 W 09/14/07 2,120.01 10,493.93
    02601       00000020          1 W 09/07/07 1,828.18 8,867.92
    02601   00000020          1 W 08/31/07 2,294.99 11,115.15
    02601   00000020          1 W 08/24/07 2,133.24 10,434.83
    02601     00000020           1 W 08/17/07 2,186.42 10,739.04

CHNL_CD VARIES AS 1 OR 2 PROD_GRP_NBR 20-200 PERIOD_FILE_TYPE IS W

.........This is just first step to get to the final result............

Answer 1

If you want to find any combination of weeks that have the same NRX_CNT (difference = 0), I would go with something similar to this:

SELECT * 
FROM lnd_wkly_plan_rx_summary as t1
INNER JOIN lnd_wkly_plan_rx_summary as t2 on t1.nrx_cnt = t2.nrx_cnt

Answer 2

Which db type you are using? SQL Server 2005?

select A.PD_END_DT as 'LAST_PD_END_DT', A.NRX_CNT - B.NRX_CNT as 'DELTA' from lnd_wkly_plan_rx_summary as A inner join lnd_wkly_plan_rx_summary as B on A.PD_END_DT =dateadd(d, 7, B.PD_END_DT)

Answer 3

Hi, what I'm missing here is a citeriea to join the table whith itself. Anyhow - if there is no criteria you will get for every PD_END_DT a set of records for every record in that table which shows the difference The query looks like this: SELECT A.PD_END_DT, A.NRX_CNT, A.NRX_CNT - B.NRX_CNT as DELTA FROM lnd_wkly_plan_rx_summary A, lnd_wkly_plan_rx_summary B

I you would have a "joinable field" it would look like SELECT A.PD_END_DT, A.NRX_CNT, A.NRX_CNT - B.NRX_CNT as DELTA FROM lnd_wkly_plan_rx_summary A INNER JOIN lnd_wkly_plan_rx_summary B ON A.JoinableField=B.JoinableField

Answer 4

If you want to compare the NRX_CNT column to the preceeding week, you can use an analytic function, it will be faster and clearer than a self-join:

SQL>  SELECT pd_end_dt, nrx_cnt,
  2         nrx_cnt - lag(nrx_cnt) over (ORDER BY pd_end_dt) delta
  3    FROM lnd_wkly_plan_rx_summary
  4  ORDER BY pd_end_dt;

PD_END_DT      NRX_CNT      DELTA
----------- ---------- ----------
31/10/2008     1985,13 
07/11/2008     1800,54    -184,59
14/11/2008     1817,55      17,01
21/11/2008     1857,82      40,27
28/11/2008     1236,44    -621,38
05/12/2008      2092,9     856,46
12/12/2008     1907,08    -185,82
19/12/2008     2027,12     120,04
26/12/2008     1178,75    -848,37