현재 회계 연도부터 sysdate까지 (Current Financial Year to sysdate)

문제 설명

현재 회계 연도부터 sysdate까지 (Current Financial Year to sysdate)

항상 현재 회계 연도를 가져오기 위해 PL SQL을 조사하지 않고 간단한 SQL을 찾을 수 없는 것 같습니다. 이 경우 01‑04‑2015를 sysdate로 가져옵니다. 이것이 항상 자동으로 업데이트되기를 원하므로 2016년 1월 4일 회계 연도가 되면 해당 날짜부터 보고서가 실행될 때까지 보류된 모든 항목을 가져올 것입니다.

누군가가 있으면 일부를 흘려주세요. 나를 위한 빛. 감사합니다

sql은:

SELECT
  PROPERTY.PRO_MANAGINGCOMPANY_DESCR,
  PROPERTY.PRO_SCHEME_DESCR,
  PROPERTY.PRO_SCHEME,
  SUM(REPAIR_CURRENT.REP_ESTIMATED_COST) as "Estimated Cost",
  nvl(SUM(REPAIR_CURRENT.REP_INVOICED_COST),SUM(REPAIR_CURRENT.REP_ESTIMATED_COST)) as "Estimated Cost Invoiced",
  SUM(REPAIR_CURRENT.REP_INVOICED_COST) as "Invoice Cost",
  to_char(REPAIR_CURRENT.REP_RAISED_DATE,'Mon') as "Month",
  to_number(to_char(to_date(REPAIR_CURRENT.REP_RAISED_DATE,'dd‑mon‑yy'),'mm')) as "Month No."
FROM
 PROPERTY,
  REPAIR_CURRENT,
  SERVICE_REQUEST
WHERE
  ( SERVICE_REQUEST.SRQ_PRO_REFNO=PROPERTY.PRO_REFNO  )
  AND  ( REPAIR_CURRENT.REP_SRQ_NO=SERVICE_REQUEST.SRQ_NO  )
  AND  
  (
  ‑‑PROPERTY.PRO_SCHEME  =   ( '00054'  )
   ‑‑AND
   REPAIR_CURRENT.REP_RAISED_DATE  BETWEEN  '01‑APR‑2015'  AND  sysdate
   AND
   REPAIR_CURRENT.REP_STATUS  <>  'CAN'
  )
GROUP BY
  PROPERTY.PRO_MANAGINGCOMPANY_DESCR,
  PROPERTY.PRO_SCHEME_DESCR,
  PROPERTY.PRO_SCHEME,
  to_char(REPAIR_CURRENT.REP_RAISED_DATE,'Mon'),
  to_number(to_char(to_date(REPAIR_CURRENT.REP_RAISED_DATE,'dd‑mon‑yy'),'mm')) 

참조 솔루션

방법 1:

If you just want to get the beginning of the fiscal year for the current date:

SELECT TO_DATE('01‑04' || CASE 
                            WHEN EXTRACT(MONTH FROM SYSDATE) > 4 THEN 
                              EXTRACT(YEAR FROM SYSDATE) 
                            ELSE 
                              EXTRACT(YEAR FROM SYSDATE)‑1 
                            END, 'DD‑MM‑RRRR') FISCAL_YEAR 
FROM DUAL

방법 2:

This works for any date:

REPAIR_CURRENT.REP_RAISED_DATE
  BETWEEN Add_Months(Trunc(Add_Months(sysdate,‑3),'YYYY'),3)
      AND Sysdate

Basically, subtract three months, truncate to the year, and add three months back on.

To just get the financial year for a date, use:

Extract(Year from Add_Months(Trunc(Add_Months(sysdate,‑3),'YYYY'),3))

방법 3:

SELECT *
FROM   your_table
WHERE  datetime >= CASE
                   WHEN SYSDATE < TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '3' MONTH 
                   THEN TRUNC( SYSDATE, 'YEAR' ) ‑ INTERVAL '9' MONTH
                   ELSE TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '3' MONTH
                   END;

방법 4:

Thank you, the following worked! add_months(trunc(sysdate,'year'),3) AND sysdate

thank you all for your input :)

방법 5:

REPAIR_CURRENT.REP_RAISED_DATE BETWEEN '01‑APR‑2015' AND sysdate

Firstly, '01‑APR‑2015' is not a DATE it is a string. You must always use TO_DATE along with proper format model to explicitly convert the string into DATE. Or, use the ANSI Date literal as you are not concerned with the time portion. It uses a fixed format 'YYYY‑MM‑DD'.

Now, coming to your date arithmetic, you could use a CASE expression to evaluate the financial date depending on the year.

REP_RAISED_DATE 
BETWEEN
   CASE
   WHEN 
      SYSDATE <  ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3) 
   THEN
      ADD_MONTHS(TRUNC(SYSDATE, 'YEAR') , ‑9)
   ELSE 
      ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3)
   END 
AND SYSDATE

Basically, SYSDATE >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3) is to check whether SYSDATE is greater than 1‑APR of current year. And, SYSDATE < ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 15) is to check whether it is between JAN and MARCH of next year.

For example,

SQL> SELECT
  2     CASE
  3       WHEN
  4          SYSDATE <  ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3)
  5       THEN
  6          ADD_MONTHS(TRUNC(SYSDATE, 'YEAR') ,‑9)
  7       ELSE
  8          ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3)
  9     END FINANCIAL_YEAR
 10  FROM dual;

FINANCIAL
‑‑‑‑‑‑‑‑‑
01‑APR‑15

For date between JAN and MAR of next year:

SQL> SELECT
  2     CASE
  3       WHEN
  4          DATE '2016‑02‑01' <  ADD_MONTHS(TRUNC(DATE '2016‑02‑01', 'YEAR'), 3)
  5       THEN
  6          ADD_MONTHS(TRUNC(DATE '2016‑02‑01', 'YEAR') ,‑9)
  7       ELSE
  8          ADD_MONTHS(TRUNC(DATE '2016‑02‑01', 'YEAR'), 3)
  9     END FINANCIAL_YEAR
 10  FROM dual;

FINANCIAL
‑‑‑‑‑‑‑‑‑
01‑APR‑15

(by abs786123、pablomatico、David Aldridge、MT0、abs786123、Lalit Kumar B)

참조 문서

1. Current Financial Year to sysdate (CC BY‑SA 2.5/3.0/4.0)