V_QUERY_SPONSORSHIPGREATESTNEEDCHILD
Returns the subset of fields on a child used for determining greatest need.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ID | |
| SPONSORSHIPOPPORTUNITYGROUPID | uniqueidentifier | SPONSORSHIPOPPORTUNITYGROUPID | |
| SPONSORSHIPLOCATIONID | uniqueidentifier | Location | |
| GENDERCODE | tinyint | Gender | |
| BIRTHDATE | UDT_FUZZYDATE | Birth date | |
| AGE | int | yes | Age |
| SPONSORSHIPOPPORTUNITYAGERANGEID | uniqueidentifier | yes | Age range |
| ISHIVPOSITIVE | bit | HIV positive | |
| SPROPPCHILDCONDITIONCODEID | uniqueidentifier | yes | Disability/Illness |
| ISORPHANED | bit | Orphaned | |
| NUMBERACTIVESPONSORS | int | yes | Active sponsor count |
| DATEADDED | datetime | Date added | |
| LASTDOCUMENTATIONDATE | date | yes | Last documentation date |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 4/13/2016 4:58:46 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.158.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPGREATESTNEEDCHILD AS
select
SPONSORSHIPOPPORTUNITY.ID,
SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID,
SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID,
CONSTITUENT.GENDERCODE,
CONSTITUENT.BIRTHDATE,
CONSTITUENT.AGE,
SPONSORSHIPOPPORTUNITYAGERANGE.ID SPONSORSHIPOPPORTUNITYAGERANGEID,
SPONSORSHIPOPPORTUNITYCHILD.ISHIVPOSITIVE,
SPONSORSHIPOPPORTUNITYCHILD.SPROPPCHILDCONDITIONCODEID,
SPONSORSHIPOPPORTUNITYCHILD.ISORPHANED,
(
select count(1)
from dbo.SPONSORSHIP
where
SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID
and STATUSCODE = 1
) as NUMBERACTIVESPONSORS,
SPONSORSHIPOPPORTUNITY.DATEADDED,
SPONSORSHIPOPPORTUNITY.LASTDOCUMENTATIONDATE
from dbo.SPONSORSHIPOPPORTUNITY
inner join dbo.SPONSORSHIPOPPORTUNITYLOCK on SPONSORSHIPOPPORTUNITYLOCK.ID = SPONSORSHIPOPPORTUNITY.ID
inner join dbo.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIPOPPORTUNITY.ID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = SPONSORSHIPOPPORTUNITYCHILD.CONSTITUENTID
left outer join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
left outer join dbo.SPONSORSHIPOPPORTUNITYAGERANGE on CONSTITUENT.BIRTHDATE <> '00000000' and CONSTITUENT.AGE between SPONSORSHIPOPPORTUNITYAGERANGE.MINAGE and SPONSORSHIPOPPORTUNITYAGERANGE.MAXAGE
where
SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE = 1
and SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE = 0
and SPONSORSHIPOPPORTUNITYLOCK.LOCKED = 0