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*'))
goldieretrieverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 GOracle 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
goldieretrieverAuthor Commented:
Thank you all for your help!
0
awking00Information Technology SpecialistCommented:
You're welcome. Glad to have helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.