V_QUERY_BILLINGITEMCOSTHISTORY

Lets a user query the cost history of a
Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
BILLINGITEMID	uniqueidentifier		Billing item ID
ACADEMICYEARNAME	nvarchar(max)	yes	Academic year
STARTDATE	date	yes	Start date
ENDDATE	date	yes	End date
PRICE	money		Cost
GRADELEVELDESCRIPTION	nvarchar(100)		Grade
DATERANGETYPE	nvarchar(14)	yes	Valid for
SCHOOLNAME	nvarchar(100)		School
Definition

Copy
/*
Generated by Blackbaud AppFx Platform
Date:  11/30/2010 11:19:49 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BILLINGITEMCOSTHISTORY AS



                select
                    BILLINGITEMPRICE.ID,
                    BILLINGITEMPRICE.BILLINGITEMID,
                    ACADEMICYEAR_ALLPOSSIBLEBYDATE.NAME as ACADEMICYEARNAME,
                    BILLINGITEMPRICE.STARTDATE,
                    BILLINGITEMPRICE.ENDDATE,
                    BILLINGITEMPRICE.PRICE,
                    '' as GRADELEVELDESCRIPTION,
                    BILLINGITEMPRICE.DATERANGETYPE,
                    '' as SCHOOLNAME                    
                from dbo.BILLINGITEMPRICE
                    left outer join dbo.UFN_GETBILLINGCYCLES() as ACADEMICYEAR_ALLPOSSIBLEBYDATE
                        on (BILLINGITEMPRICE.STARTDATE between ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE and ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE) or
                           (BILLINGITEMPRICE.ENDDATE between ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE and ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE) or
                           ((BILLINGITEMPRICE.STARTDATE < ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE) and (BILLINGITEMPRICE.ENDDATE > ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE)) or
                           ((BILLINGITEMPRICE.STARTDATE is null) and (BILLINGITEMPRICE.ENDDATE is null))

                union

                select
                    BILLINGITEMDATES.ID,
                    BILLINGITEMDATES.BILLINGITEMID,
                    ACADEMICYEAR_ALLPOSSIBLEBYDATE.NAME as ACADEMICYEARNAME,
                    BILLINGITEMDATES.STARTDATE,
                    BILLINGITEMDATES.ENDDATE,
                    BILLINGITEMPRICEBYGRADELEVEL.PRICE,
                    GRADELEVEL.DESCRIPTION as GRADELEVELDESCRIPTION,
                    BILLINGITEMDATES.DATERANGETYPE,
                    '' as SCHOOLNAME
                from dbo.BILLINGITEMDATES
                    inner join dbo.BILLINGITEMPRICEBYGRADELEVEL
                        on BILLINGITEMPRICEBYGRADELEVEL.BILLINGITEMDATESID = BILLINGITEMDATES.ID
                    inner join dbo.GRADELEVEL
                        on BILLINGITEMPRICEBYGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
                    left outer join dbo.UFN_GETBILLINGCYCLES() as ACADEMICYEAR_ALLPOSSIBLEBYDATE
                        on (BILLINGITEMDATES.STARTDATE between ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE and ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE) or
                           (BILLINGITEMDATES.ENDDATE between ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE and ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE) or
                           ((BILLINGITEMDATES.STARTDATE < ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE) and (BILLINGITEMDATES.ENDDATE > ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE)) or
                           ((BILLINGITEMDATES.STARTDATE is null) and (BILLINGITEMDATES.ENDDATE is null))

                union

                select
                    BILLINGITEMDATES.ID,
                    BILLINGITEMDATES.BILLINGITEMID,
                    ACADEMICYEAR_ALLPOSSIBLEBYDATE.NAME as ACADEMICYEARNAME,
                    BILLINGITEMDATES.STARTDATE,
                    BILLINGITEMDATES.ENDDATE,
                    BILLINGITEMPRICEBYSCHOOL.PRICE,
                    '' as GRADELEVELDESCRIPTION,
                    BILLINGITEMDATES.DATERANGETYPE,
                    CONSTITUENT.KEYNAME as SCHOOLNAME
                from dbo.BILLINGITEMDATES
                    inner join dbo.BILLINGITEMPRICEBYSCHOOL
                        on BILLINGITEMPRICEBYSCHOOL.BILLINGITEMDATESID = BILLINGITEMDATES.ID
                    inner join dbo.CONSTITUENT
                        on BILLINGITEMPRICEBYSCHOOL.SCHOOLID = CONSTITUENT.ID
                    left outer join dbo.UFN_GETBILLINGCYCLES() as ACADEMICYEAR_ALLPOSSIBLEBYDATE
                        on (BILLINGITEMDATES.STARTDATE between ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE and ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE) or
                           (BILLINGITEMDATES.ENDDATE between ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE and ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE) or
                           ((BILLINGITEMDATES.STARTDATE < ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGSTARTDATE) and (BILLINGITEMDATES.ENDDATE > ACADEMICYEAR_ALLPOSSIBLEBYDATE.BILLINGENDDATE)) or
                           ((BILLINGITEMDATES.STARTDATE is null) and (BILLINGITEMDATES.ENDDATE is null))