Raiser's Edge Gift Attributes

re-attributes.sql

-- 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