1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
SELECT
(to_char(iv.inventory_date,'YYYY') || (trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) ) AS tri,
(CASE TO_CHAR(trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1)
WHEN '1' THEN 'First Qtr' WHEN '2' THEN 'Second Qtr' WHEN '3' THEN
'Third Qtr' WHEN '4' THEN 'Fourth Qtr' end) AS label,
NVL(SUM(va.QUANTITY_USED),0) AS value1,
va.VESSEL_UID,
ve.VESSEL_NAME,
(to_char(iv.inventory_date,'YYYY')
|| (trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) )||':'||(CASE
TO_CHAR(trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) WHEN
'1' THEN 'First Qtr' WHEN '2' THEN 'Second Qtr' WHEN '3' THEN 'Third
Qtr' WHEN '4' THEN 'Fourth Qtr' end) as trititle
FROM
LO_VALUATION va,
LO_VESSEL ve,
lo_inventory iv
WHERE va.VESSEL_UID=ve.VESSEL_UID
and va.month=iv.inventory_month
and ve.TEU_UID in (2,6,8,12)
and iv.inventory_date BETWEEN to_date('20/09/2010','DD/MM//YYYY') AND to_date('01/10/2013','DD/MM//YYYY')
GROUP
BY (to_char(iv.inventory_date,'YYYY') ||
(trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) ), (CASE
TO_CHAR(trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) WHEN '1'
THEN 'First Qtr' WHEN '2' THEN 'Second Qtr' WHEN '3' THEN 'Third
Qtr' WHEN '4' THEN 'Fourth Qtr' end),
va.VESSEL_UID,
ve.VESSEL_NAME,
(to_char(iv.inventory_date,'YYYY')
|| (trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) )||':'||(CASE
TO_CHAR(trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) WHEN
'1' THEN 'First Qtr' WHEN '2' THEN 'Second Qtr' WHEN '3' THEN 'Third
Qtr' WHEN '4' THEN 'Fourth Qtr' end)
ORDER BY
(to_char(iv.inventory_date,'YYYY')
|| (trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) ), (CASE
TO_CHAR(trunc(to_number(to_char(va.modif_date, 'MM')) / 3)+1) WHEN '1'
THEN 'First Qtr' WHEN '2' THEN 'Second Qtr' WHEN '3' THEN 'Third
Qtr' WHEN '4' THEN 'Fourth Qtr' end),
va.VESSEL_UID ASC |
Partager