Data Joins and Pivot Help

Frank Kulash

Hi,

Sorry, I had the dates backwards.
Swap all references to a.date_changed and b.date_upload in sub-query got_priormonth:

WITH	got_priormonth		AS
(
	SELECT	a.acct
	,	b.score
	,	MONTHS_BETWEEN ( TRUNC (a.date_changed,  'MONTH')
			       , TRUNC (b.date_upload,   'MONTH')
			       )	AS priormonth
	FROM	tableA  a
	JOIN	tableB	b	ON	a.acct	= b.acct
	WHERE	b.date_upload	>= ADD_MONTHS  ( TRUNC (a.date_changed, 'MONTH')
				  	       , -2
					       )
	AND	b.date_upload   <                TRUNC (a.date_changed, 'MONTH')
)
SELECT	  acct
,	  MAX (CASE WHEN priormonth = 2 THEN score ELSE 0 END)	AS score2monthprior
,	  MAX (CASE WHEN priormonth = 1 THEN score ELSE 0 END)	AS score1monthprior
FROM	  got_priormonth
GROUP BY  acct
;

Output, using Solomon's data:

.     ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR
---------- ---------------- ----------------
       123              620              650
       456              630              680

684479

Perfect!

Thanks, Frank.

Aketi Jyuuzou

Hehe I like Pivot B-)
But recently,I think that group by and max and decode is sometimes readable than Pivot.
Because Pivot do implicit group by and we must image it implicit group by

with a as (
select 123 acct,to_date('1/12/2008','mm/dd/yyyy') date_changed from dual union all
select 456,to_date('8/25/2008','mm/dd/yyyy') from dual),
b as (
select 123 acct,to_date('11/30/2007','mm/dd/yyyy') date_upload,620 score from dual union all
select 123,to_date('12/31/2007','mm/dd/yyyy'),650 from dual union all
select 123,to_date('01/31/2008','mm/dd/yyyy'),712 from dual union all
select 123,to_date('02/29/2008','mm/dd/yyyy'),800 from dual union all
select 456,to_date('05/31/2008','mm/dd/yyyy'),520 from dual union all
select 456,to_date('06/30/2008','mm/dd/yyyy'),630 from dual union all
select 456,to_date('07/31/2008','mm/dd/yyyy'),680 from dual union all
select 456,to_date('08/31/2008','mm/dd/yyyy'),710 from dual union all
select 456,to_date('09/30/2008','mm/dd/yyyy'),780 from dual union all
select 456,to_date('10/31/2008','mm/dd/yyyy'),786 from dual union all
select 456,to_date('11/30/2008','mm/dd/yyyy'),823 from dual)
select Acct,Score2MonthPrior,Score1MonthPrior
from (select a.acct,b.score,
       extract( year from a.date_changed)*12
      +extract(month from a.date_changed)
      -extract( year from b.date_upload)*12
      -extract(month from b.date_upload) as diff
      from a join b
        on a.acct = b.acct)
Pivot (max(score) for diff in(2 as Score2MonthPrior,
                              1 as Score1MonthPrior));

ACCT  Score2MonthPrior  Score1MonthPrior
----  ----------------  ----------------
 123               620               650
 456               630               680

Aketi Jyuuzou

I like this query more than my previous :8}

select acct,
max(decode(diff,2,score)) as Score2MonthPrior
max(decode(diff,1,score)) as Score1MonthPrior,
from (select a.acct,b.score,
      months_between(trunc(a.date_changed,'mm'),
                     trunc(b.date_upload,'mm')) as diff
        from a join b
          on a.acct = b.acct)
 where diff in(1,2)
group by acct;

acct  Score2MonthPrior  Score1MonthPrior
----  ----------------  ----------------
 123               620               650
 456               630               680