converting Calendar year to Fiscal Year 1

[mdlaugh1]

Hello,

I am attempting to group transactions by month. However we are on a fiscal year with October being Period 1. I'm a beginner when it comes to Oracle so please bear with me.


I'm using the following code, which groups and handles the months correctly, making OCT period 01. However it is taking the YEAR component as the calendar year value. I need to modify statement that if Month is Oct - Dec, then add 1 to the YEAR. Else use the YEAR out of the actual_finish_date value:

"to_number(to_char(wot.actual_finish_date,'YYYY')||decode(to_char(wot.actual_finish_date,'MON'),
'OCT','01','NOV','02','DEC','03','JAN','04',
'FEB','05','MAR','06','APR','07','MAY','08',
'JUN','09','JUL','10','AUG','11','SEP','12'))Actual_Finish_Period"

My results of this are not correct -- Actual_Finish_Period for OCT - DEC should read 200601,200602, 200603 not 200501, etc:

Actual_Finish_Date Actual_Finish_Period
20051011 200501
20051118 200502
20051230 200503
20060115 200604

I've tried adding 1 to YEAR as but that did't work:

"to_number(to_char(wot.actual_finish_date + 1,'YYYY')||decode(to_char(wot.actual_finish_date,'MON'),
'OCT','01'.........

Thank you!!

 

[SantaMufasa]

MDLaugh,

Oracle has the most amazing DATE HANDLING capabilities...more amazing than any other vendor's functionality (at least that I am aware of).

Here are the sample data you posted earlier:

select * from wot;

ACTUAL_FINISH_DATE
------------------
11-OCT-05
18-NOV-05
30-DEC-05
15-JAN-06

Here is very simple code to achieve the results you want:

select to_char(actual_finish_date,'yyyymmdd')actual_finish_date
      ,to_char(add_months(trunc(actual_finish_date),3),'YYYYMM')actual_finish_period
from wot;

ACTUAL_FINISH_DATE   ACTUAL_FINISH_PERIOD
-------------------- --------------------
20051011             200601
20051118             200602
20051230             200603
20060115             200604

Since your fiscal year is three months ahead of the calendar year, this code correctly computes the "ACTUAL_FINISH_PERIOD" by

1) trimming the "ACTUAL_FINISH_DATE" to the first day of its month ("trunc(actual_finish_date)"), then
2) adding three months to that trimmed date, then
3) displaying the year and month of that date three months in the future.

Let us know if this satisfies your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mdlaugh1]

Hi SantaMufasa!

Well... it's almost there! I've listed 3 items:

#1 (your code) works... This code does not use aliases and selects directly from the sa_work_order_task table.

"select
to_char(actual_finish_date,'yyyymmdd')actual_finish_date,
to_char(add_months(trunc(actual_finish_date),3),'YYYYMM')
actual_finish_period
from sa_work_order_task"



#2. - my actual code does not work, uses aliases because the entire code is joining 3 tables or views. This segment selects from a view.... I get 'FROM keyword not where expected..."

"select
to_char(wot.actual_finish_date,'yyyymmdd')wot.actual_finish_date,
to_char(add_months(trunc(wot.actual_finish_date),3),'YYYYMM')
actual_finish_period
from sv_work_order_and_task wot"



#3 - this is the entire code for creating this new view in which I get the "FROM keyword not where expected":

CREATE OR REPLACE VIEW CV_WO_CONCRETE_COST
(PLANT, WORK_ORDER_NO, WORK_ORDER_TASK_NO, TOTAL_ACTUAL_AMOUNT, TYPE_OF_WORK_AMOUNT,
ACTUAL_FINISH_DATE, ACTUAL_FINISH_PERIOD, WORK_CLASS, WORK_CATEGORY, TYPE_OF_WORK,
TYPE_OF_WORK_UNITS, CREW)
AS
SELECT
wot.plant,
wot.work_order_no,
wot.work_order_task_no,
sum(woc.actual_amount) total_actual_amount,
wot.type_of_work_amount,
to_number(to_char(wot.actual_finish_date,'YYYY')),

to_char(wot.actual_finish_date,'yyyymmdd')wot.actual_finish_date,
to_char(add_months(trunc(wot.actual_finish_date),3),'YYYYMM')
actual_finish_period,

wot.work_class,
wot.work_category,
wot.type_of_work,
wt.type_of_work_units,
wot.crew

FROM
sv_work_order_and_task wot, sv_work_order_task_cost woc, sa_work_order_task wt
WHERE
wot.plant = woc.plant
AND
wot.work_order_no = woc.work_order_no
AND
wot.work_order_task_no = woc.work_order_task_no
AND
wot.work_order_no = wt.work_order_no
AND
wot.work_order_task_no = wt.work_order_task_no
AND
wot.actual_finish_date IS NOT NULL
AND
wot.type_of_work in( '*6"PCRP', '*7"PCRP','*8"PCRP')
AND
wot.crew in ('806', '807', '808', '809')
AND
woc.actual_amount > 0

GROUP BY
wot.plant, wot.work_order_no, wot.work_order_task_no, wot.type_of_work_amount, wot.Actual_Finish_date, wot.work_class, wot.work_category, wot.type_of_work, wt.type_of_work_units, wot.crew;

 

[mdlaugh1]

SantaMufasa..
Pls disregard my last post. I realized that I had an extra line of code for the actual_finish_date YYYY.

I have removed that and I was able to create the view! I'm closing the post as resolved. If I find a problem after checking it I'll repost.

thank you!!!