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
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax Question 9 56
update based on the value on another table 8 40
Oracle Partitions. 1 20
ORACLE SQL DEVELOPER + Compare Tables 5 37
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

738 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