Data Joins and Pivot Help

684479 · Jan 22, 2010 2:18PM

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

Frank Kulash · Jan 22, 2010 4:04PM

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.

Anurag Tibrewal · Jan 22, 2010 2:33PM

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

fsitja · Jan 22, 2010 2:41PM

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.

Solomon Yakobson · Jan 22, 2010 2:47PM

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 · Jan 22, 2010 2:55PM

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 · Jan 22, 2010 2:58PM

Agreed Frank,
You are really a guru.

Regards
Anurag

684479 · Jan 22, 2010 3:04PM

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 · Jan 22, 2010 3:07PM

No, one rcord for each month but sometimes the month end dates got screwed up (not month and year they are fine).

684479 · Jan 22, 2010 3:35PM

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 · Jan 22, 2010 4:04PM

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 · Jan 22, 2010 4:46PM

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,

Forum Stats

Quick Links

Categories

Data Joins and Pivot Help

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Data Joins and Pivot Help

Best Answer

Answers