Database

CREATE Procedure [dbo].[cpGetFirmRemittedPolicySummaryByPolicyType]  
 @firmId int = null,  
 @totalPoliciesToShow int = 0,  
 @currentDate datetime = NULL  
AS  
BEGIN  
/******************************************************************************************  
  
Name: cpGetFirmRemittedPolicySummaryByPolicyType  
  
Description: Returns a dataset with the count of remitted policies types by  
MTD, YTD and Prior Year.  
  
Parmeters:  
@firmId - Id of the firm that need to retrieve data for  
  
@totalPoliciesToShow - The number of unique policy types to display for the current month.  If there   
are more than totalPoliciesToShow policies in a given month the remaining ones are rolled up into   
the 'All Others' Category.  If the value is 0, then there is no rollup into the 'All Others' category,   
all policy types are shown.  There is one case where the 'All Others' category could still show with   
the @totalPoliciesToShow value of 0.  That is when there are policy types that exist for YTD or Prior   
Year but not in MTD.  In this case, the All others category will show zero for MTD but will have a   
value for YTD and/or Prior Year.  
  
@currentDate - Date to be used as current date.  If the value is not supplied, then the current date  
is used.  If a value is supplied, then that date is used to be considered as the current date.  Added  
for testing purposes.  
  
Testing:  
-- Show all policies types for Firm Id 3244631 for the current date  
exec dbo.cpGetFirmRemittedPolicySummaryByPolicyType 4699056, 0  
  
-- Show top 3 policies types for Firm Id 3244631, rolling all others into ALL OTHERS for current date  
exec dbo.cpGetFirmRemittedPolicySummaryByPolicyType 3244631, 3  
  
-- Show top 3 policies types for Firm Id 3244631, rolling all others into ALL OTHERS, using 12/12/2012  
-- as current date  
exec dbo.cpGetFirmRemittedPolicySummaryByPolicyType 3244631, 3, '12/12/2012'  
   
          
********************************************************************************************/  
  
SET NOCOUNT ON  
  
declare @debug bit = 0  
declare @ALL_OTHERS varchar(10) = 'All Others'  
  
-- If the @currentDate is NULL then use the current system date, otherwise use the date passed  
-- This is used for testing purposes  
if (@currentDate is NULL) set @currentDate = GETDATE()  
if (@debug = 1) select @currentDate CurrentDate  
  
declare @priorYear int = Year(@currentDate) - 1  
declare @janFirstThisYear datetime = convert(datetime, '01/01/' + CAST(YEAR(@currentDate) as varchar(4)), 101)  
declare @lowestPolicyRank int  
declare @rankCutoff int  
declare @rowCount int  
  
/*  
Temporary table that holds the details for the ranking of policy types by remitted policy count.  
What policies get populated in this table is determined by whether all policies are to be shown.  
*/  
declare @tblPolicyData table  
(  
  PolicyTypeName varchar(250),  
  RemittedPolicyCount int,  
  PolicyCountRank int  
)  
  
/*  
@tblAggregatedPolicyData holds the aggregrated results for MTD, YTD, and PriorYear for each Policy Type  
IsAllOtherColumn is set to 1 when it is an ALL OTHER column so that it will sort to the bottom  
*/  
declare @tblAggregatedPolicyData table  
(  
  PolicyTypeName varchar(250),  
  PolicyCountRank int,  
  MTDRemittedPolicyCount int,  
  YTDRemittedPolicyCount int,  
  PriorYearRemittedPolicyCount int,  
  IsAllOtherColumn smallint DEFAULT (0)  
)  
  
/*  
@tblResults holds the results that will be returned.  Id identity column has been added so that we  
can control the order of line items - more specifically, that the Total line shows up at the  
end.  This is done by inserting the Total line item last and then returning the results using  
ORDER BY Id  
*/  
declare @tblResults table  
(  
  Id int NOT NULL IDENTITY (1,1),  
  PolicyTypeName varchar(250),  
  MTDRemittedPolicyCount int,  
  YTDRemittedPolicyCount int,  
  PriorYearRemittedPolicyCount int  
)  
  
/*  
Load @tblPolicyData.  If showing all Policies, date range is all policies with a   
RemittedDate with a year that is greater than or equal to last year.  
If not showing all Policies:  
 Start by looking for Remitted Policies in current month.  
 If no remitted policies in the current month, look for policies in this year.  
 If no remitted policies in the current year, look for policies remitted in the previous year.  
*/  
if (@totalPoliciesToShow <> 0)  
begin  
 -- Try the current month  
 INSERT INTO  
  @tblPolicyData  
 SELECT  
  [PolicyTypeName],  
  sum([RemittedPolicyCount]) RemittedPolicyCount,  
  ROW_NUMBER() OVER(ORDER BY Sum(RemittedPolicyCount) DESC) AS PolicyCountRank              
 FROM   
  [dbo].[FirmPolicyDetailMonthly]  
 where  
  FirmId = @firmId  
  and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)  
 group by  
  substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6), PolicyTypeName  
   
 select @rowCount = ISNULL(COUNT(*), 0) from @tblPolicyData  
   
 -- If no policies for the current month, try the current year  
 if (@rowCount = 0)  
 BEGIN  
  INSERT INTO  
   @tblPolicyData  
  SELECT  
   [PolicyTypeName],  
   sum([RemittedPolicyCount]) RemittedPolicyCount,  
   ROW_NUMBER() OVER(ORDER BY Sum(RemittedPolicyCount) DESC) AS PolicyCountRank              
  FROM   
   [dbo].[FirmPolicyDetailMonthly]  
  where  
   FirmId = @firmId  
   and RemittedDate between @janFirstThisYear AND @currentDate  
  group by  
   YEAR(RemittedDate), PolicyTypeName   
 END  
END  
  
-- If the rowCount in @tblPolicyData is still 0 then either we are selecting all policies  
-- types or we are not selecting all policies types and there have been are no remitted policies   
-- for MTD or YTD so we are moving to previous year  
select @rowCount = ISNULL(COUNT(*), 0) from @tblPolicyData  
if (@rowCount = 0)  
BEGIN  
 INSERT INTO  
  @tblPolicyData  
 SELECT  
  [PolicyTypeName],  
  sum([RemittedPolicyCount]) RemittedPolicyCount,  
  ROW_NUMBER() OVER(ORDER BY Sum(RemittedPolicyCount) DESC) AS PolicyCountRank              
 FROM   
  [dbo].[FirmPolicyDetailMonthly]  
 where  
  FirmId = @firmId  
  and YEAR(RemittedDate) >= @priorYear  
 group by  
  PolicyTypeName  
END  
  
if (@debug = 1) select 'PolicyData' PolicyData, * from @tblPolicyData  
  
select @lowestPolicyRank = ISNULL(MAX(PolicyCountRank), 0) from @tblPolicyData  
  
/*  
Calculate the rank cutoff value which is used to determine if items need to be rolled into the   
ALL OTHERS line item. If we are showing all policies the RANK cutoff is simply the lowest ranked   
item in @tblPolicyData.  If not showing all items the rank cutoff is then @totalPoliciesToShow   
unless @totalPoliciesToShow is less than the total policies found.  If it is, then the   
@rankCutoff is the lowest ranked item in tblPolicyData.  
*/  
if (@totalPoliciesToShow = 0)  
begin  
 set @rankCutoff = @lowestPolicyRank  
end  
else  
begin  
 if (@totalPoliciesToShow < @lowestPolicyRank)  
  set @rankCutoff = @totalPoliciesToShow  
 else  
  set @rankCutoff = @lowestPolicyRank  
end  
  
if (@debug = 1) select @lowestPolicyRank LowerstPolicRank, @rankCutoff RankCutoff  
  
/*  
Insert Policy Types into @tblAggregatedPolicyData so that @tblAggregatedPolicyData can be used  
to supply the policy types in the join with FirmPolicyDetailMonthly to set the MTD, YTD or PriorYear  
RemittedPolicy counts  
*/  
  
INSERT INTO  
 @tblAggregatedPolicyData (PolicyTypeName, PolicyCountRank, MTDRemittedPolicyCount, YTDRemittedPolicyCount, PriorYearRemittedPolicyCount)  
SELECT  
 PolicyTypeName, PolicyCountRank, 0, 0, 0  
FROM  
 @tblPolicyData  
WHERE  
 PolicyCountRank <= @rankCutoff  
  
if (@debug = 1) select 'AllPoliciesBeforeTotalCalcs' Message,* from @tblAggregatedPolicyData  
  
-- Update MTD counts for top Remitted Policy Types  
UPDATE   
 @tblAggregatedPolicyData  
SET  
 MTDRemittedPolicyCount = detail.RemittedPolicyCount  
FROM  
 @tblAggregatedPolicyData r INNER JOIN (  
    SELECT  
     PolicyTypeName, SUM(RemittedPolicyCount) as RemittedPolicyCount  
    FROM  
     FirmPolicyDetailMonthly  
    WHERE  
     FirmId = @firmId  
     and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)  
    GROUP BY  
     PolicyTypeName) detail ON r.PolicyTypeName = detail.PolicyTypeName  
WHERE  
 r.PolicyCountRank <= @rankCutoff  
  
if (@debug = 1) select 'MTD-TopX' Message,* from @tblAggregatedPolicyData  
  
-- Insert MTD 'ALL OTHERS' category if needed.  ALL OTHERS is only used when @totalPoliciesToShow is not  
-- zero.  If ALL OTHERS is required, it is inserted with a rank that is one more than the lowest found rank.  
IF (@totalPoliciesToShow <> 0)  
BEGIN     
 select @rowCount = ISNULL(COUNT(*), 0) from FirmPolicyDetailMonthly where  
  FirmId = @firmId  
  and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)  
  and PolicyTypeName not in   
   (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)  
  
 IF (@rowCount <> 0)  
 BEGIN  
  INSERT INTO  
   @tblAggregatedPolicyData (PolicyTypeName, PolicyCountRank, MTDRemittedPolicyCount, IsAllOtherColumn)  
  SELECT  
   @ALL_OTHERS as PolicyTypeName, @lowestPolicyRank + 1, Sum(RemittedPolicyCount) as RemittedPolicyCount, 1  
  FROM  
   FirmPolicyDetailMonthly  
  WHERE  
   FirmId = @firmId  
   and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)  
   and PolicyTypeName not in   
    (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)  
 END  
END  
  
if (@debug = 1) select 'MTD-AllOthers' Message,* from @tblAggregatedPolicyData  
  
-- Insert YTD for top remitted policies  
UPDATE   
 @tblAggregatedPolicyData  
SET  
 YTDRemittedPolicyCount = detail.RemittedPolicyCount  
FROM  
 @tblAggregatedPolicyData r INNER JOIN (  
    SELECT  
     PolicyTypeName, SUM(RemittedPolicyCount) as RemittedPolicyCount  
    FROM  
     FirmPolicyDetailMonthly  
    WHERE  
     FirmId = @firmId  
     and RemittedDate between @janFirstThisYear AND @currentDate  
    GROUP BY  
     PolicyTypeName) detail ON r.PolicyTypeName = detail.PolicyTypeName  
WHERE  
 r.PolicyCountRank <= @rankCutoff  
  
if (@debug = 1) select 'YTD-TopX' Message,* from @tblAggregatedPolicyData  
  
-- 'ALL OTHERS' Policies YTD  
IF (@totalPoliciesToShow <> 0)  
BEGIN     
 select @rowCount = ISNULL(COUNT(*), 0) from FirmPolicyDetailMonthly where  
  FirmId = @firmId  
  and RemittedDate between @janFirstThisYear AND @currentDate  
  and PolicyTypeName not in   
   (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)  
      
 IF (@rowCount <> 0)  
 BEGIN  
  -- If the ALL OTHERS line item doesn't exist, add empty one  
  select @rowCount = ISNULL(Count(*), 0) from @tblAggregatedPolicyData where PolicyTypeName = @ALL_OTHERS  
  if (@rowCount = 0) INSERT INTO @tblAggregatedPolicyData (PolicyTypeName, IsAllOtherColumn,  PolicyCountRank) SELECT @ALL_OTHERS, 1, @lowestPolicyRank + 1  
    
  UPDATE   
   @tblAggregatedPolicyData  
  SET  
   YTDRemittedPolicyCount = detail.RemittedPolicyCount  
  FROM  
   @tblAggregatedPolicyData r INNER JOIN (  
      SELECT  
       @ALL_OTHERS as PolicyTypeName, Sum(RemittedPolicyCount) as RemittedPolicyCount  
      FROM  
       FirmPolicyDetailMonthly  
      WHERE  
       FirmId = @firmId  
       and RemittedDate between @janFirstThisYear AND @currentDate  
       and PolicyTypeName  
        not in (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)) detail  
      ON r.PolicyTypeName = detail.PolicyTypeName  
 END  
END  
  
if (@debug = 1) select 'YTD-AllOthers' Message,* from @tblAggregatedPolicyData  
  
-- Prior Year for top remitted policies  
UPDATE   
 @tblAggregatedPolicyData  
SET  
 PriorYearRemittedPolicyCount = detail.RemittedPolicyCount  
FROM  
 @tblAggregatedPolicyData r INNER JOIN (  
    SELECT  
     PolicyTypeName, SUM(RemittedPolicyCount) as RemittedPolicyCount  
    FROM  
     FirmPolicyDetailMonthly  
    WHERE  
     FirmId = @firmId  
     and Year(RemittedDate) = @priorYear  
    GROUP BY  
     PolicyTypeName) detail ON r.PolicyTypeName = detail.PolicyTypeName  
WHERE   
 r.PolicyCountRank <= @rankCutoff  
  
if (@debug = 1) select 'PY-TopX' Message,* from @tblAggregatedPolicyData  
  
-- 'ALL OTHERS' Policies Prior Year  
IF (@totalPoliciesToShow <> 0)  
BEGIN     
 select @rowCount = ISNULL(COUNT(*), 0) from FirmPolicyDetailMonthly where  
  FirmId = @firmId  
  and Year(RemittedDate) = @priorYear  
  and PolicyTypeName not in   
   (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)  
  
 IF (@rowCount <> 0)  
 BEGIN  
  -- If the ALL OTHERS line item doesn't exist, add empty one  
  select @rowCount = ISNULL(Count(*), 0) from @tblAggregatedPolicyData where PolicyTypeName = @ALL_OTHERS  
  if (@rowCount = 0) INSERT INTO @tblAggregatedPolicyData (PolicyTypeName, IsAllOtherColumn, PolicyCountRank) SELECT @ALL_OTHERS, 1, @lowestPolicyRank + 1  
   
  UPDATE   
   @tblAggregatedPolicyData  
  SET  
   PriorYearRemittedPolicyCount = detail.RemittedPolicyCount  
  FROM  
   @tblAggregatedPolicyData r INNER JOIN (  
      SELECT  
       @ALL_OTHERS as PolicyTypeName, Sum(RemittedPolicyCount) as RemittedPolicyCount  
      FROM  
       FirmPolicyDetailMonthly  
      WHERE  
       FirmId = @firmId  
       and Year(RemittedDate) = @priorYear  
       and PolicyTypeName   
        not in (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)) detail  
      ON r.PolicyTypeName = detail.PolicyTypeName  
 END  
END  
  
if (@debug = 1) select 'PY-AllOthers' Message,* from @tblAggregatedPolicyData  
  
/*  
Insert the aggregated results into a @tblResults with the results sorted with the highest   
policy counts at the top.  IsAllOtherColumn is the first sort column because we want   
ALL OTHERS to be the last item inserted.  
The ALL OTHERS row has IsAllOtherColumn = 1 while the real policy data rows have   
IsAllOtherColumn = 0.  So by sorting on IsAllOtherColumn first, all the real policy types   
will show first and ALL OTHERS will be at the end.  
*/  
INSERT INTO  
 @tblResults  (PolicyTypeName, MTDRemittedPolicyCount, YTDRemittedPolicyCount, PriorYearRemittedPolicyCount)  
select  
 UPPER(PolicyTypeName) as PolicyTypeName,   
 ISNULL(MTDRemittedPolicyCount,0) MTDRemittedPolicyCount,  
 ISNULL(YTDRemittedPolicyCount,0) YTDRemittedPolicyCount,  
 ISNULL(PriorYearRemittedPolicyCount,0) PriorYearRemittedPolicyCount  
from  
 @tblAggregatedPolicyData  
order by  
 IsAllOtherColumn,   
 MTDRemittedPolicyCount DESC, YTDRemittedPolicyCount DESC, PriorYearRemittedPolicyCount DESC  
   
-- Insert the TOTAL row into @tblResults as the last row, therefore it wil have the highest Id value  
INSERT INTO  
 @tblResults  (PolicyTypeName, MTDRemittedPolicyCount, YTDRemittedPolicyCount, PriorYearRemittedPolicyCount)  
SELECT  
 'TOTAL', SUM(MTDRemittedPolicyCount), SUM(YTDRemittedPolicyCount), SUM(PriorYearRemittedPolicyCount)  
FROM  
 @tblAggregatedPolicyData  
  
/*  
Since @tblResults has an identity column, return the results ordering by identity   
column Id so that the TOTAL row is at the bottom.  
*/  
SELECT  
 PolicyTypeName, ISNULL(MTDRemittedPolicyCount, 0) MTDRemittedPolicyCount,   
 ISNULL(YTDRemittedPolicyCount, 0) YTDRemittedPolicyCount,  
 ISNULL(PriorYearRemittedPolicyCount, 0) PriorYearRemittedPolicyCount  
FROM  
 @tblResults  
ORDER BY  
 Id  
  
SET NOCOUNT OFF  
  
RETURN 0  
  
END