Solved

oracle SQL to join to tables and get the last record from one table and sum a dollar field

Posted on 2012-04-01
13
500 Views
Last Modified: 2012-04-03
I need to join two tables and display the last records (pd & sub_pd) from the first table while summing the dollar field.

1st tables query fields -
  PROJECT_NO  / VARCHAR (30)
  ACCOUNT_NO / VARCHAR (15)
  FY_CD / VARCHAR (6)
  PD_NO / NUMBER (5)
  SUB_PD_NO / NUMBER (5)
  YTD_DOLLARS / NUMBER (14v2)

2nd table query fields -
  ACCOUNT_NO (15)
  ACCT_DESC (25)

This query pulls all of the periods and sub periods of the year - but I would like to only pull the last period and last sub period while summing the dollar field. I cant figure out out to use the MAX command to get the last records or even if I should be using MAX -

SELECT TABLE_1.PROJECT_NO, TABLE_1.ACCOUNT_NO, TABLE_1.FY_CD, TABLE_1.PD_NO, TABLE_1.SUB_PD_NO, Sum(TABLE_1.YTD_DOLLARS) AS SUMYTD_DOLLARS

FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.ACCOUNT_NO = TABLE_2.ACCOUNT_NO

GROUP BY TABLE_1.PROJECT_NO, TABLE_1.ACCOUNT_NO, TABLE_1.FY_CD, TABLE_1.PD_NO, TABLE_1.SUB_PD_NO, TABLE_2.ACCT_NAME

HAVING (((TABLE_1.PROJECT_NO)='123456') AND ((TABLE_1.FY_CD)= TO_CHAR(SYSDATE, 'YYYY')) AND ((TABLE_2.ACCT_DESC) Like '*LETTUCE*'))
0
Comment
Question by:goldieretriever
13 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 37793900
you can user the Rank() function


SELECT *
FROM
(
SELECT T1.* , RANK() OVER (PARTITION BY PROJECT_NO, ACCOUNT_NO, FY_CD ORDER BY  PD_NO DESC, SUB_PD_NO DESC) RNK
FROM
(
SELECT TABLE_1.PROJECT_NO, TABLE_1.ACCOUNT_NO, TABLE_1.FY_CD, TABLE_1.PD_NO, TABLE_1.SUB_PD_NO, Sum(TABLE_1.YTD_DOLLARS) AS SUMYTD_DOLLARSFEI
FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.ACCOUNT_NO = TABLE_2.ACCOUNT_NO
GROUP BY TABLE_1.PROJECT_NO, TABLE_1.ACCOUNT_NO, TABLE_1.FY_CD, TABLE_1.PD_NO, TABLE_1.SUB_PD_NO, TABLE_2.ACCT_NAME
HAVING (((TABLE_1.PROJECT_NO)='123456') AND ((TABLE_1.FY_CD)= TO_CHAR(SYSDATE, 'YYYY')) AND ((TABLE_2.ACCT_DESC) Like '*LETTUCE*')) T1
)
WHERE RNK <= 2

it probably possible to get the rank earlier (even before the group by) an so having to group less records , but you try that for yourself
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 37794683
why are you using a WHERE clause in the HAVING clause ?
(you don't need all those brackets)

SELECT TABLE_1.PROJECT_NO, TABLE_1.ACCOUNT_NO, TABLE_1.FY_CD, TABLE_1.PD_NO, TABLE_1.SUB_PD_NO, Sum(TABLE_1.YTD_DOLLARS) AS SUMYTD_DOLLARS
FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.ACCOUNT_NO = TABLE_2.ACCOUNT_NO
WHERE TABLE_1.PROJECT_NO='123456'
   AND TABLE_1.FY_CD= TO_CHAR(SYSDATE, 'YYYY')
   AND TABLE_2.ACCT_DESC Like '*LETTUCE*'
GROUP BY TABLE_1.PROJECT_NO, TABLE_1.ACCOUNT_NO, TABLE_1.FY_CD, TABLE_1.PD_NO, TABLE_1.SUB_PD_NO, TABLE_2.ACCT_NAME

next use the rank function on a date field ... which is ?
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 37795948
Is this select generated using hyperion reporting studio ? If so you can do the filtering in the results.

Regards.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 32

Expert Comment

by:awking00
ID: 37796187
You show fy_cd as being a varchar2(6) datatype but are comparing it to to_char(sysdate,'yyyy') which would only produce four characters. Can you provide some sample data for the two tables and what your expected results should be?
0
 

Author Comment

by:goldieretriever
ID: 37796487
PROJECT_NO   ACCOUNT_NO   FY_CD   PD_NO   SUB_PD_NO   YTD_DOLLARS
   123456         1111-22-33       2012       2              2                  1000.11
   123456         1111-22-33       2012       2              4                  1700.12
   123456         1111-22-34       2012       3              3                  1900.18
   123456         1111-22-33       2012       3              4                  2700.12
   123456         1111-12-44       2012       3              4                  1500.87
   123456         1111-22-34       2012       3              4                  2500.22

ACCOUNT_NO       ACCT_NAME
1111-22-33           ROMAINE LETTUCE
1111-12-44           CARROTS
1111-22-34           ORGANIC LETTUCE - RED

With this data I would like to pull out account numbers 1111-22-33 and 1111-22-34 Period 3 Sub Period 4 with a total dollar of 5200.34 for project number 123456.

I've tried the Rank over partition and I get 'no rows selected' I've never used the rank command before so I will work with it.

Right now I'm just using SQL*Plus Worksheet to get the command right then I will be using the query to update a value in a old VB6 application.

Thanks for your help.
0
 
LVL 32

Expert Comment

by:awking00
ID: 37797557
select  project_no, account_no, acct_desc, fy_cd, pd_no, sub_pd_no,
sum(ytd_dollars) over (partition by project_no order by rn) sumytd_dollars from
(select t1.project_no
       ,t1.account_no
       ,t2.acct_desc
       ,t1.fy_cd
       ,t1.pd_no
       ,t1.sub_pd_no
       ,t1.ytd_dollars
       ,row_number() over (partition by t1.project_no, t1.account_no order by t1.pd_no desc, t1.sub_pd_no desc) rn
 from table_1 t1, table_2 t2
 where t1.account_no = t2.account_no
   and t2.acct_desc like '%LETTUCE%')
where rn = 1;
0
 

Author Comment

by:goldieretriever
ID: 37798107
This is close, my live data has 11 rows of data that meet the "Lettuce" criteria  with 4 account_no's
When I ran the query the output window displayed the 4 account numbers for the last period/subperiod but for each account_no, the sumytd_dollars is 18171.79 - wich is summing only the last dollar record for each account number.
 
account_no     ytd_dollars         
1111-22-33       3188.72         
1111-22-34       1741.83         
1111-22-34     13511.23         
1111-22-34     14045.77         
1111-22-34         252.17         
1111-22-34     13375.6         
1111-22-35         240.76         
1111-22-35         593.43         
1111-22-35         275.06         
1111-22-35       1539.16         
1111-22-36           68.31       

Thank you for getting me this close I will look up the over partition command but if you can see it please post any more help will be greatly appreciated.
0
 
LVL 32

Expert Comment

by:awking00
ID: 37800813
Can you post the pd_no and sub_pd_no for those records? I suspect they may either be different, which would mean the criteria to determine the selection needs to be modified, or, if they are the same, the analytic query probably needs to be changed from row_number() to rank() or dense_rank().
0
 

Author Comment

by:goldieretriever
ID: 37800925
The pd_no and sub_pd_no are all the same - pd_no = 3 sub_pd_no = 5 which are the last records posted to the database.

Thank you.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37801161
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 37801420
Hopefully, you were looking for a total of 48,832.04 -
select  distinct project_no, account_no, acct_desc, fy_cd, pd_no, sub_pd_no,
sum(ytd_dollars) over (partition by project_no order by pd_rnk, sub_rnk) sumytd_dollars from
(select t1.project_no
       ,t1.account_no
       ,t2.acct_desc
       ,t1.fy_cd
       ,t1.pd_no
       ,t1.sub_pd_no
       ,t1.ytd_dollars
       ,rank() over (partition by t1.project_no, t1.account_no order by t1.pd_no desc) pd_rnk
       ,rank() over (partition by t1.project_no, t1.account_no order by t1.sub_pd_no desc) sub_rnk
 from table_1 t1, table_2 t2
 where t1.account_no = t2.account_no
   and t2.acct_desc like '%LETTUCE%')
where pd_rnk = 1 and sub_rnk = 1;
0
 

Author Closing Comment

by:goldieretriever
ID: 37802167
Thank you all for your help!
0
 
LVL 32

Expert Comment

by:awking00
ID: 37802217
You're welcome. Glad to have helped.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle numeric condition check 4 27
oracle forms question 22 42
minium over 4 numeric columns for each row in oracle 2 29
Oracle Nested table uses ? 2 35
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question