USP_REPORT_CAMPAIGNPRIORITYPROGRESSBYQUARTER

            CREATE procedure dbo.USP_REPORT_CAMPAIGNPRIORITYPROGRESSBYQUARTER
            (
                @CAMPAIGNID uniqueidentifier,
                @CAMPAIGNPRIORITYTYPECODEID uniqueidentifier = null,
                @STARTDATE datetime,
                @ENDDATE datetime
            )
            as
                set nocount on;

                select distinct
                    CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITY_ID,
                    dbo.UFN_CAMPAIGNPRIORITYTYPECODE_GETDESCRIPTION(CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID) as CAMPAIGNPRIORITY_TYPE,
                    case PRIORITYPROGRESS.SEQUENCE
                        when 1 then
                            case PRIORITYPROGRESS.TOTALQUARTERS
                                when 1 then
                                    dbo.UFN_CAMPAIGNPRIORITY_GETTOTALRECEIVED(CAMPAIGNPRIORITY.ID, @STARTDATE, @ENDDATE)
                                else
                                    dbo.UFN_CAMPAIGNPRIORITY_GETTOTALRECEIVED(CAMPAIGNPRIORITY.ID,@STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                                end
                        when PRIORITYPROGRESS.TOTALQUARTERS then
                                dbo.UFN_CAMPAIGNPRIORITY_GETTOTALRECEIVED(CAMPAIGNPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)),@ENDDATE)
                            else
                                dbo.UFN_CAMPAIGNPRIORITY_GETTOTALRECEIVED(CAMPAIGNPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                    end as CAMPAIGNPRIORITY_TOTALRECEIVED,
                    case PRIORITYPROGRESS.SEQUENCE
                        when 1 then
                            case PRIORITYPROGRESS.TOTALQUARTERS
                                when 1 then
                                    dbo.UFN_CAMPAIGNPRIORITY_GETTOTALBALANCE(CAMPAIGNPRIORITY.ID,@STARTDATE,@ENDDATE)
                                else
                                    dbo.UFN_CAMPAIGNPRIORITY_GETTOTALBALANCE(CAMPAIGNPRIORITY.ID,@STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                                end
                        when PRIORITYPROGRESS.TOTALQUARTERS then
                            dbo.UFN_CAMPAIGNPRIORITY_GETTOTALBALANCE(CAMPAIGNPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)),@ENDDATE)
                        else
                            dbo.UFN_CAMPAIGNPRIORITY_GETTOTALBALANCE(CAMPAIGNPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                    end as CAMPAIGNPRIORITY_TOTALBALANCE,
                    CAMPAIGNPRIORITY.GOAL as CAMPAIGNPRIORITY_GOAL,
                    PRIORITYPROGRESS.YEARNAME as CAMPAIGNPRIORITY_DATEYEAR,
                    PRIORITYPROGRESS.QUARTER as CAMPAIGNPRIORITY_DATEQUARTER,
                    CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITY_ID,
                    CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as CAMPAIGNSUBPRIORITY_NAME,
                    case SUBPRIORITYPROGRESS.SEQUENCE
                        when 1 then
                            case SUBPRIORITYPROGRESS.TOTALQUARTERS
                                when 1 then
                                    dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVED(CAMPAIGNSUBPRIORITY.ID, @STARTDATE, @ENDDATE)
                                else
                                    dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVED(CAMPAIGNSUBPRIORITY.ID,@STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                                end
                        when SUBPRIORITYPROGRESS.TOTALQUARTERS then
                                dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVED(CAMPAIGNSUBPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)),@ENDDATE)
                            else
                                dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVED(CAMPAIGNSUBPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                    end as CAMPAIGNSUBPRIORITY_TOTALRECEIVED,
                    case SUBPRIORITYPROGRESS.SEQUENCE
                        when 1 then
                            case SUBPRIORITYPROGRESS.TOTALQUARTERS
                                when 1 then
                                    dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALBALANCE(CAMPAIGNSUBPRIORITY.ID,@STARTDATE,@ENDDATE)
                                else
                                    dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALBALANCE(CAMPAIGNSUBPRIORITY.ID,@STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                                end
                        when SUBPRIORITYPROGRESS.TOTALQUARTERS then
                            dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALBALANCE(CAMPAIGNSUBPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)),@ENDDATE)
                        else
                            dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALBALANCE(CAMPAIGNSUBPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                    end as CAMPAIGNSUBPRIORITY_TOTALBALANCE,
                    CAMPAIGNSUBPRIORITY.GOAL as CAMPAIGNSUBPRIORITY_GOAL,
                    SUBPRIORITYPROGRESS.YEARNAME as CAMPAIGNSUBPRIORITY_DATEYEAR,
                    SUBPRIORITYPROGRESS.QUARTER as CAMPAIGNSUBPRIORITY_DATEQUARTER,
                    @STARTDATE as PARAMGOALSTARTDATE,
                    @ENDDATE as PARAMGOALENDDATE,
                    case PRIORITYPROGRESS.SEQUENCE
                        when 1 then 
                            case PRIORITYPROGRESS.TOTALQUARTERS
                                when 1 then 
                                    dbo.UFN_CAMPAIGNPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNPRIORITY.ID,@STARTDATE,@ENDDATE)
                                else
                                    dbo.UFN_CAMPAIGNPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNPRIORITY.ID,@STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                                end
                        when PRIORITYPROGRESS.TOTALQUARTERS then 
                            dbo.UFN_CAMPAIGNPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)),@ENDDATE)
                        else
                            dbo.UFN_CAMPAIGNPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                    end as CAMPAIGNPRIORITY_TOTALPLANNEDGIFTS,
                    case SUBPRIORITYPROGRESS.SEQUENCE
                        when 1 then
                            case SUBPRIORITYPROGRESS.TOTALQUARTERS
                                when 1 then 
                                    dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNSUBPRIORITY.ID, @STARTDATE, @ENDDATE)
                                else
                                    dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNSUBPRIORITY.ID,@STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                                end
                        when SUBPRIORITYPROGRESS.TOTALQUARTERS then
                            dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNSUBPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)),@ENDDATE)
                        else    
                            dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALPLANNEDGIFTS(CAMPAIGNSUBPRIORITY.ID,(DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))))
                    end as CAMPAIGNSUBPRIORITY_TOTALPLANNEDGIFTS

                from 
                    dbo.CAMPAIGNPRIORITY
                inner join dbo.CAMPAIGN on CAMPAIGN.ID = CAMPAIGNPRIORITY.CAMPAIGNID 
                left join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID 
                inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNPRIORITY.ID
                left join dbo.UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as SUBPRIORITYPROGRESS on SUBPRIORITYPROGRESS.CAMAPIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY.ID and (PRIORITYPROGRESS.YEARNAME = SUBPRIORITYPROGRESS.YEARNAME and PRIORITYPROGRESS.QUARTER = SUBPRIORITYPROGRESS.QUARTER)
                left join dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID = CAMPAIGNSUBPRIORITYNAMECODE.ID
                where
                    CAMPAIGNPRIORITY.CAMPAIGNID = @CAMPAIGNID
                    and ( (CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = @CAMPAIGNPRIORITYTYPECODEID) or (@CAMPAIGNPRIORITYTYPECODEID is null) )

                order by 
                    CAMPAIGNPRIORITY_DATEYEAR, CAMPAIGNPRIORITY_DATEQUARTER;