I have a sp for selecting all the records(policies here) and it should display 3 records and remainig in 'all others' category and total. But this is displaying 'all others' if there is less than 3 records. Waht should do for this
CREATE Procedure [dbo].[cpGetFirmRemittedPolicySummaryByPolicyType]
@firmId int = null,
@totalPoliciesToShow int = 0,
@currentDate datetime = NULL
AS
BEGIN
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
declare @tblPolicyData table
(
PolicyTypeName varchar(250),
RemittedPolicyCount int,
PolicyCountRank int
)
declare @tblAggregatedPolicyData table
(
PolicyTypeName varchar(250),
PolicyCountRank int,
MTDRemittedPolicyCount int,
YTDRemittedPolicyCount int,
PriorYearRemittedPolicyCount int,
IsAllOtherColumn smallint DEFAULT (0)
)
declare @tblResults table
(
Id int NOT NULL IDENTITY (1,1),
PolicyTypeName varchar(250),
MTDRemittedPolicyCount int,
YTDRemittedPolicyCount int,
PriorYearRemittedPolicyCount int
)
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
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 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 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
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
|