USP_REPORT_VSEREPORT_COLLEGE
Returns the data for the VSE report (College).
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_COLLEGE
(
@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_GETBALANCEASOF(vg.revenueid, @ENDDATE) * 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 <> 1 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 <> 1 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 & MG pledge (or claim) */
vsecat.usagetypecode <> 1 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, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numCol1, amtcol1)
select 1, 1, 2, '2. For capital purposes',
isnull(count(distinct revenueid), 0),
isnull(sum(revenuesplitamount), 0)
from #vse1 where revenuetype = 1 and revenuesplitamount > 0
and vsecategorycode in (12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)
/*
No longer needed in new VSE format
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, headinglabel, colhead1, colhead2, colhead3, Colhead4, rowbefore)
values (3, 0, 2, 'Section 3a. Outright Giving: Current Operations', 'Alumni', 'Parents', 'Other Individuals ', 'Foundations', 1)
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4)
values (3, 0, 3, 'A', 'B', 'C', 'D')
/*
No longer needed in new VSE format
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 1, 0, 'A. Current Operations')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 2, 0, 'Restricted to:')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 3, 0, 'Section 3b. Outright Giving: Capital Purposes', 1)
/*
No longer needed in new VSE format
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 4, 0, 'TOTAL', 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 in (2, 3, 4, 5, 6, 7, 8, 9, 10) then 2
else 3 end,
case vc.vsecategorycode
when 0 then 1
when 2 then 2
when 3 then 3
when 4 then 4
when 5 then 5
when 6 then 6
when 7 then 7
when 8 then 8
when 9 then 9
when 10 then 10
when 12 then 13
when 13 then 14
when 14 then 15
when 15 then 16
end,
--vc.vsecategory,
case vc.vsecategorycode
when 0 then '1. Unrestricted'
when 2 then '2. Academic Divisions'
when 3 then '3. Faculty & Staff Compensation'
when 4 then '4. Research'
when 5 then '5. Public Service & Extension'
when 6 then '6. Library'
when 7 then '7. Operation & Maint. of Plant'
when 8 then '8. Student Financial Aid'
when 9 then '9. Athletics'
when 10 then '10. Other Restricted'
when 12 then '1. Prop., Build. & Equipment'
when 13 then '2. Endowment - Unrestricted'
when 15 then '4. Loan Funds'
end,
sum(case when t.vseconstituencycode in (1, 2, 3) then t.revenuesplitamount else 0 end),
sum(case when t.vseconstituencycode in (4) then t.revenuesplitamount else 0 end),
sum(case when t.vseconstituencycode in (8, 9, 10, 11) then t.revenuesplitamount else 0 end),
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, 18, 19, 20, 21, 22, 23, 24, 25, 26) and usagetypecode <> 1
group by
vc.vsecategorycode, vc.vsecategory
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3,
3,
15,
'3. Endowment - Restricted',
sum(case when t.vseconstituencycode in (1, 2, 3) then t.revenuesplitamount else 0 end),
sum(case when t.vseconstituencycode in (4) then t.revenuesplitamount else 0 end),
sum(case when t.vseconstituencycode in (8, 9, 10, 11) then t.revenuesplitamount else 0 end),
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 in (18, 19, 20, 21, 22, 23, 24, 25, 26) and usagetypecode <> 1
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 2, 11, '11. Total Restricted (add 2-10)',
1,
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
0
from #vse2 ins where linenum >=2 and linenum<=10 and sectionnum=3
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 2, 12, '12. Total Outright for Curr. Ops (add 1 & 11)', 1,
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
0
from #vse2 ins where (linenum =1 or linenum=11) and sectionnum=3
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 3, 17, '5. Total Outright for Capital Purposes', 1,
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
0
from #vse2 ins where (linenum >=13 or linenum<=16) and sectionnum=3 and subsection = 3
/*
No longer needed in new VSE format
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 4, 18, '18. Outright Gifts (add 12+17)',
sum(ins.amtcol1),
sum(ins.amtcol2),
sum(ins.amtcol3),
sum(ins.amtcol4),
0
from #vse2 ins where (linenum =12 or linenum=17) and sectionnum=3
*/
/* section 3 second set */
/*
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 5, -3, 'Section 3a. Outright Giving: Current Operations', 1)
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 5, -2, 'Section 3a. Outright Giving: Current Operations', 1, 'Corporations', 'Religious Organizations', 'Fundraising Consortia', 'Other Organizations', 'TOTAL')
insert into #vse2 (sectionnum, subsection, linenum, colhead1, colhead2, colhead3, colhead4, colhead5)
values (3, 5, -1, 'E', 'F', 'G', 'H', 'I')
/*
No longer needed for new VSE format
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 5, 0, 'A. Current Operations')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel)
values (3, 6, 0, 'Restricted to:')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 7, 0, 'Section 3a. Outright Giving: Capital Purposes', 1)
/*
No longer needed in new VSE format
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 8, 0, 'TOTAL', 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 in (2, 3, 4, 5, 6, 7, 8, 9, 10) then 6
else 7 end,
case vc.vsecategorycode
when 0 then 1
when 2 then 2
when 3 then 3
when 4 then 4
when 5 then 5
when 6 then 6
when 7 then 7
when 8 then 8
when 9 then 9
when 10 then 10
when 12 then 13
when 13 then 14
when 14 then 15
when 15 then 16
end,
--vc.vsecategory,
case vc.vsecategorycode
when 0 then '1. Unrestricted'
when 2 then '2. Academic Divisions'
when 3 then '3. Faculty & Staff Compensation'
when 4 then '4. Research'
when 5 then '5. Public Service & Extension'
when 6 then '6. Library'
when 7 then '7. Operation & Maint. of Plant'
when 8 then '8. Student Financial Aid'
when 9 then '9. Athletics'
when 10 then '10. Other Restricted'
when 12 then '1. Prop., Build., & Equipment'
when 13 then '2. Endowment - Unrestricted'
when 15 then '4. Loan Funds'
end,
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, 18, 19, 20, 21, 22, 23, 24, 25, 26) and usagetypecode <> 1
group by vc.vsecategorycode, vc.vsecategory
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3,
7,
15,
'3. Endowment - Restricted',
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 in (18, 19, 20, 21, 22, 23, 24, 25, 26) and usagetypecode <> 1
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 6, 11, '11. Total Restricted (add 2-10)', 1,
sum(ins.AmtCol1),
sum(ins.AmtCol2),
sum(ins.AmtCol3),
sum(ins.AmtCol4),
sum(ins.AmtCol5)
from #vse2 ins where linenum >=2 and linenum<=10 and sectionnum=3 and subsection=6
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 6, 12, '12. Total Outright for Curr. Ops (add 1 & 11)', 1,
sum(ins.AmtCol1),
sum(ins.AmtCol2),
sum(ins.AmtCol3),
sum(ins.AmtCol4),
sum(ins.AmtCol5)
from #vse2 ins where ((linenum =1 and subsection=5) or (linenum=11 and subsection=6)) and sectionnum=3
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 7, 17, '5. Total Outright for Capital Purposes', 1,
sum(ins.AmtCol1),
sum(ins.AmtCol2),
sum(ins.AmtCol3),
sum(ins.AmtCol4),
sum(ins.AmtCol5)
from #vse2 ins where (linenum >=13 or linenum<=16) and sectionnum=3 and subsection=7
/*
No longer needed in new VSE format
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1, amtcol2, amtcol3, amtcol4, amtcol5)
select
3, 8, 18, '18. Outright Gifts (add 12+17)',
sum(ins.AmtCol1),
sum(ins.AmtCol2),
sum(ins.AmtCol3),
sum(ins.AmtCol4),
sum(ins.AmtCol5)
from #vse2 ins where ((linenum =12 and subsection = 6) or ( subsection = 7 and linenum=17)) and sectionnum=3
*/
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) 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) 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) 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) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=4) v
where sectionnum = 3 and subsection = 6 and linenum = 4
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=5) v
where sectionnum = 3 and subsection = 6 and linenum = 5
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=6) v
where sectionnum = 3 and subsection = 6 and linenum = 6
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=7) v
where sectionnum = 3 and subsection = 6 and linenum = 7
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=8) v
where sectionnum = 3 and subsection = 6 and linenum = 8
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=9) v
where sectionnum = 3 and subsection = 6 and linenum = 9
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=10) v
where sectionnum = 3 and subsection = 6 and linenum = 10
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=11) v
where sectionnum = 3 and subsection = 6 and linenum = 11
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 2 and linenum=12) v
where sectionnum = 3 and subsection = 6 and linenum = 12
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=13) v
where sectionnum = 3 and subsection = 7 and linenum = 13
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=14) v
where sectionnum = 3 and subsection = 7 and linenum = 14
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=15) v
where sectionnum = 3 and subsection = 7 and linenum = 15
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=16) v
where sectionnum = 3 and subsection = 7 and linenum = 16
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 3 and linenum=17) v
where sectionnum = 3 and subsection = 7 and linenum = 17
update #vse2
set amtcol5 = amtcol5 + sumamt
from (select (amtcol1 + amtcol2 + amtcol3 + amtcol4) sumamt from #vse2 where sectionnum = 3 and subsection = 4 and linenum=18) v
where sectionnum = 3 and subsection = 8 and linenum = 18
/* section 3c */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 8, 0, 'Section 3c. Deferred Giving', 1)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, colhead1, colhead2, colhead3, Colhead4, rowbefore)
values (3, 9, 1, '', 'A. Alumni - FV', 'B. Alumni - PV', 'C. Parents - FV', 'D. Parents - PV', 0)
/* section 3c page 2 */
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore)
values (3, 10, -3, 'Section 3c. Deferred Giving', 1)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, rowbefore, colhead1, colhead2, colhead3, colhead4)
values (3, 10, -2, '', 1, 'E. Others - FV', 'F. Others - PV', 'G. Total Face Value', 'H. Total Present Value')
/* 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')
/*
insert into #vse2 (sectionnum, subsection, linenum, ColHead1, ColHead2, ColHead3, ColHead4)
values (4, 1, 6, 'No. of Records', 'No. Solicited', 'No. of Donors', 'Amount')
*/
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1,
colhead1, colhead2, colhead3, colhead4)
select
4, 1,
case vse.vseconstituencycode
when 1 then 2
when 2 then 3
when 3 then 4
when 4 then 6
when 8 then 7
when 9 then 8
when 11 then 9
end,
case vse.vseconstituencycode
when 1 then '1a. Alumni-Undergraduate'
when 2 then '1b. Alumni-Graduate'
when 3 then '1c. Alumni-Non degree'
when 4 then '2. Parents'
when 8 then '3. Faculty and Staff'
when 9 then '4. Students'
when 11 then '5. Other Individuals'
end,
isnull(vsecnts.numrecords, 0),
isnull(vsecnts.numsolicit, 0),
isnull(numdonor, 0),
isnull(totamt, 0),
case when vse.vseconstituencycode in (4, 8) then 'No. of Records' else null end,
case when vse.vseconstituencycode in (4, 8) then 'No. Solicited' else null end,
case when vse.vseconstituencycode in (4, 8) then 'No. of Donors' else null end,
case when vse.vseconstituencycode in (4, 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
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
left outer join dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 1) vsecnts
on vsecnts.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode in (1, 2, 3, 4, 8, 9, 11) and usagetypecode <> 1
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 1, 5, '1e. Alumni Total (add 1a-1c)',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 1 and linenum in (2, 3, 4)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 1, 10, '6. Dollar Total of rows 3-5 above',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 1 and linenum in (7, 8, 9)
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 1 then 2
when 2 then 3
when 3 then 4
when 4 then 6
when 8 then 7
when 9 then 8
when 11 then 9
end,
case vse.vseconstituencycode
when 1 then '1a. Alumni-Undergraduate'
when 2 then '1b. Alumni-Graduate'
when 3 then '1c. Alumni-Non degree'
when 4 then '2. Parents'
when 8 then '3. Faculty and Staff'
when 9 then '4. Students'
when 11 then '5. Other Individuals'
end,
isnull(vsecnts.numsolicit, 0),
isnull(numdonor, 0),
isnull(totamt, 0),
case when vse.vseconstituencycode in (4, 8) then 'No. Solicited' else null end,
case when vse.vseconstituencycode in (4, 8) then 'No. of Donors' else null end,
case when vse.vseconstituencycode in (4, 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, 2, 3, 4, 5, 6, 7, 8, 9 , 10, 11)
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
left outer join dbo.UFN_REPORT_VSECOUNTS(@STARTDATE, @ENDDATE, @INCLUDEINACTIVE, @INCLUDEDECEASED, 1) vsecnts
on vsecnts.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode in (1, 2, 3, 4, 8, 9, 11) and usagetypecode <> 1
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 2, 5, '1e. Alumni Curr. Ops. Total',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 2 and linenum in (2, 3, 4)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, numcol2, numcol3, amtcol1)
select
4, 2, 10, '6. Dollar Total of rows 3-5 above',
sum(numcol1), sum(numcol2), sum(numcol3), sum(amtcol1)
from #vse2
where sectionnum = 4 and subsection = 2 and linenum in (7, 8, 9)
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 (1, 2, 3, 4, 8, 9, 10, 11))
where vsecategory.vsecategorycode = 11
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 (1, 2, 3, 4, 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 (1, 2, 3, 4, 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 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 (1, 2, 3, 4, 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 (1, 2, 3, 4, 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 (1, 2, 3, 4, 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 (1, 2, 3, 4, 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 (1, 2, 3, 4, 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, ColHead1, ColHead2)
values (4, 5, 0, 1, 'E. Direct Governing Board Giving for All Purposes', 'No. of Donors', 'Amount' )
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 5, 1,
'1. Include current, emeritus and honorary board members',
isnull(numdonor, 0),
isnull(totamt, 0)
from
dbo.vseconstituency vse
left outer join
(
select count(distinct constituentid) "numdonor", sum(#vse1.revenuesplitamount) "totamt", vseconstituencycode
from #vse1 where #vse1.vseconstituencycode = 10
group by vseconstituencycode
) v2 on v2.vseconstituencycode = vse.vseconstituencycode
where vse.vseconstituencycode = 10
/* 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 & Other')
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 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 and revenuetype in (0, 3, 4, 5, 6, 7, 8)
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 and revenuetype in (0, 3, 4, 5, 6, 7, 8)
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 and revenuetype in (0, 3, 4, 5, 6, 7, 8)
group by constituentid order by totalamt desc
) v
where sectionnum = 4 and subsection=9 and linenum = 1
/* section 4 part 3 */
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel)
values (4, 10, -1, 1, 'Part 3. Other Fundraising Activity')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, colhead1)
values (4, 10, 0, 'A. Purposes of Gifts to Endowment: Income Restricted', 'Amount')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1)
select
4, 10,
(case vsecategory.vsecategorycode
when 18 then 2
when 19 then 3
when 20 then 4
when 21 then 5
when 22 then 6
when 23 then 7
when 24 then 8
when 25 then 9
when 26 then 10
end),
(case vsecategory.vsecategorycode
when 18 then '1. Academic Division'
when 19 then '2. Faculty and Staff Compensation'
when 20 then '3. Research'
when 21 then '4. Public Service and Extension'
when 22 then '5. Library'
when 23 then '6. Operation and Maintenance of Plant'
when 24 then '7. Student Financial Aid'
when 25 then '8. Athletics'
when 26 then '9. Other'
end),
isnull(v.Amount, 0)
from dbo.vsecategory
left outer join
(
select sum(revenuesplitamount) "Amount",
vsecategorycode
from #vse1
where vsecategorycode in (18, 19, 20, 21, 22, 23, 24, 25, 26)
group by vsecategorycode
) v on v.vsecategorycode = vsecategory.vsecategorycode
where vsecategory.vsecategorycode in (18, 19, 20, 21, 22, 23, 24, 25, 26)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, amtcol1)
select 4, 10, 11, '10. Total (add 1-9)', sum(amtcol1)
from #vse2 where sectionnum = 4 and subsection = 10 and linenum in (2, 3, 4, 5, 6, 7, 8, 9, 10)
insert into #vse2 (sectionnum, subsection, linenum, rowbefore, headinglabel, colhead1, colhead2)
values (4, 11, 0, 1, 'B. Support of Intercollegiate Athletics ONLY', 'No. of Donors', 'Amount')
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select
4, 11,
(case vsecategory.vsecategorycode
when 9 then 1
when 17 then 2
end),
(case vsecategory.vsecategorycode
when 9 then '1. Restricted to current operations'
when 17 then '2. Restricted to capital purposes'
end),
isnull(v.numdonors, 0),
isnull(v.Amount, 0)
from dbo.vsecategory
left outer join
(
select
count(distinct constituentid) "NumDonors",
sum(revenuesplitamount) "Amount",
vsecategorycode
from #vse1
where vsecategorycode in (9, 17)
group by vsecategorycode
) v on v.vsecategorycode = vsecategory.vsecategorycode
where vsecategory.vsecategorycode in (9, 17)
insert into #vse2 (sectionnum, subsection, linenum, headinglabel, numcol1, amtcol1)
select 4, 11, 3, '3. Total', sum(numcol1), sum(amtcol1)
from #vse2 where sectionnum = 4 and subsection = 11 and linenum in (1, 2)
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 = 3 where (sectionnum = 4 and subsection in (0, 1, 2, 3, 4, 5))
update #vse2 set pageno = 4 where (sectionnum = 4 and subsection in (6, 7, 8, 9, 10, 11))
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