Selecting issue in SQl Server
-
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
/******************************************************************************************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 purpose -
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
/******************************************************************************************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 purposeIt might be helpfull if you divide this script into several simpeler stored procedures. Complex tasks are always easier when broken into smaller pieces.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]