• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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

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
goldieretriever
Asked:
goldieretriever
1 Solution
 
flow01Commented:
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
 
Geert GruwezOracle dbaCommented:
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
 
CarlsbergFTWCommented:
Is this select generated using hyperion reporting studio ? If so you can do the filtering in the results.

Regards.
0
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!

 
awking00Commented:
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
 
goldieretrieverAuthor Commented:
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
 
awking00Commented:
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
 
goldieretrieverAuthor Commented:
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
 
awking00Commented:
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
 
goldieretrieverAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
awking00Commented:
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
 
goldieretrieverAuthor Commented:
Thank you all for your help!
0
 
awking00Commented:
You're welcome. Glad to have helped.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now