Hi all, I need to create a dataset for a report containg monthly sales grouped by salesperson and then by month for a given year, the part I'm struggling with is for some or all months there might not be any sales but I need to show zeroes for these cases - as a starting point I have this ( the calendar table contains dates for the next fifty years)
SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber
FROM CalendarTable ct
cross join Opp_View ov
where year(ct.dateindex) = 2016
GROUP BY ov.Owner_Id,MONTH(DateIndex)
order by ov.Owner_Id,MonthNumber
which returns
Owner_Id MonthNumber
REP1 1
REP1 2
REP1 3
REP1 4
REP1 5
REP1 6
REP1 7
REP1 8
REP1 9
REP1 10
REP1 11
REP1 12
etc... for all representatives
which is what I want, the problem starts when I include summed amounts by month using another table as in
SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,
sum(sf.o_sum_financed) as SumFinanced
FROM CalendarTable ct
cross join Opp_View ov
left outer join O_Sum_Financed sf
on ov.Opp_Id = sf.Client_Id
where year(ct.dateindex) = 2016
GROUP BY ov.Owner_Id,MONTH(DateIndex)
order by ov.Owner_Id,MonthNumber
which returns this
Owner_Id MonthNumber SumFinanced
REP1 1 113358054.33
REP1 2 106044631.47
REP1 3 113358054.33
REP1 4 109701342.9
REP1 5 113358054.33
REP1 6 109701342.9
REP1 7 113358054.33
REP1 8 113358054.33
REP1 9 109701342.9
REP1 10 113358054.33
REP1 11 109701342.9
REP1 12 113358054.33
which is totally wrong ( e.g the first row should show 420,000 ) any ideas chaps ?
What I have tried:
SELECT ov.Owner_Id,MONTH(DateIndex) as MonthNumber,
sum(sf.o_sum_financed) as SumFinanced
FROM CalendarTable ct
cross join Opp_View ov
left outer join O_Sum_Financed sf
on ov.Opp_Id = sf.Client_Id
where year(ct.dateindex) = 2016
GROUP BY ov.Owner_Id,MONTH(DateIndex)
order by ov.Owner_Id,MonthNumber