I am trying to combine four different select statements into one that gives me the desired output.
Two of the statements can be seen below (they and the others are identical with the exception of Y.Date_Year)
select sum(N.EBIT)/Sum(S.Sales), M.Date_Month from EBIT as N
inner join Date_Year as Y on Y.Date_Year_Id = N.Date_Year_Id
inner join Sales As S on S.Company_Id = N.Company_Id
inner join Date_Month As M on M.Date_Month_Id=N.Date_Month_Id
where Y.Date_Year = 2014 and (N.Date_Month_Id = S.Date_Month_Id And N.Date_Year_Id = S.Date_Year_Id) and N.EBIT <> 0 and S.Sales <> 0
group by M.Date_Month
select sum(N.EBIT)/Sum(S.Sales), M.Date_Month from EBIT as N
inner join Date_Year as Y on Y.Date_Year_Id = N.Date_Year_Id
inner join Sales As S on S.Company_Id = N.Company_Id
inner join Date_Month As M on M.Date_Month_Id=N.Date_Month_Id
where Y.Date_Year = 2015 and (N.Date_Month_Id = S.Date_Month_Id And N.Date_Year_Id = S.Date_Year_Id) and N.EBIT <> 0 and S.Sales <> 0
group by M.Date_Month
They give me different views with the Date_Month Column and the EBIT/Sales Column. As of now I have to go to excel, paste the different values in and arrange them so they go from starting date (First month in Date_Month Column) to the ending date (Last month in Date_Month Column) and then move the different EBIT/Sales values in position.
The first Statement has Data from 2012-01-31 to 2015-11-30 while the second statement has Data from 2012-01-31 to 2016-11-30. I would like to have a table that looks somewhat like this below:
Date_Month EBIT/Sales 2014 EBIT/Sales 2015
2012-01-31 0.09 0.10
.... ..... .....
2016-11-30 'Null' 0.098
Hence that they are in the same list but wherever one of the columns does not have a value it will give out Null.
Thank you for any help.
P.s these are Estimates in the data so do not get confused with 2014 values existing in 2012-01-31 etcetera.
You are looking for conditional aggregation or a pivot query. I am more used to the former, so here it is:
select
m.date_month,
sum(case when y.date_year = 2014 then n.ebit end) /
sum(case when y.date_year = 2014 then s.sales end) as "EBIT/Sales 2014",
sum(case when y.date_year = 2015 then n.ebit end) /
sum(case when y.date_year = 2015 then s.sales end) as "EBIT/Sales 2015",
sum(case when y.date_year = 2016 then n.ebit end) /
sum(case when y.date_year = 2016 then s.sales end) as "EBIT/Sales 2016",
sum(case when y.date_year = 2017 then n.ebit end) /
sum(case when y.date_year = 2017 then s.sales end) as "EBIT/Sales 2017"
from ebit as n
inner join sales as s on s.company_id = n.company_id
and s.date_month_id = n.date_month_id
and s.date_year_id = n.date_year_id
inner join date_year as y on y.date_year_id = n.date_year_id
inner join date_month as m on m.date_month_id = n.date_month_id
where y.date_year in (2014, 2015, 2016, 2017)
and n.ebit <> 0
and s.sales <> 0
group by m.date_month;