Add Date Parameter to script to filter Date Paid Through

1

Hi I need to add a date parameter to filters. Here is my script so far.

--If CommissionsPaid is null then make it 0
--Payment Month needs to be updated to this format - 2021/03 - YYYYY/MM
--Add Date Paid Param
--Turn this into a Stored Proc named -> spREPPremAndCommPaidByPolicy

--drop PROCEDURE spREPremAndCommPaidByPolicy

CREATE PROCEDURE spREPremAndCommPaidByPolicy

	@datepaid date

AS
BEGIN

--DROP TABLE #Policies_3
CREATE TABLE #Policies_3 (
PolicyId int, -- pp.PlcPolicyId,
PolicyHolder varchar(100), --AS PolicyHolder,
EffectiveDate date, --pp.EffectiveDate,
PremiumReceived money, --SUM(ptpd.PremiumAmt) AS PremiumAmt,
CommissionsPaid money, --SUM(ptc.CommAmount) AS CommissionAmt,
DateReceived date, --pda.DepAdjDate,
DatePaid date, --cr.PrintDate
CheckNumber char(20), --cr.CheckNumber
IssueDate date, --pp.PolicyIssueDate, &?
PaymentMonth varchar(20) --pdap.yearnumber, monthnumber
);

declare @RequestProducerID int = 0,
@RequestBrokerId int = 0,
@RequestTPAId int =0,
--@RequestTagId int =0, --5 Tags
@BegDate Date = '02/01/2015' , -- Used for Eff Date
@EndDate Date = '02/01/2015' , -- Used for Eff Date
@IsAll BIT = 0, -- Bring Everything

@Tag1Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag1 selection
@Tag2Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag2 selection
@Tag3Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag3 selection
@Tag4Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag4 selection
@Tag5Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag5 selection
@IsFullRS bit = 0 -- 1: Returns full result set; 0: Returns partical result set

declare @rc int,
@op varchar(16),
@obj varchar(64)

     --@datepaid date = getdate()

--= SELECT CONVERT(date, GETDATE())

INSERT INTO #Policies_3

SELECT pp.plcPolicyId, dc_acct.Name AS PolicyHolder, pp.EffectiveDate , SUM(ptpd.PremiumAmt) AS PremiumAmt, ISNULL(SUM(ptc.CommAmount),0) AS CommissionAmt,

pda.DepAdjDate, cr.PrintDate, cr.CheckNumber, pp.PolicyIssueDate,
Convert(Varchar(20),pdap.Yearnumber) + '/' + Convert(varchar(20),pdap.MonthNumber) AS PaymentMonth

FROM PrmDepAdj pda

INNER JOIN PrmDepAdjPolicy pdap ON pdap.PrmDepAdjId = pda.PrmDepAdjId
INNER JOIN dbo.PlcPolicy AS pp ON pp.PlcPolicyId = pdap.PlcPolicyId
INNER JOIN dbo.DirCompany AS dc_acct ON dc_acct.DirCompanyId = pp.AccountId
INNER JOIN PrmTransPremium ptp ON ptp.PrmDepAdjPolicyId = pdap.PrmDepAdjPolicyId
Inner JOIN PlcMDSLPolicy AS pmd on pmd.PlcMDSLPolicyId = pp.PlcPolicyId
INNER JOIN PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId

INNER JOIN dbo.PrmDeposits AS pd ON PD.PrmDepAdjId = pda.PrmDepAdjId
INNER JOIN dbo.CheckRegister AS cr ON cr.CheckNumber = pd.CheckNumber

LEFT OUTER JOIN dbo.CheckExportDetail AS ced ON ced.CurrentRegisterId = cr.CheckRegisterId

INNER JOIN
( SELECT PrmTransPremiumId, SUM(Amount) AS PremiumAmt FROM dbo.PrmTransPremiumDetail GROUP BY PrmTransPremiumId) AS ptpd ON ptpd.PrmTransPremiumId = ptp.PrmTransPremiumId

LEFT OUTER JOIN dbo.PrmTransCommission AS ptc ON ptc.PrmTransPremiumId = ptp.PrmTransPremiumId

INNER JOIN dbo.CodeCoverageType AS cct ON cct.CoverageTypeId = ptp.CoverageTypeId
INNER JOIN dbo.CodeTierSchedule AS cts ON cts.TierScheduleId = ptp.TierScheduleId

WHERE --@datepaid =getdate()

( pp.EffectiveDate BETWEEN @BegDate AND @EndDate

 AND
    (-- Bring everything 
		( 
			@IsAll = 1 
		)
	OR 
	(	(-- Broker 
                @RequestBrokerId = 0 
       OR 

				pp.BrokerId IN (select IntId from MgmtReportRequests where RequestId = @RequestBrokerId)
		)
	AND 
		(-- Producer 
			   @RequestProducerId = 0 
       OR 

				pp.ProducerId IN (select IntId from MgmtReportRequests where RequestId = @RequestProducerId)
		)

	AND 
		( --TPA
			@RequestTPAId = 0 
        OR 

			 pmd.PremiumAdministratorId IN (select IntId from MgmtReportRequests where RequestId = @RequestTPAId)
		
		)

	--AND 
	
		--( --TagID
		--   @RequestTagId = 0 
  --     OR

-- PPT.TagId IN (select IntId from MgmtReportRequests where RequestId = @RequestTagId)
--)
)
)
)

GROUP BY pp.PlcPolicyId, dc_acct.Name, pp.EffectiveDate, pp.PolicyIssueDate , pda.DepAdjDate, cr.PrintDate, cr.CheckNumber, pdap.MonthNumber, pdap.YearNumber
ORDER BY dc_acct.Name, pp.EffectiveDate

IF @Tag1Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy Pp inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag1Id ) )

--set @rc = @@error
--if ( @rc <> 0 )
--begin
-- set @op = 'delete'
-- set @obj = '#Policies (Tags1)'
-- goto OnError
--end
end

if @Tag2Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag2Id ) )

--set @rc = @@error
--if ( @rc <> 0 )
--begin
-- set @op = 'delete'
-- set @obj = '#Policies (Tags2)'
-- goto OnError
--end
end

if @Tag3Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag3Id ) )

--set @rc = @@error
--if ( @rc <> 0 )
--begin
-- set @op = 'delete'
-- set @obj = '#Policies (Tags3)'
-- goto OnError
--end
end

if @Tag4Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag4Id ) )

--set @rc = @@error
--if ( @rc <> 0 )
--begin
-- set @op = 'delete'
-- set @obj = '#Policies (Tags4)'
-- goto OnError
--end
end

if @Tag5Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag5Id ) )

--set @rc = @@error
--if ( @rc <> 0 )
--begin
-- set @op = 'delete'
-- set @obj = '#Policies (Tags5)'
-- goto OnError
--end
end

--OnError:
-- raiserror('$DBGIR$:1;sp_RepSalesDetail;%d;%s;%s;', 18, 1, @rc, @op, @obj)
-- --return(1)
-- Select 'OnError'
--GO

SELECT * FROM #Policies_3

END

Go

EXEC spREPremAndCommPaidByPolicy '2015-02-01'

2

Welcome to forum. Please clean up the script by removing the commented out part of the code and post back. What you posted is unreadable and difficult to sort out.

3

I think you're doing more work than you need to.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE spREPPremAndCommPaidByPolicy
	@datepaid date
AS
SET NOCOUNT ON;

declare @BegDate Date = '02/01/2015' , -- Used for Eff Date
  @EndDate Date = '02/01/2015' , -- Used for Eff Date
  @IsAll BIT = 0, -- Bring Everything
  @IsFullRS bit = 0, -- 1: Returns full result set; 0: Returns partical result set
  @obj varchar(64),
  @op varchar(16),
  @rc int,
  @RequestBrokerId int = 0,
  @RequestTPAId int = 0,
  @RequestProducerID int = 0,
  --@RequestTagId int = 0, --5 Tags
  @Tag1Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag1 selection
  @Tag2Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag2 selection
  @Tag3Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag3 selection
  @Tag4Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag4 selection
  @Tag5Id int = 0 -- 0 or Value for RequestID column in MgmtReportRequests table for Tag5 selection

     --@datepaid date = getdate()
--= SELECT CONVERT(date, GETDATE())

SELECT pp.plcPolicyId, dc_acct.Name AS PolicyHolder, pp.EffectiveDate , SUM(ptpd.PremiumAmt) AS PremiumAmt, ISNULL(SUM(ptc.CommAmount),0) AS CommissionAmt,
  pda.DepAdjDate, cr.PrintDate, cr.CheckNumber, pp.PolicyIssueDate,
  Convert(Varchar(20),pdap.Yearnumber) + '/' + Convert(varchar(20),pdap.MonthNumber) AS PaymentMonth

FROM PrmDepAdj pda

INNER JOIN PrmDepAdjPolicy pdap ON pdap.PrmDepAdjId = pda.PrmDepAdjId
INNER JOIN dbo.PlcPolicy AS pp ON pp.PlcPolicyId = pdap.PlcPolicyId
INNER JOIN dbo.DirCompany AS dc_acct ON dc_acct.DirCompanyId = pp.AccountId
INNER JOIN PrmTransPremium ptp ON ptp.PrmDepAdjPolicyId = pdap.PrmDepAdjPolicyId
Inner JOIN PlcMDSLPolicy AS pmd on pmd.PlcMDSLPolicyId = pp.PlcPolicyId
INNER JOIN PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId

INNER JOIN dbo.PrmDeposits AS pd ON PD.PrmDepAdjId = pda.PrmDepAdjId
INNER JOIN dbo.CheckRegister AS cr ON cr.CheckNumber = pd.CheckNumber

LEFT OUTER JOIN dbo.CheckExportDetail AS ced ON ced.CurrentRegisterId = cr.CheckRegisterId

INNER JOIN
  ( SELECT PrmTransPremiumId, SUM(Amount) AS PremiumAmt FROM dbo.PrmTransPremiumDetail GROUP BY PrmTransPremiumId ) 
  AS ptpd ON ptpd.PrmTransPremiumId = ptp.PrmTransPremiumId

  LEFT OUTER JOIN dbo.PrmTransCommission AS ptc ON ptc.PrmTransPremiumId = ptp.PrmTransPremiumId

  INNER JOIN dbo.CodeCoverageType AS cct ON cct.CoverageTypeId = ptp.CoverageTypeId
  INNER JOIN dbo.CodeTierSchedule AS cts ON cts.TierScheduleId = ptp.TierScheduleId

  WHERE --@datepaid =getdate()

  ( pp.EffectiveDate BETWEEN @BegDate AND @EndDate

   AND
      (-- Bring everything 
	  	( 
			@IsAll = 1 
		)
	OR 
	(	(-- Broker 
                @RequestBrokerId = 0 
       OR 

				pp.BrokerId IN (select IntId from MgmtReportRequests where RequestId = @RequestBrokerId)
		)
	AND 
		(-- Producer 
			   @RequestProducerId = 0 
       OR 

				pp.ProducerId IN (select IntId from MgmtReportRequests where RequestId = @RequestProducerId)
		)

	AND 
		( --TPA
			@RequestTPAId = 0 
        OR 

			 pmd.PremiumAdministratorId IN (select IntId from MgmtReportRequests where RequestId = @RequestTPAId)
		
		)

  )
  )
  )

  AND NOT EXISTS( select PPT.PlcPolicyId 
  from PlcPolicyTag PPT
  where PPT.PlcPolicyId = pp.plcPolicyId
  and (PPT.ExpirationDate IS NULL OR  PPT.ExpirationDate >= PP.ExpirationDate)
  and PPT.TagId in ( select IntId from MgMtReportRequests where RequestID IN (NULLIF(@Tag1Id, 0), NULLIF(@Tag2Id, 0), NULLIF(@Tag3Id, 0), NULLIF(@Tag4Id, 0), NULLIF(@Tag5Id, 0) ) )
  )

GROUP BY dc_acct.Name, pp.EffectiveDate, pp.PlcPolicyId, pp.PolicyIssueDate , pda.DepAdjDate, cr.PrintDate, cr.CheckNumber, pdap.MonthNumber, pdap.YearNumber
ORDER BY dc_acct.Name, pp.EffectiveDate

/*end of proc*/
GO
4

Hi!

I need to create a procedure and then have a date parameter set. This is the script without the commented out errors.

CREATE PROCEDURE spREPremAndCommPaidByPolicy

	@datepaid date

AS
BEGIN

CREATE TABLE #Policies_3 (
PolicyId int, -- pp.PlcPolicyId,
PolicyHolder varchar(100), --AS PolicyHolder,
EffectiveDate date, --pp.EffectiveDate,
PremiumReceived money, --SUM(ptpd.PremiumAmt) AS PremiumAmt,
CommissionsPaid money, --SUM(ptc.CommAmount) AS CommissionAmt,
DateReceived date, --pda.DepAdjDate,
DatePaid date, --cr.PrintDate
CheckNumber char(20), --cr.CheckNumber
IssueDate date, --pp.PolicyIssueDate, &?
PaymentMonth varchar(20) --pdap.yearnumber, monthnumber
);

declare @RequestProducerID int = 0,
@RequestBrokerId int = 0,
@RequestTPAId int =0,
@BegDate Date = '02/01/2015' , -- Used for Eff Date
@EndDate Date = '02/01/2015' , -- Used for Eff Date
@IsAll BIT = 0, -- Bring Everything

@Tag1Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag1 selection
@Tag2Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag2 selection
@Tag3Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag3 selection
@Tag4Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag4 selection
@Tag5Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag5 selection
@IsFullRS bit = 0 -- 1: Returns full result set; 0: Returns partical result set

declare @rc int,
@op varchar(16),
@obj varchar(64)

INSERT INTO #Policies_3

SELECT pp.plcPolicyId, dc_acct.Name AS PolicyHolder, pp.EffectiveDate , SUM(ptpd.PremiumAmt) AS PremiumAmt, ISNULL(SUM(ptc.CommAmount),0) AS CommissionAmt,

pda.DepAdjDate, cr.PrintDate, cr.CheckNumber, pp.PolicyIssueDate,
Convert(Varchar(20),pdap.Yearnumber) + '/' + Convert(varchar(20),pdap.MonthNumber) AS PaymentMonth

FROM PrmDepAdj pda

INNER JOIN PrmDepAdjPolicy pdap ON pdap.PrmDepAdjId = pda.PrmDepAdjId
INNER JOIN dbo.PlcPolicy AS pp ON pp.PlcPolicyId = pdap.PlcPolicyId
INNER JOIN dbo.DirCompany AS dc_acct ON dc_acct.DirCompanyId = pp.AccountId
INNER JOIN PrmTransPremium ptp ON ptp.PrmDepAdjPolicyId = pdap.PrmDepAdjPolicyId
Inner JOIN PlcMDSLPolicy AS pmd on pmd.PlcMDSLPolicyId = pp.PlcPolicyId
INNER JOIN PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId

INNER JOIN dbo.PrmDeposits AS pd ON PD.PrmDepAdjId = pda.PrmDepAdjId
INNER JOIN dbo.CheckRegister AS cr ON cr.CheckNumber = pd.CheckNumber

LEFT OUTER JOIN dbo.CheckExportDetail AS ced ON ced.CurrentRegisterId = cr.CheckRegisterId

INNER JOIN
( SELECT PrmTransPremiumId, SUM(Amount) AS PremiumAmt FROM dbo.PrmTransPremiumDetail GROUP BY PrmTransPremiumId) AS ptpd ON ptpd.PrmTransPremiumId = ptp.PrmTransPremiumId

LEFT OUTER JOIN dbo.PrmTransCommission AS ptc ON ptc.PrmTransPremiumId = ptp.PrmTransPremiumId

INNER JOIN dbo.CodeCoverageType AS cct ON cct.CoverageTypeId = ptp.CoverageTypeId
INNER JOIN dbo.CodeTierSchedule AS cts ON cts.TierScheduleId = ptp.TierScheduleId

WHERE

( pp.EffectiveDate BETWEEN @BegDate AND @EndDate

 AND
    (-- Bring everything 
		( 
			@IsAll = 1 
		)
	OR 
	(	(-- Broker 
                @RequestBrokerId = 0 
       OR 

				pp.BrokerId IN (select IntId from MgmtReportRequests where RequestId = @RequestBrokerId)
		)
	AND 
		(-- Producer 
			   @RequestProducerId = 0 
       OR 

				pp.ProducerId IN (select IntId from MgmtReportRequests where RequestId = @RequestProducerId)
		)

	AND 
		( --TPA
			@RequestTPAId = 0 
        OR 

			 pmd.PremiumAdministratorId IN (select IntId from MgmtReportRequests where RequestId = @RequestTPAId)
		
		)

GROUP BY pp.PlcPolicyId, dc_acct.Name, pp.EffectiveDate, pp.PolicyIssueDate , pda.DepAdjDate, cr.PrintDate, cr.CheckNumber, pdap.MonthNumber, pdap.YearNumber
ORDER BY dc_acct.Name, pp.EffectiveDate

IF @Tag1Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy Pp inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag1Id ) )

end

if @Tag2Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag2Id ) )

end

if @Tag3Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag3Id ) )

end

if @Tag4Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag4Id ) )

end

if @Tag5Id <> 0
begin
delete P
from #Policies_3 P
where P.PolicyId not in ( select PPT.PlcPolicyId
from PlcPolicy PP inner join PlcPolicyTag PPT on PPT.PlcPolicyId = PP.PlcPolicyId
where isnull( PPT.ExpirationDate, PP.ExpirationDate ) >= PP.ExpirationDate
and PPT.TagId in ( select IntId
from MgMtReportRequests
where RequestID = @Tag5Id ) )

end

SELECT * FROM #Policies_3

END

Go

EXEC spREPremAndCommPaidByPolicy '2015-02-01'

5

Thanks Scott! That really help!

6

You're welcome!

Let me explain a few "whys" of what I did.

I changed the "declare"s at the start of the proc to be in alphanum order.
Why?: To make it easier to find variables. When a proc has a long list of variables -- as many do -- it makes it much easier to find one. And so, why not?

I consolidated all the @Tag{1-5} searches and made them NOT EXISTS rather than DELETEs from the temp table.
Why? Efficiency. And removed need for temp table.

I changed the GROUP BY so that the first two columns were the same as the ORDER BY?
Why?: Efficiency. This reduces sort overhead when generating the final result.

1 Like
7

Awesome! Thanks for the explanation because I was wondering about tags, but this makes since which helps me not to deal with the temp table