devicz devicz - 1 month ago 6
SQL Question

Join issue on SQL request

My database :

TB_DW_VAB_FLIGHT : ID_TEC_FLIGHT
TB_DW_VAB_SALES : QUANTITY, TRANSACTION_NUMBER, UNIT_SALES_PRICE


I want to have a table with 4 columns as result : CA, QTE, NB_TRANSACTION and NB_VOLS at the same month. ( N-1 )

I tried a SQL request like this :

SELECT
sum(QUANTITY*UNIT_SALES_PRICE) as CA,
sum(QUANTITY) as QTE,
count(distinct TRANSACTION_NUMBER) as NB_TRANSACTION,
count(distinct ID_TEC_FLIGHT) as NB_VOLS
FROM TB_DW_VAB_SALES, TB_DW_VAB_FLIGHT
where to_char(TB_DW_VAB_SALES.FLIGHT_DATE,'MM')=to_char(current_date,'MM')-1 and to_char(TB_DW_VAB_SALES.FLIGHT_DATE,'YYYY')=to_char(current_date,'YYYY') and SALES_TYPE='SALES'
and to_char(TB_DW_VAB_FLIGHT.FLIGHT_DATE,'MM')=to_char(current_date,'MM')-1 and to_char(TB_DW_VAB_FLIGHT.FLIGHT_DATE,'YYYY')=to_char(current_date,'YYYY');


But Oracle can't give me an answer.

Thank you a lot for any help.

Answer

Try

with CTE1 as 
(
select to_char(FLIGHT_DATE, 'MM-YYYY') as PERIOD,
       sum(QUANTITY*UNIT_SALES_PRICE) as CA,
       sum(QUANTITY) as QTE,
       count(distinct TRANSACTION_NUMBER) as NB_TRANSACTION
from TB_DW_VAB_SALES
where SALES_TYPE = 'SALES'
group by to_char(FLIGHT_DATE, 'MM-YYYY')
)
, CTE2 as 
(
select count(distinct ID_TEC_FLIGHT) as NB_VOLS, 
       to_char(FLIGHT_DATE, 'MM-YYYY') as PERIOD
from TB_DW_VAB_FLIGHT
group by to_char(FLIGHT_DATE, 'MM-YYYY')
)
select CTE1.CA, 
       CTE1.QTE, 
       CTE1.NB_TRANSACTION, 
       CTE2.NB_VOLS
from CTE1
inner join CTE2 on CTE1.PERIOD = CTE2.PERIOD
where CTE1.PERIOD = to_char(add_Months(sysdate,-1),'MM-YYYY')
Comments