Oleg Oleg - 3 months ago 5
SQL Question

Why I have duplicate month number

I dont understand, why the result of this query displays duplicate month with different count number of sumbmission:

[![SELECT CAST(B.YearNum as varchar(10))+ ' Submitted' as Type,
1 as OrderNum,
COUNT( ControlNo) Count,
b.YearNum, b.MonthNum
FROM tblCalendar b
LEFT JOIN ClearanceReportMetrics a ON b.MonthNum = MONTH(a.EffectiveDate) AND b.YearNum=YEAR(a.EffectiveDate)
AND CompanyLine = 'Plaza Insurance Company' AND Underwriter <> 'Batcheller, Jerry'
WHERE YEAR(EffectiveDate) IN (2016, 2015,2014)
GROUP BY b.YearNum, b.MonthNum, Type
order by b.YearNum, b.MonthNum][1]][1]


enter image description here

Answer

Most probably on of the source tables has type column .

select year + 1 as type, count(*)
from (
      values 
        (1, 2014, 1)
      , (2, 2014, 2)
    ) t (id,year, type)
group by year, type