V_QUERY_EVENTREGISTRANT_BALANCE

This provides the ability to query for balances for event registrants.

Fields

Field	Field Type	Null	Description
REGISTRANTID	uniqueidentifier		System record ID
BALANCE	money	yes	Balance

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  8/17/2011 2:51:10 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTREGISTRANT_BALANCE AS



                select
                    REGISTRANT.ID REGISTRANTID,
                    --REGISTRANT AMOUNT

                    cast((select sum(AMOUNT)  
                    from dbo.REGISTRANTREGISTRATION   
                    where REGISTRANTID = REGISTRANT.ID)
                    -
                    (
                        --PAID AMOUNT  

                        coalesce((select sum(EVENTREGISTRANTPAYMENT.AMOUNT)   
                        from dbo.EVENTREGISTRANTPAYMENT 
                        where EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID), 0)
                        -
                        --CREDIT AMOUNT  

                        coalesce((select sum([CREDITITEMS].[TOTAL])  
                        from dbo.[UFN_REGISTRANT_GETCREDITITEMS](REGISTRANT.ID) as [CREDITITEMS]), 0)
                    ) as money) BALANCE
                from dbo.REGISTRANT