## Forum Stats

• 3,837,640 Users
• 2,262,276 Discussions
• 7,900,337 Comments

Discussions

# Data Joins and Pivot Help

2»

## Answers

• Member, Moderator Posts: 42,219 Red Diamond
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```
• Member Posts: 30
Perfect!

Thanks, Frank.
• Member Posts: 1,072 Bronze Badge
edited Jan 26, 2010 5:05PM
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```
• Member Posts: 1,072 Bronze Badge
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```
This discussion has been closed.