Forum Stats

  • 3,853,805 Users
  • 2,264,277 Discussions
  • 7,905,450 Comments

Discussions

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
  • 684479
    684479 Member Posts: 30
    Perfect!

    Thanks, Frank.
  • 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
  • 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
This discussion has been closed.