Skip to content

Instantly share code, notes, and snippets.

@johndstein
Created February 9, 2017 20:25
Show Gist options
  • Save johndstein/d54ffa67ffd46f10c34a728eabb05022 to your computer and use it in GitHub Desktop.
Save johndstein/d54ffa67ffd46f10c34a728eabb05022 to your computer and use it in GitHub Desktop.
Raiser's Edge Gift Attributes
-- Shows some helpful info about gift attributes.
-- This SQL is for SQL Server.
DECLARE @attr varchar(MAX)
SET @attr = 'Legacy Correction'
-- Overall Count
select count(*) count
from giftattributes ga
join attributetypes at on ga.attributetypesid = at.attributetypesid
where at.description = @attr
-- Determine Picklist Values
select
count(*) count,
te.longdescription picklist
from
giftattributes ga
join attributetypes at on at.attributetypesid = ga.attributetypesid
left outer join tableentries te on ga.tableentriesid = te.tableentriesid
where
at.typeofdata = 6
and at.description = @attr
and te.longdescription is not null
and te.active = -1
group by
te.longdescription
order by te.longdescription
-- Do Objects Have Multiple Entries for the same Attribute?
select
ga.parentid giftid,
at.description name,
-- ga.sequence,
ga.text,
ga.num,
ga.datetime,
ga.currency,
ga.boolean,
ga.comments,
te.longdescription picklist,
convert(varchar, ga.attributedate, 101) date
from
giftattributes ga
-- ga.constitid is always null
-- join records r on r.id = ga.constitid
join attributetypes at on at.attributetypesid = ga.attributetypesid
left outer join tableentries te on te.tableentriesid = ga.tableentriesid
where
at.description = @attr
and 1 < (
select count(*)
from giftattributes z
join attributetypes zt on zt.attributetypesid = z.attributetypesid
where z.parentid = ga.parentid
and zt.description = @attr)
order by
ga.parentid, ga.sequence
-- Select all
select
--ga.parentid giftid,
at.description name,
--ga.sequence,
ga.text,
ga.num,
ga.datetime,
ga.currency,
ga.boolean,
ga.comments,
te.longdescription picklist,
convert(varchar, ga.attributedate, 101) date
from
giftattributes ga
-- ga.constitid is always null
-- join records r on r.id = ga.constitid
join attributetypes at on at.attributetypesid = ga.attributetypesid
left outer join tableentries te on te.tableentriesid = ga.tableentriesid
where
at.description = @attr
order by
ga.parentid, ga.sequence
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment