Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Data Joins and Pivot Help

684479
Member Posts: 30
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!
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
-
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.
Answers
-
Hi,
One waywith 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 -
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. -
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. -
Hi,Anurag Tibrewal wrote:Right idea. I'm sure if OP had posted some sample data, then you would have tested it and found the mistakes.
Hi,
One waywith 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
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 -
Agreed Frank,
You are really a guru.
Regards
Anurag -
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, -
No, one rcord for each month but sometimes the month end dates got screwed up (not month and year they are fine).
-
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?
-
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. -
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.