USP_REPORT_CHILDINVENTORY
Returns data for the sponsorship child inventory 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 | |
| @DIMENSION3 | nvarchar(100) | IN | |
| @DIMENSION3VALUE | nvarchar(255) | IN | |
| @DIMENSION3VALUE3 | nvarchar(255) | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @DATETYPE | nvarchar(15) | IN | |
| @DATERANGEDISPLAY | nvarchar(100) | IN | |
| @SPONSORSHIPOPPORTUNITYTYPECODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_CHILDINVENTORY (
@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,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@DATETYPE nvarchar(15) = null,
@DATERANGEDISPLAY nvarchar(100) = null,
@SPONSORSHIPOPPORTUNITYTYPECODE tinyint = 1
)
with execute as owner
as
set nocount on;
declare @SQLTOEXEC nvarchar(max);
declare @OSQLTOEXEC nvarchar(max);
declare @OLDSQLTOEXEC nvarchar(max);
declare @FROMCLAUSE nvarchar(max);
declare @COLUMNSELECT nvarchar(max);
declare @OLDCOLUMNSELECT nvarchar(max);
declare @WHERECLAUSE nvarchar(max);
declare @OLDWHERECLAUSE 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 @HAVING nvarchar(max) = '';
declare @HAVINGCLAUSE nvarchar(max) = '';
declare @LOCATIONCOUNT tinyint = 0;
declare @LOCATIONTYPEENUM tinyint = 2;
declare @DATECOLTEMP nvarchar(800);
declare @DIMENSIONCOLUMNS nvarchar(100) = 'DIMENSION1';
declare @RESULTSELECTION nvarchar(max);
-- reset dates
set @ENDDATE = getdate()
set @STARTDATE = DATEADD(month,1,@STARTDATE)
set @FROMCLAUSE = ' from dbo.SPONSORSHIPOPPORTUNITY OPP ';
set @FROMCLAUSE = @FROMCLAUSE + 'inner join dbo.SPONSORSHIPOPPORTUNITYGROUP SOG on SOG.ID = OPP.SPONSORSHIPOPPORTUNITYGROUPID '
set @FROMCLAUSE = @FROMCLAUSE + 'left join dbo.SPONSORSHIPOPPORTUNITYCHILD SOC on SOC.ID = OPP.ID '
set @FROMCLAUSE = @FROMCLAUSE + 'left join dbo.SPONSORSHIPOPPORTUNITYPROJECT SOP on SOP.ID = OPP.ID '
set @FROMCLAUSE = @FROMCLAUSE + 'left join dbo.CONSTITUENT CON on CON.ID = SOC.CONSTITUENTID '
if @DATETYPE not in ('0', '1')
begin
set @WHERECLAUSE = ' where (@STARTDATE is null or OPP.LASTDOCUMENTATIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) ' +
' and (@ENDDATE is null or OPP.LASTDOCUMENTATIONDATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and ';
set @OLDWHERECLAUSE = ' where (OPP.LASTDOCUMENTATIONDATE < @STARTDATE or OPP.LASTDOCUMENTATIONDATE is null) and '
end
else
begin
set @WHERECLAUSE = ' where '
set @OLDWHERECLAUSE = 'where '
end
set @WHERECLAUSE = @WHERECLAUSE + 'SOG.SPONSORSHIPOPPORTUNITYTYPECODE = @SPONSORSHIPOPPORTUNITYTYPECODE '
set @OLDWHERECLAUSE = @OLDWHERECLAUSE + 'SOG.SPONSORSHIPOPPORTUNITYTYPECODE = @SPONSORSHIPOPPORTUNITYTYPECODE '
set @COLUMNSELECT = 'select dbo.UFN_REPORT_PROCESSDATE_PARAM('+ @DATETYPE + ',OPP.LASTDOCUMENTATIONDATE) DATECOL, datename(year, OPP.LASTDOCUMENTATIONDATE) YEARCOL';
set @OLDCOLUMNSELECT = 'select ''Older'' DATECOL, ''Older'' YEARCOL';
-- 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
-- only process dimension if it is not null and is not Location Type
if @DIMENSION1 is not null
begin
if @DIMENSION1 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_CHILDINVENTORY_PROCESSDIMENSION
@DIMENSION1,
'@DIMENSION1VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION1',
@HAVING output
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
set @COLUMNSELECT = @COLUMNSELECT + ',' + @COLUMN ;
set @OLDCOLUMNSELECT = @OLDCOLUMNSELECT + ',' + @COLUMN ;
set @WHERECLAUSE += @WHERE + ' and @DIMENSION1VALUE3 is null'
set @OLDWHERECLAUSE += @WHERE + ' and @DIMENSION1VALUE3 is null'
set @GROUPBYCLAUSE = 'DIMENSION1' ;
if @DIMENSION1VALUE is not null
set @HAVINGCLAUSE = @HAVINGCLAUSE + @HAVING;
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
set @HAVING = '';
end
end
-- only process dimension if it's not null and is not Location Type
if @DIMENSION2 is not null
begin
set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION2';
if @DIMENSION2 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_CHILDINVENTORY_PROCESSDIMENSION
@DIMENSION2,
'@DIMENSION2VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION2',
@HAVING output
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + ',' + @COLUMN ;
set @OLDCOLUMNSELECT = @OLDCOLUMNSELECT + ',' + @COLUMN ;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE;
set @OLDWHERECLAUSE = @OLDWHERECLAUSE + @WHERE;
if @GROUPBYCLAUSE is not null
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION2';
else
set @GROUPBYCLAUSE = 'DIMENSION2';
if @HAVINGCLAUSE is not null and @DIMENSION2VALUE is not null
set @HAVINGCLAUSE = @HAVINGCLAUSE + @HAVING;
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
set @HAVING = '';
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION2VALUE is null';
set @OLDWHERECLAUSE = @OLDWHERECLAUSE + ' and @DIMENSION2VALUE is null';
end
--
if @DIMENSION3 is not null
begin
set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION3';
if @DIMENSION3 <> @LOCATIONTYPEENUM
begin
exec dbo.USP_REPORT_CHILDINVENTORY_PROCESSDIMENSION
@DIMENSION3,
'@DIMENSION3VALUE',
@FROM output,
@COLUMN output,
@WHERE output,
'DIMENSION3',
@HAVING output
if @FROM is not null
begin
set @FROMCLAUSE = @FROMCLAUSE + @FROM;
end
set @COLUMNSELECT = @COLUMNSELECT + ',' + @COLUMN ;
set @OLDCOLUMNSELECT = @OLDCOLUMNSELECT + ',' + @COLUMN ;
set @WHERECLAUSE = @WHERECLAUSE + @WHERE;
set @OLDWHERECLAUSE = @OLDWHERECLAUSE + @WHERE;
if @GROUPBYCLAUSE is not null
set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION3' ;
else
set @GROUPBYCLAUSE = 'DIMENSION3';
if @HAVINGCLAUSE is not null and @DIMENSION3VALUE is not null
set @HAVINGCLAUSE = @HAVINGCLAUSE + @HAVING;
--
-- set these to null for the next row
--
set @FROM = '';
set @COLUMN = '';
set @WHERE = '';
set @HAVING = '';
end
end
else
begin
set @WHERECLAUSE = @WHERECLAUSE + ' and @DIMENSION3VALUE is null';
set @OLDWHERECLAUSE = @OLDWHERECLAUSE + ' and @DIMENSION3VALUE is null';
end
--After we know how many location types have been set, max of 3 dimensions, we can now
-- construct the sql to handle the location types.
-- construct the sql query
-- handle processing of location types
If @LOCATIONCOUNT > 0
begin
exec dbo.USP_REPORT_CHILDINVENTORY_PROCESSLOCATIONTYPE_DIMENSION
@LOCATIONCOUNT,
@DIMENSION1,
@DIMENSION2,
@DIMENSION3,
@COLUMNSELECT,
@OLDCOLUMNSELECT,
@FROMCLAUSE,
@WHERECLAUSE,
@OLDWHERECLAUSE,
@GROUPBYCLAUSE,
@SQLTOEXEC output,
@OSQLTOEXEC output,
'count(*) CNT'
set @SQLTOEXEC = @SQLTOEXEC + @HAVINGCLAUSE
set @OSQLTOEXEC = @OSQLTOEXEC + @HAVINGCLAUSE
set @OLDSQLTOEXEC = 'insert #RESULT select * from (' + @OSQLTOEXEC + ') as QRYOLD';
end
else
begin
set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBYCLAUSE;
set @SQLTOEXEC = @SELECT + ' from (' + @COLUMNSELECT + ' ' + @FROMCLAUSE + ' ' + @WHERECLAUSE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBYCLAUSE + @HAVINGCLAUSE;
set @OLDSQLTOEXEC = 'insert #RESULT select * from (' + replace(@SELECT,'DATECOL, YEARCOL',' ''Older'' as DATECOL1, ''Older'' as YEARCOL1') +
' from (' + @COLUMNSELECT + @FROMCLAUSE + @OLDWHERECLAUSE + ') as RESOLD ' +
' group by ' + @GROUPBYCLAUSE + @HAVINGCLAUSE + ') as QRYOLD';
end
if @DATETYPE not in ('0', '1')
begin
set @DATECOLTEMP = dbo.UFN_REPORT_BUILD_DATECOL_TABLE_STRING(@DATETYPE)
-- append to the query the code that handles blanc column value
set @RESULTSELECTION = ' delete from #DATECOL where DATECOL+YEARCOL in (select DATECOL+YEARCOL from #RESULT) ' +
' insert into #RESULT (DATECOL, YEARCOL, CNT, ' + @DIMENSIONCOLUMNS + ') ' +
' select distinct DATEC , YEARC , 0, ' + @DIMENSIONCOLUMNS +
' from (select #DATECOL.DATECOL as DATEC, #DATECOL.YEARCOL as YEARC, #RESULT.* from #DATECOL cross join #RESULT) as CROSSJOIN ' +
@OLDSQLTOEXEC +
' select CNT, DATECOL, YEARCOL, ' + @DIMENSIONCOLUMNS + ' from #RESULT '
end
else
begin
set @DATECOLTEMP = ''
set @RESULTSELECTION = ' select CNT, DATECOL, YEARCOL, ' + @DIMENSIONCOLUMNS + ' from #RESULT '
end
set @SQLTOEXEC = @DATECOLTEMP + ' select * into #RESULT from (' +@SQLTOEXEC + ') as RES ' + @RESULTSELECTION + ' drop table #RESULT'
-- execute the query
exec sp_executesql @SQLTOEXEC,
N'@DIMENSION1VALUE nvarchar(255), @DIMENSION2VALUE nvarchar(255), @DIMENSION3VALUE nvarchar(255), @STARTDATE datetime, @ENDDATE datetime, @DIMENSION1VALUE3 nvarchar(255), @DIMENSION2VALUE3 nvarchar(255), @DIMENSION3VALUE3 nvarchar(255), @SPONSORSHIPOPPORTUNITYTYPECODE tinyint',
@DIMENSION1VALUE=@DIMENSION1VALUE, @DIMENSION2VALUE=@DIMENSION2VALUE, @DIMENSION3VALUE=@DIMENSION3VALUE, @STARTDATE = @STARTDATE,@ENDDATE = @ENDDATE,@DIMENSION1VALUE3=@DIMENSION1VALUE3, @DIMENSION2VALUE3=@DIMENSION2VALUE3, @DIMENSION3VALUE3=@DIMENSION3VALUE3,@SPONSORSHIPOPPORTUNITYTYPECODE = @SPONSORSHIPOPPORTUNITYTYPECODE;