USP_REPORT_OPERATIONS
Returns data for the sponsorship operations report.
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 | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @DATETYPE | nvarchar(15) | IN | |
| @DATERANGEDISPLAY | nvarchar(100) | IN | |
| @NEWASSIGNMENT | bit | IN | |
| @CANCELLATIONS | bit | IN | |
| @TERMINATIONS | bit | IN | |
| @TRANSFERIN | bit | IN | |
| @TRANSFEROUT | bit | IN | |
| @REASSIGNMENTS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_OPERATIONS (
@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,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@DATETYPE nvarchar(15) = null,
@DATERANGEDISPLAY nvarchar(100) = null,
@NEWASSIGNMENT bit = null,
@CANCELLATIONS bit = null,
@TERMINATIONS bit = null,
@TRANSFERIN bit = null,
@TRANSFEROUT bit = null,
@REASSIGNMENTS bit = null
)
with execute as owner
as
set nocount on;
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);
declare @GROUPBY nvarchar(max);
declare @LOCATIONCOUNT tinyint = 0;
declare @LOCATIONTYPEENUM tinyint = 2;
declare @SQLTOEXEC1 nvarchar(max);
declare @SQLTOEXEC2 nvarchar(max);
declare @SQLTOEXEC3 nvarchar(max);
declare @SQLTOEXEC4 nvarchar(max);
declare @SQLTOEXEC5 nvarchar(max);
declare @SQLTOEXEC6 nvarchar(max);
---Blank value data columns
declare @DATECOLTEMP nvarchar(800);
declare @DIMENSIONCOLUMNS nvarchar(100);
declare @RESULTSELECTION nvarchar(2000);
set @DATECOLTEMP = dbo.UFN_REPORT_BUILD_DATECOL_TABLE_STRING(@DATETYPE)
-- add dimtransaction as always a select column in the result
set @DIMENSIONCOLUMNS = 'DIMTRANSACTION'
--- Build query strings
set @FROMCLAUSE = 'from dbo.SPONSORSHIP SP ';
set @WHERECLAUSE = 'where (@STARTDATE is null or SPT1.TRANSACTIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) ' +
' and (@ENDDATE is null or SPT1.TRANSACTIONDATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) ';
if @DIMENSION1 in (1,3,4,5) OR @DIMENSION2 in (1,3,4,5)
begin
set @REVENUEJOIN = ' inner join dbo.REVENUESPLIT SPLT on SPLT.ID = SP.REVENUESPLITID ' +
' inner join dbo.REVENUE RV on RV.ID = SPLT.REVENUEID ';
end
if @REVENUEJOIN is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @REVENUEJOIN;
end
set @COLUMNSELECT = 'select dbo.UFN_REPORT_PROCESSDATE_PARAM('+ @DATETYPE + ',SPT1.TRANSACTIONDATE) DATECOL, datename(year, SPT1.TRANSACTIONDATE) YEARCOL';
-- count how many dimension parameters have location type specified
if @DIMENSION1 is not null and @DIMENSION1 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
if @DIMENSION2 is not null and @DIMENSION2 = @LOCATIONTYPEENUM
begin
set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
end
-- only process dimension if not null and not Location Type
if @DIMENSION1 is not null
begin
-- append it to the columns in the result
set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION1'
if @DIMENSION1 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_PROCESSDIMENSION
@DIMENSION1,
'@DIMENSION1VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION1'
if @FROM is not null and @DIMENSION1 <> 15
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE + ' and @DIMENSION1VALUE3 is null';
if @GROUPBYCLAUSE is not null
begin
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION1';
end
else
begin
set @GROUPBYCLAUSE = 'DIMENSION1';
end
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION1VALUE is null and @DIMENSION1VALUE3 is null';
end
-- only process dimension if it's not null and is not Location Type
if @DIMENSION2 is not null
begin
-- append it to the columns in the result
set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION2'
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 and @DIMENSION2 <> 15
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE + ' and @DIMENSION2VALUE3 is null';
if @GROUPBYCLAUSE is not null
begin
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION2';
end
else
begin
set @GROUPBYCLAUSE = 'DIMENSION2';
end
--
-- 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
--PROCESS NEWASSIGNMENTS
if @NEWASSIGNMENT = 1
begin
--deal with the reason from clause - add it after the transaction join
if @DIMENSION1 = 15 or @DIMENSION2 = 15
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.TARGETSPONSORSHIPID inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
end
else
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.TARGETSPONSORSHIPID ';
end
set @WHERE = @WHERECLAUSE + ' and ACTIONCODE=0 ';
set @COLUMN = @COLUMNSELECT + ', ''Acquisitions'' as DIMTRANSACTION '
if @GROUPBYCLAUSE is not null
begin
set @GROUPBY = @GROUPBYCLAUSE + ', DIMTRANSACTION';
end
else
begin
set @GROUPBY ='DIMTRANSACTION';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
null,
@COLUMN,
@FROM,
@WHERE,
@GROUPBY,
@SQLTOEXEC1 output
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBY;
-- construct the sql query
set @SQLTOEXEC1 = @SELECT + ' from (' + @COLUMN + ' ' + @FROM + ' ' + @WHERE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBY;
end
end
--PROCESS CANCELLATIONS
if @CANCELLATIONS = 1
begin
--deal with the reason from clause - add it after the transaction join
if @DIMENSION1 = 15 or @DIMENSION2 = 15
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.CONTEXTSPONSORSHIPID inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
end
else
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.CONTEXTSPONSORSHIPID ';
end
set @WHERE = @WHERECLAUSE + ' and ACTIONCODE=2 ';
set @COLUMN = @COLUMNSELECT + ', ''Cancellations'' as DIMTRANSACTION '
if @GROUPBYCLAUSE is not null
begin
set @GROUPBY = @GROUPBYCLAUSE + ', DIMTRANSACTION';
end
else
begin
set @GROUPBY ='DIMTRANSACTION';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
null,
@COLUMN,
@FROM,
@WHERE,
@GROUPBY,
@SQLTOEXEC2 output
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBY;
-- construct the sql query
set @SQLTOEXEC2 = @SELECT + ' from (' + @COLUMN + ' ' + @FROM + ' ' + @WHERE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBY;
end
end
--PROCESS TERMINATIONS
if @TERMINATIONS = 1
begin
--deal with the reason from clause - add it after the transaction join
if @DIMENSION1 = 15 or @DIMENSION2 = 15
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.CONTEXTSPONSORSHIPID inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
end
else
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.CONTEXTSPONSORSHIPID ';
end
set @WHERE = @WHERECLAUSE + ' and ACTIONCODE=3 ';
set @COLUMN = @COLUMNSELECT + ', ''Terminations'' as DIMTRANSACTION '
if @GROUPBYCLAUSE is not null
begin
set @GROUPBY = @GROUPBYCLAUSE + ', DIMTRANSACTION';
end
else
begin
set @GROUPBY ='DIMTRANSACTION';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
null,
@COLUMN,
@FROM,
@WHERE,
@GROUPBY,
@SQLTOEXEC3 output
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBY;
-- construct the sql query
set @SQLTOEXEC3 = @SELECT + ' from (' + @COLUMN + ' ' + @FROM + ' ' + @WHERE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBY;
end
end
--PROCESS TRANSFER OUT
if @TRANSFEROUT = 1
begin
--deal with the reason from clause - add it after the transaction join
if @DIMENSION1 = 15 or @DIMENSION2 = 15
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.CONTEXTSPONSORSHIPID inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
end
else
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.CONTEXTSPONSORSHIPID ';
end
set @WHERE = @WHERECLAUSE + ' and ACTIONCODE in (1,7,8) ';
set @COLUMN = @COLUMNSELECT + ', ''Transfers out'' as DIMTRANSACTION '
if @GROUPBYCLAUSE is not null
begin
set @GROUPBY = @GROUPBYCLAUSE + ', DIMTRANSACTION';
end
else
begin
set @GROUPBY ='DIMTRANSACTION';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
null,
@COLUMN,
@FROM,
@WHERE,
@GROUPBY,
@SQLTOEXEC4 output
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBY;
-- construct the sql query
set @SQLTOEXEC4 = @SELECT + ' from (' + @COLUMN + ' ' + @FROM + ' ' + @WHERE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBY;
end
end
--PROCESS TRANSFER IN
if @TRANSFERIN = 1
begin
--deal with the reason from clause - add it after the transaction join
if @DIMENSION1 = 15 or @DIMENSION2 = 15
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.TARGETSPONSORSHIPID inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
end
else
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.TARGETSPONSORSHIPID ';
end
set @WHERE = @WHERECLAUSE + ' and ACTIONCODE in (1,7,8) ';
set @COLUMN = @COLUMNSELECT + ', ''Transfers in'' as DIMTRANSACTION '
if @GROUPBYCLAUSE is not null
begin
set @GROUPBY = @GROUPBYCLAUSE + ', DIMTRANSACTION';
end
else
begin
set @GROUPBY ='DIMTRANSACTION';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
null,
@COLUMN,
@FROM,
@WHERE,
@GROUPBY,
@SQLTOEXEC5 output
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBY;
-- construct the sql query
set @SQLTOEXEC5 = @SELECT + ' from (' + @COLUMN + ' ' + @FROM + ' ' + @WHERE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBY;
end
end
---REASSIGNMENTS
if @REASSIGNMENTS = 1
begin
--deal with the reason from clause - add it after the transaction join
if @DIMENSION1 = 15 or @DIMENSION2 = 15
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.TARGETSPONSORSHIPID inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
end
else
begin
set @FROM = @FROMCLAUSE + ' inner join dbo.SPONSORSHIPTRANSACTION SPT1 on SP.ID = SPT1.TARGETSPONSORSHIPID ';
end
set @WHERE = @WHERECLAUSE + ' and ACTIONCODE = 5 and SPT1.TRANSACTIONSEQUENCE in (select MAX(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQ where MAXSEQ.TARGETSPONSORSHIPID = SP.ID and MAXSEQ.ACTIONCODE =5) ';
set @COLUMN = @COLUMNSELECT + ', ''Reassignments'' as DIMTRANSACTION '
if @GROUPBYCLAUSE is not null
begin
set @GROUPBY = @GROUPBYCLAUSE + ', DIMTRANSACTION';
end
else
begin
set @GROUPBY ='DIMTRANSACTION';
end
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
null,
@COLUMN,
@FROM,
@WHERE,
@GROUPBY,
@SQLTOEXEC6 output
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBY;
-- construct the sql query
set @SQLTOEXEC6 = @SELECT + ' from (' + @COLUMN + ' ' + @FROM + ' ' + @WHERE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBY;
end
end
if @SQLTOEXEC1 is not null
begin
set @SQLTOEXEC = @SQLTOEXEC1
end
if @SQLTOEXEC2 is not null
begin
if @SQLTOEXEC <> ''
begin
set @SQLTOEXEC = @SQLTOEXEC + ' union all '+ @SQLTOEXEC2
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC2
end
end
if @SQLTOEXEC3 is not null
begin
if @SQLTOEXEC <> ''
begin
set @SQLTOEXEC = @SQLTOEXEC + ' union all '+ @SQLTOEXEC3
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC3
end
end
if @SQLTOEXEC4 is not null
begin
if @SQLTOEXEC <> ''
begin
set @SQLTOEXEC = @SQLTOEXEC + ' union all '+ @SQLTOEXEC4
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC4
end
end
if @SQLTOEXEC5 is not null
begin
if @SQLTOEXEC <> ''
begin
set @SQLTOEXEC = @SQLTOEXEC + ' union all '+ @SQLTOEXEC5
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC5
end
end
if @SQLTOEXEC6 is not null
begin
if @SQLTOEXEC <> ''
begin
set @SQLTOEXEC = @SQLTOEXEC + ' union all '+ @SQLTOEXEC6
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC6
end
end
-- append to the query the code that handles blanc column values
set @RESULTSELECTION =' delete from #DATECOL where DATECOL+YEARCOL in (select DATECOL+YEARCOL from #RESULT) ' +
' insert into #RESULT (DATECOL, YEARCOL, CNT, ' + @DIMENSIONCOLUMNS + ') ' +
' select DATEC , YEARC , 0, ' + @DIMENSIONCOLUMNS +
' from (select #DATECOL.DATECOL as DATEC, #DATECOL.YEARCOL as YEARC, #RESULT.* from #DATECOL cross join #RESULT) as CROSSJOIN ' +
' select CNT, DATECOL, YEARCOL, ' + @DIMENSIONCOLUMNS + ' from #RESULT '
set @SQLTOEXEC = @DATECOLTEMP + ' select * into #RESULT from (' +@SQLTOEXEC + ') as RES ' + @RESULTSELECTION
--insert into tudortest (str, date, sqlexec, stardate, enddate) values (@SQLTOEXEC, getdate(),'TOTAL', @STARTDATE, @ENDDATE);
-- execute the query
exec sp_executesql @SQLTOEXEC,
N'@DIMENSION1VALUE nvarchar(255), @DIMENSION2VALUE nvarchar(255), @STARTDATE datetime, @ENDDATE datetime, @DIMENSION1VALUE3 nvarchar(255), @DIMENSION2VALUE3 nvarchar(255)',
@DIMENSION1VALUE=@DIMENSION1VALUE, @DIMENSION2VALUE=@DIMENSION2VALUE, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @DIMENSION1VALUE3=@DIMENSION1VALUE3, @DIMENSION2VALUE3=@DIMENSION2VALUE3 ;