Forum Stats

  • 3,853,797 Users
  • 2,264,275 Discussions
  • 7,905,449 Comments

Discussions

Categories

Data Joins and Pivot Help

2»

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,497 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
    · Share on TwitterShare on Facebook
  • 684479
    684479 Member Posts: 30
    Perfect!

    Thanks, Frank.
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou 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
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou 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
    Aketi Jyuuzou
    · Share on TwitterShare on Facebook
This discussion has been closed.