USP_REPORT_PASTDUE
Provides counts of all the past due sponsorships given a number of months.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DIMENSION1 | nvarchar(100) | IN | |
| @DIMENSION1VALUE | nvarchar(255) | IN | |
| @DIMENSION1VALUE3 | nvarchar(255) | IN | |
| @DIMENSION2 | nvarchar(100) | IN | |
| @DIMENSION2VALUE | nvarchar(255) | IN | |
| @DIMENSION2VALUE3 | nvarchar(255) | IN | |
| @DIMENSION3 | nvarchar(100) | IN | |
| @DIMENSION3VALUE | nvarchar(255) | IN | |
| @DIMENSION3VALUE3 | nvarchar(255) | IN | |
| @TYPE | int | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_PASTDUE (
@DIMENSION1 as nvarchar(100) = null,
@DIMENSION1VALUE as nvarchar(255) = null,
@DIMENSION1VALUE3 as nvarchar(255) = null,
@DIMENSION2 as nvarchar(100) = null,
@DIMENSION2VALUE as nvarchar(255) = null,
@DIMENSION2VALUE3 as nvarchar(255) = null,
@DIMENSION3 as nvarchar(100) = null,
@DIMENSION3VALUE as nvarchar(255) = null,
@DIMENSION3VALUE3 as nvarchar(255) = null,
@TYPE int = null
)
with execute as owner
as
set nocount on;
declare @SQL nvarchar(max)='';
declare @LOCATIONCOUNT tinyint=0;
declare @LOCATIONTYPEENUM tinyint = 2;
-- count how many dimension parameters have location type specified
if @DIMENSION1 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
if @DIMENSION2 is not null and @DIMENSION2 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
if @DIMENSION3 is not null and @DIMENSION3 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
declare @SQLTOEXEC nvarchar(max);
declare @FROMCLAUSE nvarchar(max);
declare @COLUMNSELECT nvarchar(max);
declare @WHERECLAUSE nvarchar(max);
declare @REVENUEJOIN nvarchar(max);
declare @GROUPBYCLAUSE nvarchar(max);
declare @SELECT nvarchar(max);
declare @FROM nvarchar(max);
declare @COLUMN nvarchar(max);
declare @WHERE nvarchar(max);
set @FROMCLAUSE = 'from dbo.SPONSORSHIP SP '+
' inner join dbo.REVENUESPLIT SPLT on SPLT.ID = SP.REVENUESPLITID ' +
' inner join dbo.REVENUE RV on RV.ID = SPLT.REVENUEID ';
if @TYPE=2
begin
set @WHERECLAUSE = ' where SP.STATUSCODE=1 '
set @COLUMNSELECT = 'select case when dbo.UFN_RECURRINGGIFT_GETPASTDUEINSTALLMENTCOUNT(RV.ID,getdate(), 0) >12 then 13 else dbo.UFN_RECURRINGGIFT_GETPASTDUEINSTALLMENTCOUNT(RV.ID,getdate(), 0) end DATECOL, 1 YEARCOL ';
end
if @TYPE=1
begin
set @WHERECLAUSE = ' where SP.STATUSCODE=1 '
set @COLUMNSELECT = 'select case when dbo.UFN_SPONSORSHIPPASTDUEREPORT_GETMONTHS_2(RV.ID, 0) > 12 then 13 else dbo.UFN_SPONSORSHIPPASTDUEREPORT_GETMONTHS_2(RV.ID, 0) end DATECOL, 1 YEARCOL ';
end
-- only process dimension if not null and not Location Type
if @DIMENSION1 is not null and @DIMENSION1 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION1,
'@DIMENSION1VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION1'
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE += @WHERE + ' and @DIMENSION1VALUE3 is null';
set @GROUPBYCLAUSE = 'DIMENSION1';
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
end
-- only process dimension if it's not null and is not Location Type
if @DIMENSION2 is not null
begin
if @DIMENSION2 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION2,
'@DIMENSION2VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION2'
-- for channel and appeal from clause is not used.
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE + ' and @DIMENSION2VALUE3 is null';
if @GROUPBYCLAUSE is not null
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION2';
else
set @GROUPBYCLAUSE = 'DIMENSION2';
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION2VALUE is null and @DIMENSION2VALUE3 is null';
end
--
if @DIMENSION3 is not null
begin
if @DIMENSION3 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION3,
'@DIMENSION3VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION3'
-- for channel and appeal from clause is not used.
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE + ' and @DIMENSION3VALUE3 is null';
if @GROUPBYCLAUSE is not null
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION3';
else
set @GROUPBYCLAUSE = 'DIMENSION3';
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION3VALUE is null and @DIMENSION3VALUE3 is null';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
@DIMENSION3,
@COLUMNSELECT,
@FROMCLAUSE,
@WHERECLAUSE,
@GROUPBYCLAUSE,
@SQLTOEXEC output,
'count(*) CNT'
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBYCLAUSE;
-- construct the sql query
set @SQLTOEXEC = @SELECT + ' from (' + @COLUMNSELECT + ' ' + @FROMCLAUSE + ' ' + @WHERECLAUSE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBYCLAUSE;
end
set @SQL = @SQLTOEXEC;
declare @DIMENSIONSTRING nvarchar(255)
declare @RESULTS nvarchar(255)
declare @RESULTSCOLUMN nvarchar(255)
if @DIMENSION2 is null
set @RESULTS = 'create table #TEMP_PASTDUERESULTS(CNT int ,DATECOL int,YEARCOL int,DIMENSION1 nvarchar(255)) '
if @DIMENSION2 is not null and @DIMENSION3 is null
set @RESULTS = 'create table #TEMP_PASTDUERESULTS(CNT int ,DATECOL int,YEARCOL int,DIMENSION1 nvarchar(255),DIMENSION2 nvarchar(255)) '
if @DIMENSION3 is not null
set @RESULTS = 'create table #TEMP_PASTDUERESULTS(CNT int ,DATECOL int,YEARCOL int,DIMENSION1 nvarchar(255),DIMENSION2 nvarchar(255),DIMENSION3 nvarchar(255)) '
if @DIMENSION2 is null
set @DIMENSIONSTRING = 'DIMENSION1'
if @DIMENSION2 is not null and @DIMENSION3 is null
set @DIMENSIONSTRING = 'DIMENSION1,DIMENSION2'
if @DIMENSION3 is not null
set @DIMENSIONSTRING ='DIMENSION1,DIMENSION2,DIMENSION3'
if @LOCATIONCOUNT > 0 and convert(tinyint,@DIMENSION1)<>2
begin
if @LOCATIONCOUNT >1
begin
set @DIMENSIONSTRING ='DIMENSION2,DIMENSION3,DIMENSION1'
end
else
begin
if @DIMENSION2 is null
set @DIMENSIONSTRING = 'DIMENSION1'
if @DIMENSION2 is not null and @DIMENSION3 is null and convert(tinyint,@DIMENSION2) = 2
set @DIMENSIONSTRING = 'DIMENSION2,DIMENSION1'
if @DIMENSION3 is not null and convert(tinyint,@DIMENSION3) = 2
set @DIMENSIONSTRING ='DIMENSION3,DIMENSION1,DIMENSION2'
if convert(tinyint,@DIMENSION2) = 2 and @DIMENSION3 is not null
begin
set @DIMENSIONSTRING='DIMENSION2,DIMENSION1,DIMENSION3';
end
end
end
if @DIMENSION2 is null
set @RESULTSCOLUMN = '#TEMP_PASTDUERESULTS.DIMENSION1'
if @DIMENSION2 is not null and @DIMENSION3 is null
set @RESULTSCOLUMN = '#TEMP_PASTDUERESULTS.DIMENSION1,#TEMP_PASTDUERESULTS.DIMENSION2'
if @DIMENSION3 is not null
set @RESULTSCOLUMN='#TEMP_PASTDUERESULTS.DIMENSION1,#TEMP_PASTDUERESULTS.DIMENSION2,#TEMP_PASTDUERESULTS.DIMENSION3'
if @LOCATIONCOUNT > 0
set @SQL = @RESULTS +
'insert into #TEMP_PASTDUERESULTS('+@DIMENSIONSTRING+',CNT,DATECOL,YEARCOL) ' + @SQL
else
set @SQL = @RESULTS +
'insert into #TEMP_PASTDUERESULTS(CNT,DATECOL,YEARCOL,'+@DIMENSIONSTRING+') ' + @SQL
declare @SQLTEMP nvarchar(max) = ' create table #TEMP_PASTDUE (CNT int ,DATECOL int) '+
' insert into #TEMP_PASTDUE values(0,1) '+
' insert into #TEMP_PASTDUE values(0,2) '+
' insert into #TEMP_PASTDUE values(0,3) '+
' insert into #TEMP_PASTDUE values(0,4) '+
' insert into #TEMP_PASTDUE values(0,5) '+
' insert into #TEMP_PASTDUE values(0,6) '+
' insert into #TEMP_PASTDUE values(0,7) '+
' insert into #TEMP_PASTDUE values(0,8) '+
' insert into #TEMP_PASTDUE values(0,9) '+
' insert into #TEMP_PASTDUE values(0,10) '+
' insert into #TEMP_PASTDUE values(0,11) '+
' insert into #TEMP_PASTDUE values(0,12) ' +
' insert into #TEMP_PASTDUE values(0,13) '
set @SQL = @SQL + @SQLTEMP
set @SQL = @SQL + ' delete from #TEMP_PASTDUE where DATECOL in (select DATECOL from #TEMP_PASTDUERESULTS) ';
/*
--if @LOCATIONCOUNT > 0
--set @SQL = @SQL + 'insert into #TEMP_PASTDUERESULTS(CNT,DATECOL,YEARCOL,'+@DIMENSIONSTRING+') ';
--else
-- set @SQL = @SQL + 'insert into #TEMP_PASTDUERESULTS('+@DIMENSIONSTRING+',CNT,DATECOL,YEARCOL) ';
*/
set @SQL = @SQL + ' select ''0'' as CNT ,DATECOL as DATECOL,DATECOL as YEARCOL,'+@DIMENSIONSTRING+' from (select #TEMP_PASTDUE.DATECOL, #TEMP_PASTDUERESULTS.CNT,'+ @RESULTSCOLUMN +' from #TEMP_PASTDUE cross join #TEMP_PASTDUERESULTS) as CROSSJOIN union select CNT,case when DATECOL IS null then 0 else DATECOL end as DATECOL,YEARCOL,'+@DIMENSIONSTRING+' from #TEMP_PASTDUERESULTS where DATECOL<>0 order by CROSSJOIN.DATECOL ';
-- execute the query
exec sp_executesql @SQL,
N'@DIMENSION2VALUE nvarchar(255), @DIMENSION1VALUE nvarchar(255), @DIMENSION3VALUE nvarchar(255), @DIMENSION1VALUE3 nvarchar(255), @DIMENSION2VALUE3 nvarchar(255), @DIMENSION3VALUE3 nvarchar(255)',
@DIMENSION2VALUE=@DIMENSION2VALUE, @DIMENSION1VALUE=@DIMENSION1VALUE, @DIMENSION3VALUE=@DIMENSION3VALUE, @DIMENSION1VALUE3=@DIMENSION1VALUE3, @DIMENSION2VALUE3=@DIMENSION2VALUE3, @DIMENSION3VALUE3=@DIMENSION3VALUE3 ;