Hi All,
I need sql for below table
CUSTOMER_SITE REFERENCE_1 SITE_CURRENCY SETTING_CURRENCY DATE_1 SALES_QTY SALES_REVENUE_SETTING_CURRENCY SALES_REVENUE_SITE_CURRENCY
00016472 BR_DC_Cajamar BRL EUR 2020-05-01 3.000000000 4.324018518 28.493120106
00226095 BR_DC_Cajamar BRL EUR 2020-05-01 2.000000000 169.513083222 1117.006465141
00111802 BR_DC_Cajamar BRL EUR 2020-07-01 3.000000000 112.504466963 741.348187209
00124217 BR_DC_Cajamar BRL EUR 2020-07-01 6.000000000 89.166354982 587.561697862
00124400 BR_DC_Cajamar BRL EUR 2020-04-01 4.000000000 336.404408822 2216.736858389
00460100 SE_DC_Vasterhaninge SEK EUR 2018-06-01 8.000000000 100.155828620 1014.658665999
00124219 BR_DC_Cajamar BRL EUR 2020-06-01 6.000000000 42.238900483 278.333235546
00226052 BR_DC_Cajamar BRL EUR 2020-07-01 6.000000000 71.003224666 467.875750297
00580000 BR_DC_Cajamar BRL EUR 2020-04-01 8.000000000 68.641652195 452.314168458
00612007 BR_DC_Cajamar BRL EUR 2020-07-01 5.000000000 120.427625982 793.557843720
My effort
SELECT MONTHS_BETWEEN ( TRUNC (MAX (DATE), 'MONTH'),TRUNC (MIN (DATE), 'MONTH')) + 1 AS months,
MIN (DATE) AS date_min
MAX (DATE) AS date_max
COUNT(REFERENCE_1) AS CNT,
SUM(SALES_QTY) as "Sales",
SUM(SALES_REVENUE_SETTING_CURRENCY) as "Revenue",
SUM(SALES_REVENUE_SITE_CURRENCY) as "Curr",
(SUM(SALES_QTY)+SUM(SALES_REVENUE_SETTING_CURRENCY)+SUM(SALES_REVENUE_SITE_CURRENCY)) AS TOTAL
FROM TEST
GROUP BY CUSTOMER_SITE,SITE_CURRENCY,SETTING_CURRENCY