Forum Stats

  • 3,837,625 Users
  • 2,262,276 Discussions
  • 7,900,331 Comments

Discussions

Data Joins and Pivot Help

684479
684479 Member Posts: 30
edited Jan 27, 2010 6:02AM in SQL & PL/SQL
Hi, I have one fact table and one huge history table that goes really deep with scores stored with month end dates.

I am looking for a way to manipulate the month and year of DateChanged in table A along with joins on the acct no to retrieve scores from previous months, resulting in a de-normalized data format as in the result table.

Any ideas?? Thanks!


Table A

Acct	DateChanged
-----------------------------------
123	1/12/2008
456	8/25/2008
 


Table B:
 
Acct           DateUpload        Score
-----------------------------------------
123           11/30/2007        620
123           12/31/2007        650
123           01/31/2008        712
123           02/29/2008        800
456           05/31/2008        520  
456           06/30/2008        630
456           07/31/2008        680
456           08/31/2008        710
456           09/30/2008        780
456           10/31/2008        786
456           11/30/2008        823



Result Table:
		        
Acct      Score2MonthPrior     Score1MonthPrior
-------------------------------------------------------
123            620                     650
456            630                     680

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,219 Red Diamond
    Answer ✓
    Hi,

    Whenever you have a question, post a little sample data in a form people can actually use. CREATE TABLE and INSERT statements are best. A WITH clause, like Solomon posted, is better than nothing. Without that, people will help you as much as they can, but don't expect them to test anything.

    Using Solomon's data, I was able to test (and correct) my query.
    WITH	got_priormonth		AS
    (
    	SELECT	a.acct
    	,	b.score
    	,	MONTHS_BETWEEN ( TRUNC (b.date_upload,  'MONTH')
    			       , TRUNC (a.date_changed, 'MONTH')
    			       )	AS priormonth	-- Alias added
    	FROM	tableA  a
    	JOIN	tableB	b	ON	a.acct	= b.acct
    	WHERE	a.date_changed	>= ADD_MONTHS  ( TRUNC (b.date_upload, 'MONTH')
    				  	       , -2
    					       )
    	AND	a.date_changed  <                TRUNC (b.date_upload, '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
    ;
    ORA-00907 really means "absolutely clue-free regarding what you meant".
    Perhaps a missing right parenthesis causes the error as much as anything, but when you get that error, look for any kind of syntax error at (or slightly before) the point indicated.

    You're right, I did forget the column alias. Sorry; if I could have tested it before posting, I would have.
«1

Answers

  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    Hi,

    One way
    with t1 as
    (
    	select 	a.acct
    		, b.score
    		, to_char(a.date_changed,'MM') - to_char(b.dateupload,'MM') priormonth
    	from tableA a, tableB b
    	where 	a.acct=b.acct
    		and (to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM') +1
    			or to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM')+2)
    )
    select 	t1.acct
    	, max(decode(priormonth,1, b.score,0)) score1priormonth
    	, max(decode(priormonth,2,b.score,0)) score1priormonth
    from t1 group by acct
    Regards
    Anurag
    Anurag Tibrewal
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Jan 22, 2010 2:51PM
    SELECT acct, score2monthprior, score1monthprior
      FROM (SELECT a.*,
                   b.dateupload,
                   b.score,
                   lag(b.dateupload) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) dt_prv_upload,
                   lag(b.score) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) score1monthprior,
                   lag(b.score, 2) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) score2monthprior
              FROM table_a a
              JOIN table_b b
                ON a.acct = b.acct
             ORDER BY b.dateupload)
     WHERE datechanged BETWEEN dt_prv_upload AND dateupload
    Can you have more than one record on table_b for each month? If so it won't work as this. I'm simply looking up the record from the previous month here.
    fsitja
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,580 Red Diamond
    If you understood your reqs correctly, it should be:
    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
              ),
          c as (
                select  acct,
                        date_upload,
                        score,
                        lead(date_upload,1,sysdate) over(partition by acct order by date_upload) next_date_upload
                  from  b
               )
    select  acct,
            (select score from c where a.acct = c.acct and add_months(date_changed,-2) >= date_upload and add_months(date_changed,-2) < next_date_upload) Score2MonthPrior,
            (select score from c where a.acct = c.acct and add_months(date_changed,-1) >= date_upload and add_months(date_changed,-1) < next_date_upload) Score1MonthPrior
      from  a
    /
    
          ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR
    ---------- ---------------- ----------------
           123                               620
           456              520              630
    
    SQL> 
    Two months prior 1/12/2008 is 11/12/2007 which falls before the earliest record for acct 123. One month prior 1/12/2008 is 12/12/2007 which falls between 11/30/2007 and 12/31/2007 and therefore score should be 620, not 650.

    SY.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,219 Red Diamond
    edited Jan 22, 2010 3:57PM
    Hi,
    Anurag Tibrewal wrote:
    Hi,

    One way
    with t1 as
    (
    	select 	a.acct
    		, b.score
    		, to_char(a.date_changed,'MM') - to_char(b.dateupload,'MM') priormonth
    	from tableA a, tableB b
    	where 	a.acct=b.acct
    		and (to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM') +1
    			or to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM')+2)
    )
    select 	t1.acct
    	, max(decode(priormonth,1, b.score,0)) score1priormonth
    	, max(decode(priormonth,2,b.score,0)) score1priormonth
    from t1 group by acct
    Regards
    Anurag
    Right idea. I'm sure if OP had posted some sample data, then you would have tested it and found the mistakes.

    We don't want to use TO_CHAR here. If this is run in month X, then we usually want months X-2 and X-1, but not early in the year. Right now, the month i s '01', but the months wanted would be '11' and '12', ,not '-1' and '00'.

    Also, table alias b has no meaning in the main query.

    I think you meant something like:
    WITH	got_priormonth		AS
    (
    	SELECT	a.acct
    	,	b.score
    	,	MONTHS_BETWEEN ( TRUNC (b.date_upload,  'MONTH')
    			       , TRUNC (a.date_changed, 'MONTH')
    			       )	AS priormonth	-- Alias added
    	FROM	tableA  a
    	JOIN	tableB	b	ON	a.acct	= b.acct
    	WHERE	a.date_changed	>= ADD_MONTHS  ( TRUNC (b.date_upload, 'MONTH')
    				  	       , -2
    					       )
    	AND	a.date_changed  <                TRUNC (b.date_upload, '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
    ;
    Edited by: Frank Kulash on Jan 22, 2010 3:00 PM
    Changed SYSDATE to b.date_upload

    Edited by: Frank Kulash on Jan 22, 2010 3:57 PM
    Fixed several typos
  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    Agreed Frank,
    You are really a guru.

    Regards
    Anurag
  • 684479
    684479 Member Posts: 30
    I got a ORA-00904 error indicating b.score invalid.

    I also changed the second score1priormonth to score2priormonth, don't we need to look at year as well? Thanks,
  • 684479
    684479 Member Posts: 30
    No, one rcord for each month but sometimes the month end dates got screwed up (not month and year they are fine).
  • 684479
    684479 Member Posts: 30
    Hi Frank, I keep getting a missing right parenthesis message but I think all of them are accounted for. We need to name month_between calculation as priormonth correct?
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,219 Red Diamond
    Answer ✓
    Hi,

    Whenever you have a question, post a little sample data in a form people can actually use. CREATE TABLE and INSERT statements are best. A WITH clause, like Solomon posted, is better than nothing. Without that, people will help you as much as they can, but don't expect them to test anything.

    Using Solomon's data, I was able to test (and correct) my query.
    WITH	got_priormonth		AS
    (
    	SELECT	a.acct
    	,	b.score
    	,	MONTHS_BETWEEN ( TRUNC (b.date_upload,  'MONTH')
    			       , TRUNC (a.date_changed, 'MONTH')
    			       )	AS priormonth	-- Alias added
    	FROM	tableA  a
    	JOIN	tableB	b	ON	a.acct	= b.acct
    	WHERE	a.date_changed	>= ADD_MONTHS  ( TRUNC (b.date_upload, 'MONTH')
    				  	       , -2
    					       )
    	AND	a.date_changed  <                TRUNC (b.date_upload, '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
    ;
    ORA-00907 really means "absolutely clue-free regarding what you meant".
    Perhaps a missing right parenthesis causes the error as much as anything, but when you get that error, look for any kind of syntax error at (or slightly before) the point indicated.

    You're right, I did forget the column alias. Sorry; if I could have tested it before posting, I would have.
  • 684479
    684479 Member Posts: 30
    I moved the join condition inside of the where clause and it worked (not sure why "on" would not work and give that Alicia Silverstone Clueless error).

    Few issues though:

    I get scores from months after change date not prior, two months prior is two months after score and one month prior is one month after. I looked at the syntax can't see why.

    I missed some records in table A, is there anyway all the records from base table are returned?

    Thanks,
This discussion has been closed.