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
497 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
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
Is this select generated using hyperion reporting studio ? If so you can do the filtering in the results.

Regards.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:goldieretriever
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
Comment Utility
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
Comment Utility
Thank you all for your help!
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
You're welcome. Glad to have helped.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now