USP_REPORT_VSEREPORT_SECONDARY
Returns the data for the VSE report (Secondary).
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @INCLUDEINACTIVE | smallint | IN | |
| @INCLUDEDECEASED | smallint | IN | |
| @USEGIFTDATE | smallint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_VSEREPORT_SECONDARY
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@INCLUDEINACTIVE smallint = 0,
@INCLUDEDECEASED smallint = 0,
@USEGIFTDATE smallint = 0
)
with execute as owner
as
set nocount on;
create table #vse2
(
sectionnum integer,
subsection integer,
linenum integer,
headinglabel varchar(100) collate DATABASE_DEFAULT,
colhead1 varchar(100) collate DATABASE_DEFAULT,
colhead2 varchar(100) collate DATABASE_DEFAULT,
colhead3 varchar(100) collate DATABASE_DEFAULT,
colhead4 varchar(100) collate DATABASE_DEFAULT,
colhead5 varchar(100) collate DATABASE_DEFAULT,
colhead6 varchar(100) collate DATABASE_DEFAULT, /* for elementary schools */
amtcol1 money,
amtcol2 money,
amtcol3 money,
amtcol4 money,
amtcol5 money,
amtcol6 money, /* for elementary schools */
numcol1 integer,
numcol2 integer,
numcol3 integer,
rowbefore smallint default(0),
rowafter smallint default(0),
boldheading smallint default(0),
pageno smallint default(1),
vsereporttype smallint default(0),
cuid1 uniqueidentifier,
cuid2 uniqueidentifier
)
select distinct
vg.date,
vg.postdate,
case when vg.transactiontypecode = 1 then
(dbo.UFN_PLEDGE_GETBALANCE(vg.revenueid) * vg.revenuesplitamount) / (case when vg.revenueamount > 0 then vg.revenueamount else 1 end)
else
vg.revenuesplitamount
end "revenuesplitamount",
vsecat.vsecategorycode,
vsecat.vsecategory,
vsecons.vseconstituencycode,
vsecons.vseconstituency,
vsecons.type "vseconstituencytype",
vg.constituentid,
vg.transactiontypecode "revenuetype",
vg.revenueid,
vg.revenuesplitid,
vg.paymentmethodcode,
dcons.id "deceasedconstituentid"
into #vse1
from
dbo.UFN_REPORT_VSECATEGORY(@STARTDATE, @ENDDATE, @USEGIFTDATE) vg
inner join dbo.vsecategory vsecat on vsecat.id = vg.vsecategoryid
inner join dbo.constituent cons with (nolock) on cons.id = vg.constituentid
inner join (
select distinct
vc.vseconstituencycode,
vc.vseconstituency,
vc.type,
cc.constituentid
--,cc.datefrom,
--cc.dateto
from dbo.vseconstituencymap vsemap
inner join dbo.vseconstituency vc on vc.id = vsemap.VSECONSTITUENCYID
inner join dbo.UFN_REPORT_VALIDCONSCODE(@STARTDATE, @ENDDATE) cc on cc.id = vsemap.constituencyid
inner join dbo.constituent cons1 with (nolock) on cons1.id = cc.constituentid
where vc.usagetypecode <> 0 and
vc.typecode = (case when cons1.isorganization = 0 then 0 else 1 end) and
vc.sequence =
(select min(vc2.sequence)
from dbo.vseconstituencymap vsemap2
inner join dbo.vseconstituency vc2 on vc2.id = vsemap2.VSECONSTITUENCYID
inner join dbo.UFN_REPORT_VALIDCONSCODE(@STARTDATE, @ENDDATE) cc2
on cc2.id = vsemap2.constituencyid
inner join dbo.constituent cons2 with (nolock) on cons2.id = cc2.constituentid
where
vc2.typecode = (case when cons2.isorganization = 0 then 0 else 1 end) and
vc2.usagetypecode <> 0 and cc2.constituentid = cc.constituentid)
) vsecons on vsecons.constituentid = vg.constituentid
/* may need to validate gift date between constituency code date range */
left outer join dbo.deceasedconstituent dcons on dcons.id = cons.id
where
vg.transactiontypecode not in (2, 3) and /* no rec. gift or MG pledge (claim) */
vsecat.usagetypecode <> 0 and
(case when @STARTDATE is null then
0
else
datediff(day, @STARTDATE, case when @USEGIFTDATE <> 0 then vg.date else isnull(vg.postdate, vg.date) end) end) >= 0
and (case when @STARTDATE is null then
0
else
datediff(day, @ENDDATE, case when @USEGIFTDATE <> 0 then vg.date else isnull(vg.postdate, vg.date) end) end) <= 0
and case when @INCLUDEINACTIVE <> 0 then 0 else cons.isinactive end = 0
and case when @INCLUDEDECEASED <> 0 then
cons.id
else (case when dcons.id is null then cons.id else null end) end = cons.id
/* section 1A. Outstanding Pledges */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, colhead1, colhead2)
values (1, 1, 0, 'Section 1A. Outstanding Pledges', 'No. of Pledges', 'Amount')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numCol1, amtCol1)
select 1, 1, 1, '1. For current operations',
isnull(count(distinct revenueid), 0),
isnull(sum(revenuesplitamount), 0)
from #vse1 where revenuetype = 1 and revenuesplitamount > 0
and vsecategorycode in (0, 1, 11)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numCol1, amtcol1)
select 1, 1, 2, '2. For capital operations',
isnull(count(distinct revenueid), 0),
isnull(sum(revenuesplitamount), 0)
from #vse1 where revenuetype = 1 and revenuesplitamount > 0
and vsecategorycode in (12, 13, 14, 15, 16, 17)
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
1, 1, 3, '3. Total',
sum(ins.numcol1),
sum(ins.amtcol1)
from #vse2 ins where linenum in (1, 2) and sectionnum=1 and subsection = 1
*/
delete from #vse1 where revenuetype = 1
/* current operations unrestricted */
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 0, 1, 'Section 3a. Outright Giving: Current Operations', 1)
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 0, 2, 'Alumni', 'Parents', 'Grandparents', 'Other Individuals', 'Foundations')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, colhead1, colhead2, colhead3, colhead4, colhead5, rowbefore)
values (3, 0, 2, 'Section 3a. Outright Giving: Current Operations', 'Alumni', 'Parents', 'Grandparents', 'Other Individuals ', 'Foundations', 1)
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 0, 3, 'A', 'B', 'B.1', 'C', 'D')
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 1, 0, 'A. Current Operations')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 3, 0, 'Section 3b. Outright Giving: Capital Purposes', 1)
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 4, 0, 'TOTAL OUTRIGHT GIFTS', 1)
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3,
case when vc.vsecategorycode = 0 then 1
when vc.vsecategorycode = 1 then 2
else 3 end,
case vc.vsecategorycode
when 0 then 1
when 1 then 2
when 12 then 4
when 13 then 5
when 14 then 6
when 15 then 7
end,
--vc.vsecategory,
case vc.vsecategorycode
when 0 then '1. Total Unrestricted'
when 1 then '2. Total Restricted'
when 12 then '1. Prop., Build. & Equipment'
when 13 then '2. Endowment - Unrestricted'
when 14 then '3. Endowment - Restricted'
when 15 then '4. Loan Funds'
end,
isnull(sum(case when t.vseconstituencycode = 0 then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (5, 6) then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (7) then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (8, 9, 10, 11) then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (12, 13) then t.revenuesplitamount else 0 end),0)
from dbo.vsecategory vc
left outer join #vse1 t on t.vsecategorycode = vc.vsecategorycode
where vc.vsecategorycode not in (11, 16, 17) and usagetypecode <> 0
group by vc.vsecategorycode, vc.vsecategory
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 2, 3, '3. Total Outright Current Operations',
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtCol4),
sum(ins.amtCol5)
from #vse2 ins where (linenum =1 or linenum=2) and sectionnum=3
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 3, 8, '5. Total Outright Capital Purposes',
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
sum(ins.amtcol5)
from #vse2 ins where (linenum >=4 or linenum<=7) and sectionnum=3 and subsection = 3
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 4, 9, '9. Curr. Ops&Cap. Purp.(add 3+8)',
sum(ins.AmtCol1),
sum(ins.AmtCol2),
sum(ins.AmtCol3),
sum(ins.AmtCol4),
0
from #vse2 ins where (linenum =3 or linenum=8) and sectionnum=3
*/
/* section 3 second set */
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 5, -3, 'Section 3a. Outright Giving: Current Operations')
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5, colhead6)
values (3, 5, -2, 'Foundations', 'Corporations', 'Religious Orgs. ', 'Fundraising Consortia', 'Other Orgs.', 'TOTAL')
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5, colhead6)
values (3, 5, -1, 'D', 'E', 'F', 'G', 'H', 'I')
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 5, -2, 'Corporations', 'Religious Orgs. ', 'Fundraising Consortia', 'Other Orgs.', 'TOTAL')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 5, -2, 'Section 3a. Outright Giving: Current Operations', 'Corporations', 'Religious Orgs. ', 'Fundraising Consortia', 'Other Orgs.', 'TOTAL')
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 5, -1, 'E', 'F', 'G', 'H', 'I')
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 5, 0, 'A. Current Operations')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 7, 0, 'Section 3b. Outright Giving: Capital Purposes', 1)
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 8, 0, 'TOTAL OUTRIGHT GIFTS', 1)
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3,
case
when vc.vsecategorycode = 0 then 5
when vc.vsecategorycode = 1 then 6
else 7 end,
case vc.vsecategorycode
when 0 then 1
when 1 then 2
when 12 then 4
when 13 then 5
when 14 then 6
when 15 then 7
end,
--vc.vsecategory,
case vc.vsecategorycode
when 0 then '1. Total Unrestricted'
when 1 then '2. Total Restricted'
when 12 then '1. Prop., Build. & Equipment'
when 13 then '2. Endowment - Unrestricted'
when 14 then '3. Endowment - Restricted'
when 15 then '4. Loan Funds'
end,
/*isnull(sum(case when t.vseconstituencycode in (12, 13) then t.revenuesplitamount else 0 end),0), */
isnull(sum(case when t.vseconstituencycode in (14) then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (15) then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (16) then t.revenuesplitamount else 0 end),0),
isnull(sum(case when t.vseconstituencycode in (17) then t.revenuesplitamount else 0 end), 0),
isnull(sum(case when t.vseconstituencycode in (14, 15, 16, 17) then t.revenuesplitamount else 0 end), 0)
from dbo.vsecategory vc
left outer join #vse1 t on t.vsecategorycode = vc.vsecategorycode
where vc.vsecategorycode not in (11, 16, 17) and usagetypecode <> 0
group by vc.vsecategorycode, vc.vsecategory
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 6, 3, '3. Total Outright Current Operations',
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
sum(ins.amtcol5)
--sum(ins.AmtCol6)
from #vse2 ins where ((linenum =1 and subsection=5) or (linenum=2 and subsection=6)) and sectionnum=3
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 7, 8, '5. Total Outright Capital Purposes',
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
sum(ins.amtcol5)
--sum(ins.AmtCol6)
from #vse2 ins where (linenum >=4 or linenum<=7) and sectionnum=3 and subsection=7
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5, amtcol6)
select
3, 8, 9, '9. Curr. Ops&Cap. Purp.(add 3+8)',
sum(ins.AmtCol1),
sum(ins.AmtCol2),
sum(ins.AmtCol3),
sum(ins.AmtCol4),
sum(ins.AmtCol5),
sum(ins.AmtCol6)
from #vse2 ins where ((linenum =3 and subsection = 6) or ( subsection = 7 and linenum=8)) and sectionnum=3
*/
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 1 and linenum=1) v
where sectionnum = 3 and subsection = 5 and linenum = 1
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=2) v
where sectionnum = 3 and subsection = 6 and linenum = 2
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=3) v
where sectionnum = 3 and subsection = 6 and linenum = 3
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=4) v
where sectionnum = 3 and subsection = 7 and linenum = 4
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=5) v
where sectionnum = 3 and subsection = 7 and linenum = 5
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=6) v
where sectionnum = 3 and subsection = 7 and linenum = 6
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=7) v
where sectionnum = 3 and subsection = 7 and linenum = 7
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=8) v
where sectionnum = 3 and subsection = 7 and linenum = 8
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4 + amtcol5) sumamt from #vse2 where sectionnum = 3 and subsection = 4 and linenum=9) v
where sectionnum = 3 and subsection = 8 and linenum = 9
/* section 4 */
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel)
values (4, 0, 0, 1, 'Section 4a Additional Details on Section 3 - Individuals')
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel)
values (4, 1, -1, 1, 'Part 1. Contributions from Individuals')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, ColHead1, ColHead2, ColHead3, ColHead4)
values (4, 1, 0, 'A. Contributions from Individuals for All purposes', 'No. of Records', 'No. Solicited', 'No. of Donors', 'Amount')
/* The Parents represents both current parents and past parents, vseconscode = 5 in this following sql
would calculate both cur parent (code = 5) and past parent (code = 6)
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1, colhead1, colhead2, colhead3, colhead4)
select
4, 1,
case vse.vseconstituencycode
when 0 then 1
when 5 then 2
when 7 then 3
when 8 then 4
when 9 then 5
when 11 then 6
end,
case vse.vseconstituencycode
when 0 then '1. Alumni (required)'
when 5 then '2. Parents'
when 7 then '3. Grandparents'
when 8 then '4. Faculty and Staff'
when 9 then '5. Students'
when 11 then '6. Other Individuals'
end,
isnull(vsecnts.numrecords, 0),
isnull(vsecnts.numsolicit, 0),
isnull(v2.numdonor, 0),
isnull(v2.totamt, 0),
case when vse.vseconstituencycode in (5, 7, 8) then 'No. of Records' else null end,
case when vse.vseconstituencycode in (5, 7, 8) then 'No. Solicited' else null end,
case when vse.vseconstituencycode in (5, 7, 8) then 'No. of Donors' else null end,
case when vse.vseconstituencycode in (5, 7, 8) then 'Amount' else null end
from
dbo.vseconstituency vse
left outer join
(
select count(distinct constituentid) "numdonor", sum(#vse1.revenuesplitamount) "totamt",
case when vseconstituencycode = 6 then 5 else vseconstituencycode end "vseconstituencycode"
from #vse1
where #vse1.vseconstituencycode in (0, 5, 6, 7, 8, 9, 11)
group by (case when vseconstituencycode = 6 then 5 else vseconstituencycode end)
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
left outer join
(
select
case when vseconstituencycode = 6 then 5 else vseconstituencycode end "vseconstituencycode",
sum(numrecords) numrecords,
sum(numsolicit) numsolicit
from dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 0)
group by (case when vseconstituencycode = 6 then 5 else vseconstituencycode end)
) vsecnts on vsecnts.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode in (0, 5, 7, 8, 9, 11) and vse.usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 1, 7, 'Dollar Total of rows 4-6 above', --'7. Total (add 4-6)',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 1 and linenum in (4, 5, 6)
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, ColHead1, ColHead2, ColHead3)
values (4, 2, 0, 1, 'B. Contributions from Individuals for Current Operations Only', 'No. Solicited', 'No. of Donors', 'Amount')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, amtcol1,
colhead1, colhead2, colhead3)
select
4, 2,
case vse.vseconstituencycode
when 0 then 1
when 5 then 2
when 6 then 3
when 7 then 5
when 8 then 6
when 9 then 7
when 11 then 8
end,
case vse.vseconstituencycode
when 0 then '1. Alumni'
when 5 then '2a. Current Parents'
when 6 then '2b. Past Parents'
when 7 then '3. Grandparents'
when 8 then '4. Faculty and Staff'
when 9 then '5. Students'
when 11 then '6. Other Individuals'
end,
isnull(vsecnts.numsolicit, 0),
isnull(numdonor, 0),
isnull(totamt, 0),
case when vse.vseconstituencycode in (5, 7, 8) then 'No. Solicited' else null end,
case when vse.vseconstituencycode in (5, 7, 8) then 'No. of Donors' else null end,
case when vse.vseconstituencycode in (5, 7, 8) then 'Amount' else null end
from
dbo.vseconstituency vse
left outer join
(
select count(distinct constituentid) "numdonor", sum(#vse1.revenuesplitamount) "totamt", vseconstituencycode
from #vse1 where
vsecategorycode in (0, 1, 11)
and vseconstituencycode in (0, 5, 6, 7, 8, 9, 11)
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
left outer join dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 0)
vsecnts on vsecnts.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode in (0, 5, 6, 7, 8, 9, 11) and usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 2, 4, '2. Total Parents (add 2a-2b)',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 2 and linenum in (2, 3)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 2, 9, 'Dollar Total of rows 4-6 above', --'7. Total (add 4-6)',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 2 and linenum in (6, 7, 8)
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, ColHead1, ColHead2)
values (4, 3, 0, 1, 'C. Bequests - REQUIRED', 'No. of Donors', 'Amount')
/* may need a gift subtype or type in criteria */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 3, 1,
'1. Bequests for Current Operations',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount) , 0)
from dbo.vsecategory
left outer join #vse1 on (#vse1.vsecategorycode = vsecategory.vsecategorycode
and #vse1.vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11))
where vsecategory.vsecategorycode = 11 and usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 3, 2,
'2. Bequests for Capital Purposes',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount),0)
from dbo.vsecategory
left outer join #vse1 on (#vse1.vsecategorycode = vsecategory.vsecategorycode
and #vse1.vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11))
where vsecategory.vsecategorycode = 16
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 3, 3,
'4. Total Bequests',
sum(numcol1),
sum(amtcol1)
from #vse2 where sectionnum=4 and subsection = 3 and linenum in (1, 2)
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, ColHead1, ColHead2, Colhead3)
values (4, 4, 0, 1, 'D. Three Largest Donor Totals from Individuals - REQUIRED', 'Largest', '2nd Largest', '3rd Largest')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3)
select
4, 4, 1,
'1. From living individuals', 0, 0, 0
update #vse2
set amtcol1 = totalamt, cuid1 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11)
and #vse1.deceasedconstituentid is null and #vse1.vsecategorycode not in (11, 16)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=4 and linenum = 1
update #vse2
set amtcol2 = totalamt, cuid2 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=4 and ins.linenum = 1)
and vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11) and #vse1.vsecategorycode not in (11, 16)
and #vse1.deceasedconstituentid is null
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=4 and linenum = 1
update #vse2
set amtcol3 = totalamt
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where
constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=4 and ins.linenum = 1)
and constituentid <> (select cuid2 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=4 and ins.linenum = 1)
and vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11) and #vse1.vsecategorycode not in (11, 16)
and #vse1.deceasedconstituentid is null
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=4 and linenum = 1
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3)
select
4, 4, 2,
'2. Through estate settlements (bequests)', 0, 0, 0
update #vse2
set amtcol1 = totalamt, cuid1 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11) and #vse1.vsecategorycode in (11, 16)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=4 and linenum = 2
update #vse2
set amtcol2 = totalamt, cuid2 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=4 and ins.linenum = 2)
and #vse1.vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11) and #vse1.vsecategorycode in (11, 16)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=4 and linenum = 2
update #vse2
set amtcol3 = totalamt
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where
constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=4 and ins.linenum = 2)
and constituentid <> (select cuid2 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=4 and ins.linenum = 2)
and #vse1.vseconstituencycode in (0, 5, 6, 7, 8, 9, 10, 11) and #vse1.vsecategorycode in (11, 16)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=4 and linenum = 2
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel)
values (4, 5, 0, 1, 'E. Direct Governing Board Giving for All Purposes')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1)
select
4, 5, 1,
'Number of Governing Board Members',
isnull(vsecnts.numrecords, 0)
from
dbo.vseconstituency vse
left outer join
dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 0) vsecnts
on vsecnts.vseconstituencycode = 10 and vsecnts.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode = 10 and usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1)
select
4, 5, 2,
'Number of Governing Board Donors',
isnull(v2.numdonor, 0)
from
dbo.vseconstituency vse
left outer join
(
select count(distinct constituentid) "numdonor", vseconstituencycode
from #vse1 where #vse1.vseconstituencycode = 10
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode = 10 and usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1)
select
4, 5, 3,
'Amount given for Current Operations',
isnull(amount, 0)
from
dbo.vseconstituency vse
left outer join
(
select sum(revenuesplitamount) "amount", vseconstituencycode
from #vse1
where #vse1.vseconstituencycode = 10 and vsecategorycode in (0, 1, 11)
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode = 10 and usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1)
select
4, 5, 4,
'Amount given for Capital Purposes',
isnull(amount, 0)
from
dbo.vseconstituency vse
left outer join
(
select sum(revenuesplitamount) "amount", vseconstituencycode
from #vse1
where #vse1.vseconstituencycode = 10 and vsecategorycode in (12, 13, 14, 15, 16, 17)
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode = 10 and usagetypecode <> 0
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1)
select
4, 5, 5,
'Total Amount Given', sum(amtcol1)
from #vse2 where sectionnum = 4 and subsection = 5 and linenum in (3, 4)
/* Part 2 subsection 6*/
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel)
values (4, 6, -2, 1, 'Section 4b. Additional Details on Section 3 - Organizations')
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel)
values (4, 6, -1, 1, 'Part 2. Contributions from Organizations')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, ColHead1, ColHead2)
values (4, 6, 0, 'A. Foundations', 'No. of Donors', 'Amount')
/* Foundations - Personal and family */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 6, 1, '1. Personal and Family',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount), 0)
from dbo.vseconstituency
left outer join #vse1 on #vse1.vseconstituencycode = vseconstituency.vseconstituencycode
where vseconstituency.vseconstituencycode = 12
/* Foundations - other */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 6, 2, '2. Other foundations and trusts, excluding corporate',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount), 0)
from dbo.vseconstituency
left outer join #vse1 on #vse1.vseconstituencycode = vseconstituency.vseconstituencycode
where vseconstituency.vseconstituencycode = 13
/* Foundations totals */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 6, 3, '3. Total',
sum(numcol1),
sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 6 and linenum in (1, 2)
/* section 4 B. Three largest donor totals from Foundations */
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, ColHead1, ColHead2, ColHead3)
values (4, 7, 0, 1, 'B. Three Largest Donor Totals from Foundations - REQUIRED', 'Largest', '2nd Largest', '3rd Largest')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3)
select 4, 7, 1,
'1. Foundations',
0, 0, 0
update #vse2
set amtcol1 = totalamt, cuid1 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where vseconstituencycode in (12, 13)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=7 and linenum = 1
update #vse2
set amtcol2 = totalamt, cuid2 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=7 and ins.linenum = 1)
and #vse1.vseconstituencycode in (12, 13)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=7 and linenum = 1
update #vse2
set amtcol3 = totalamt
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where
constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=7 and ins.linenum = 1)
and constituentid <> (select cuid2 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=7 and ins.linenum = 1)
and #vse1.vseconstituencycode in (12, 13)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=7 and linenum = 1
/* section 4 C. From Corporations */
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, ColHead1, ColHead2)
values (4, 8, 0, 1, 'C. Corporations 1. Forms of Giving', 'No. of Donors', 'Amount')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select 4, 8, 1,
'1. Cash and securities (exclusive of matching gifts)',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount), 0)
from dbo.vseconstituency
left outer join #vse1 on #vse1.vseconstituencycode = vseconstituency.vseconstituencycode
where vseconstituency.vseconstituencycode = 14
and #vse1.revenuetype <> 8 and paymentmethodcode <> 5
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select 4, 8, 2, '2. Company Products',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount), 0)
from dbo.vseconstituency
left outer join #vse1 on #vse1.vseconstituencycode = vseconstituency.vseconstituencycode
where vseconstituency.vseconstituencycode = 14
and #vse1.revenuetype in (0, 3, 4, 5, 6, 7, 8)
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select 4, 8, 3, '3. Other company property',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount), 0)
from dbo.vseconstituency
left outer join #vse1 on #vse1.vseconstituencycode = vseconstituency.vseconstituencycode
where vseconstituency.vseconstituencycode = 14
and #vse1.revenuetype <> 8 and #vse1.paymentmethodcode = 5
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select 4, 8, 4, '4. Matching gifts',
isnull(count(distinct constituentid), 0),
isnull(sum(revenuesplitamount), 0)
from dbo.vseconstituency
left outer join #vse1 on #vse1.vseconstituencycode = vseconstituency.vseconstituencycode
where vseconstituency.vseconstituencycode = 14
and #vse1.revenuetype = 8
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select 4, 8, 5, '5. Total', sum(numcol1), sum(amtcol1)
from #vse2 where sectionnum = 4 and subsection = 8 and linenum in (1, 2, 3, 4)
/* section 4 D. Three largest donors from corporations */
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, ColHead1, ColHead2, ColHead3)
values (4, 9, 0, 1, 'D. Three Largest Donor Totals from Corporations - REQUIRED', 'Largest', '2nd Largest', '3rd Largest')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3)
select 4, 9, 1, '1. Corporations', 0, 0, 0
update #vse2
set amtcol1 = totalamt, cuid1 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where vseconstituencycode = 14
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=9 and linenum = 1
update #vse2
set amtcol2 = totalamt, cuid2 = v.constituentid
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=9 and ins.linenum = 1)
and vseconstituencycode = 14
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=9 and linenum = 1
update #vse2
set amtcol3 = totalamt
from (
select Top 1 sum(revenuesplitamount) totalamt, constituentid from #vse1
where
constituentid <> (select cuid1 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=9 and ins.linenum = 1)
and constituentid <> (select cuid2 from #vse2 ins where ins.sectionnum = 4 and ins.subsection=9 and ins.linenum = 1)
and vseconstituencycode = 14
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=9 and linenum = 1
update #vse2 set pageno = 1 where sectionnum = 1 or (sectionnum = 3 and subsection in (0, 1, 2, 3, 4))
update #vse2 set pageno = 2 where (sectionnum = 3 and subsection in (5, 6, 7, 8))
update #vse2 set pageno = 2 where (sectionnum = 4 and subsection in (0, 1))
update #vse2 set pageno = 3 where (sectionnum = 4 and subsection in (2, 3, 4, 5))
update #vse2 set pageno = 4 where (sectionnum = 4 and subsection in (6, 7, 8, 9))
update #vse2 set vsereporttype = 1
select
sectionnum,
subsection,
linenum,
headinglabel,
colhead1,
colhead2,
colhead3,
colhead4,
colhead5,
colhead6,
amtcol1,
amtcol2,
amtcol3,
amtcol4,
amtcol5,
amtcol6,
numCol1,
numcol2,
numcol3,
rowbefore,
rowafter,
vsereporttype,
boldheading, pageno
from #vse2 order by pageno, sectionnum, subsection, linenum
drop table #vse1
drop table #vse2