SQL Add Fiscal Year Column

I using the script below and would like to add a FY Column based on C_COMPLETE_REQUEST_RECVD_DT.
The FYs are
FY2002 Between 10/1/2001 and 9/30/2002
FY2015 Between  10/1/2014 and 9/30/2015
FY2016 Between  10/1/2015 and 9/30/2016
FY2017 Between  10/1/2016 and 9/30/2017
FY2018 Between  10/1/2017 and 9/30/2018
FY2019 Between  10/1/2018 and 9/30/2019

Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"


From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

shieldscoAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
Okay, this should work for the statement we added...

,Case WHEN EXTRACT(MONTH FROM C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN EXTRACT(YEAR FROM C_COMPLETE_REQUEST_RECVD_DT) ELSE EXTRACT(YEAR FROM C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year

Open in new window


»bp
0
 
NorieVBA ExpertCommented:
Is C_COMPLETE_REQUEST_RECVD_DT a field?

Which table is it in?
0
 
shieldscoAuthor Commented:
Yes
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Bill PrewCommented:
Try adding this for the fiscal year field:

SELECT CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() ELSE YEAR()+1 END AS Fiscal_Year

Open in new window


»bp
0
 
shieldscoAuthor Commented:
java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"
, CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() ELSE YEAR()+1 END AS Fiscal_Year



From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
 
Bill PrewCommented:
SELECT CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR(C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year

Open in new window


»bp
0
 
shieldscoAuthor Commented:
Bill java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier
0
 
Bill PrewCommented:
See my last comment, I forgot to pass the field to YEAR(), sorry...


»bp
0
 
shieldscoAuthor Commented:
java.sql.SQLException: ORA-03001: unimplemented feature

,CASE WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR()(C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR()(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year
0
 
Bill PrewCommented:
You have a typo, please check my prior post and compare to yours, you have extra parens after the YEAR function.


»bp
0
 
shieldscoAuthor Commented:
java.sql.SQLException: ORA-03001: unimplemented feature



,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year
0
 
Bill PrewCommented:
You still have a typo...

,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR() (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year


»bp
0
 
shieldscoAuthor Commented:
java.sql.SQLSyntaxErrorException: ORA-00904: "YEAR": invalid identifier
,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year
0
 
Bill PrewCommented:
Well, YEAR() is a valid Oracle SQL function, so not sure the exact context of the query.  Or if the line break after YEAR could be causing a problem.  How are you executing this from Java?


»bp
0
 
shieldscoAuthor Commented:
Anybody with other thoughts
0
 
NorieVBA ExpertCommented:
shieldsco

You need to tell us how you are executing the query, without that information it's just kind of guesswork.:)
0
 
shieldscoAuthor Commented:
I'm executing from entellitrak platform (https://www.micropact.com/products/entellitrak/)
0
 
Bill PrewCommented:
I'd suggest you connect to the database via SQL Developer, or TOAD, and get your query working first.  Then adapt it for the delivery environment as needed.


»bp
0
 
shieldscoAuthor Commented:
I have run from SQL developer with the same error
0
 
Bill PrewCommented:
Please post the full query you are executing.


»bp
0
 
shieldscoAuthor Commented:
Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as Appeal_Age
, W.C_NAME as "Status"
,Case WHEN MONTH(C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN YEAR (C_COMPLETE_REQUEST_RECVD_DT) ELSE YEAR(C_COMPLETE_REQUEST_RECVD_DT)+1 END AS Fiscal_Year


From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
 
shieldscoAuthor Commented:
New Script
I'm getting the following error java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
on this line ,TO_CHAR(C_COMPLETE_REQUEST_RECVD_DT, 'YY') < 10 Then (C_COMPLETE_REQUEST_RECVD_DT, 'YY') Else (C_COMPLETE_REQUEST_RECVD_DT, 'YYYY')+1 AS "FY"

Select distinct

C_ALJ_APPEAL_NUM as "ALJ Appeal Num"
,C_RECONSIDER_NUM as "Reconsider Num"
,MT.C_Name as  "Medicare Type"
,C_L2_APPEAL_CATEGORY as "Appeal Category"
,C_CLAIM_CNT as "Claim Cnt"
,C_REQUEST_RECV_DT as "Request Recv Dt"
,C_COMPLETE_REQUEST_RECVD_DT as "Complete Request Recvd Dt"
,C_RAC_REGION as "RAC Region"
,To_date(CURRENT_DATE) - to_date(C_REQUEST_RECV_DT) as "Appeal_Age"
, W.C_NAME as "Status"
,TO_CHAR(C_COMPLETE_REQUEST_RECVD_DT, 'YY') < 10 Then (C_COMPLETE_REQUEST_RECVD_DT, 'YY') Else (C_COMPLETE_REQUEST_RECVD_DT, 'YYYY')+1 AS "FY"



From ((T_APL inner join 
T_MEDICARE_TYPE MT on C_MEDICARE_TYPE = MT.id)
inner join T_APL_CLAIM AC on AC.ID_Parent = T_APL.ID)
inner join T_RF_STATE W on t_apl.C_WORKFLOW_STATE = W.ID

Open in new window

0
 
shieldscoAuthor Commented:
Thanks very much... good job
0
 
Bill PrewCommented:
Welcome, glad we got that one.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.