Oleg Oleg - 4 months ago 12
SQL Question

Break down by month sub select statement

I my query im breaking down data by 12 month, but column

'Submitted - New Business'
coming from another table. Is any chance it can also be broken down by month?

SELECT 'Eldred, Rod' AS Underwriter,
SUM(CASE WHEN Underwriter = 'Eldred, Rod' THEN UWReportFee ELSE 0 END) as 'UW ReportFee',
(SUM(CASE WHEN Underwriter = 'Eldred, Rod' THEN Fees ELSE 0 END)) - SUM(CASE WHEN Underwriter = 'Eldred, Rod' THEN UWReportFee ELSE 0 END) as 'Taxes & Surcharges',
COUNT(CASE WHEN Underwriter = 'Eldred, Rod' AND PolicyType = 'New Business' THEN QuoteControlNum END) as 'Submitted - New Business',
/*SubQuery*/
(
SELECT COUNT(CASE WHEN Underwriter = 'Eldred, Rod' AND Type = 'New Business' THEN ControlNo END)
FROM tblCalendar b
LEFT JOIN ClearanceReportMetrics a ON b.MonthNum=Month(a.EffectiveDate)
AND b.YearNum = YEAR(a.EffectiveDate) AND CompanyLine = 'Arch Insurance Company' AND YEAR(EffectiveDate)=2016
) as 'Submitted - New Business' ,
/* End of SubQuery*/

b.MonthNum,
b.YearNum
FROM tblCalendar b
LEFT JOIN ProductionReportMetrics a ON b.MonthNum=Month(a.EffectiveDate)
AND b.YearNum = YEAR(a.EffectiveDate) AND CompanyLine = 'Arch Insurance Company' AND YEAR(EffectiveDate)=2016
--AND a.Underwriter ='Eldred, Rod'
WHERE b.YearNum = 2016
GROUP BY --a.Underwriter,
b.MonthName,
b.MonthNum,
b.YearNum


enter image description here

as you can see on a pic, every Month has the same value, is any chance to break it down somehow without usin
JOIN
?

Answer

Try joining grouped data sets

    declare @uw varchar(50) ='Eldred, Rod';
    declare @year = 2016;
    declare @cn varchar(50) ='Arch Insurance Company';

    SELECT      @uw,                   
                t1.'UW ReportFee',
                t1.'Taxes & Surcharges',
                t1.'Submitted - New Business',
                t2.cnt as 'Submitted - New Business count' ,
                b.MonthNum,
                b.YearNum
    FROM        tblCalendar b  
    LEFT JOIN   ( 
                SELECT 
                Month(EffectiveDate) as Month,
                SUM( UWReportFee) as 'UW ReportFee',
                (SUM(Fees ) - SUM(UWReportFee ) as 'Taxes & Surcharges',
                COUNT(CASE WHEN PolicyType = 'New Business' THEN QuoteControlNum  END)  as 'Submitted - New Business',
                FROM ProductionReportMetrics 
                GROUP BY Month(EffectiveDate),
                WHERE YEAR(a.EffectiveDate) = @year AND CompanyLine = @cn AND Underwriter = @uw
                ) t1 ON t1.Month = b.MonthNum
    LEFT JOIN   ( 
                SELECT 
                Month(EffectiveDate) as Month,
                COUNT(ControlNo) as cnt 
                FROM ClearanceReportMetrics
                GROUP BY Month(EffectiveDate),
                WHERE YEAR(a.EffectiveDate) = @year AND CompanyLine = @cn AND Underwriter = @uw AND Type = 'New Business'
                ) t2 ON t2.Month = b.MonthNum
   WHERE       b.YearNum = 2016
Comments