USP_REPORT_AUCTIONCOMPARISON

            CREATE procedure dbo.USP_REPORT_AUCTIONCOMPARISON
            (
                @AUCTIONID1 as uniqueidentifier = null,
                @AUCTIONID2 as uniqueidentifier = null,
                @IDSETREGISTERID as uniqueidentifier = null,
                @USESELECTION as bit = null,
                @CURRENCYCODE smallint = null
            )
            as
            begin
                declare @AUCTIONFILTER table(ID uniqueidentifier primary key);

                if @USESELECTION = 0
                    begin
                        insert into @AUCTIONFILTER(ID) values(@AUCTIONID1)
                        insert into @AUCTIONFILTER(ID) values(@AUCTIONID2)
                    end
                else
                    insert into @AUCTIONFILTER(ID)
                    (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID))

                declare @AUCTIONRESULTS table
                (
                    AUCTIONID uniqueidentifier primary key,
                    AUCTIONNAME nvarchar(255),
                    TOTALITEMSFORBID int,
                    TOTALITEMSSOLD int,
                    TOTALFMV money,
                    TOTALPURCHASE money,
                    PROFITLOSS money,
                    ISOCURRENCYCODE nvarchar(3),
                    CURRENCYSYMBOL nvarchar(5),
                    SYMBOLDISPLAYSETTINGCODE tinyint,
                    DECIMALDIGITS tinyint,
                    SOLDFMV money
                )

                insert into @AUCTIONRESULTS(AUCTIONID, AUCTIONNAME, TOTALFMV, TOTALPURCHASE, ISOCURRENCYCODE, CURRENCYSYMBOL, SYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
                (
                    select
                        EVENT.ID as AUCTIONID,
                        EVENT.NAME as AUCTIONNAME,
                        case @CURRENCYCODE 
                            when 2 then coalesce(sum(AUCTIONITEM.TRANSACTIONVALUE),0) 
                            else coalesce(sum(AUCTIONITEM.ORGANIZATIONVALUE),0)
                        end as TOTALFMV,
                        case @CURRENCYCODE 
                            when 2 then coalesce(sum([PURCHASESPLIT].TRANSACTIONAMOUNT),0) 
                            else coalesce(sum([PURCHASESPLIT].ORGANIZATIONAMOUNT),0)
                        end as TOTALPURCHASE,
                        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                        CURRENCYPROPERTIES.CURRENCYSYMBOL,
                        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                        CURRENCYPROPERTIES.DECIMALDIGITS
                    from
                        dbo.EVENT
                        left join dbo.AUCTIONITEM on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
                        left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                        left join dbo.REVENUESPLIT [PURCHASESPLIT] on AUCTIONITEMPURCHASE.PURCHASEID = [PURCHASESPLIT].ID
                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 2 then AUCTIONITEM.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) CURRENCYPROPERTIES
                    where
                        exists(select 1 from @AUCTIONFILTER where ID = EVENT.ID)
                    group by
                        EVENT.ID, EVENT.NAME, CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS
                )

                update @AUCTIONRESULTS
                set TOTALITEMSFORBID = 
                        (
                            select count(AUCTIONITEM.ID)
                            from dbo.AUCTIONITEM
                            where AUCTIONITEM.EVENTAUCTIONID = AUCTIONID and AUCTIONITEM.PACKAGEID is null
                        )

                update @AUCTIONRESULTS
                set 
                    TOTALITEMSSOLD = 
                        (    --JamesWill WI162777 2011-06-17 Don't count items in a package since packages are counted as a single unit, no matter how many items are in them 

                            select count(AUCTIONITEM.ID)
                            from dbo.AUCTIONITEM 
                            inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                            where AUCTIONITEM.EVENTAUCTIONID = AUCTIONID 
                                and AUCTIONITEMPURCHASE.PURCHASEID is not null
                                and AUCTIONITEM.PACKAGEID is null
                        ) + 
                        (    --JamesWill WI162777 2011-06-17 Count individual packages that were sold by looking for items in packages that were sold. Fortunately,

                            --we do not support partially selling packages, so this works. 

                            select count(distinct AUCTIONITEM.PACKAGEID) 
                            from dbo.AUCTIONITEM 
                            inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                            where AUCTIONITEM.EVENTAUCTIONID = AUCTIONID 
                                and AUCTIONITEMPURCHASE.PURCHASEID is not null
                                and AUCTIONITEM.PACKAGEID is not null
                        ),
                    SOLDFMV = 
                        (
                            --JamesWill WI162777 2011-06-17 The FMV of a package is the sum of the FMVs of its items, so don't need to count packages separately here

                            select sum(    case @CURRENCYCODE when 2 then coalesce(AUCTIONITEM.TRANSACTIONVALUE,0)  
                                        else coalesce(AUCTIONITEM.ORGANIZATIONVALUE,0) end 
                                      )
                            from dbo.AUCTIONITEM 
                            inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                            where AUCTIONITEM.EVENTAUCTIONID = AUCTIONID 
                                and AUCTIONITEMPURCHASE.PURCHASEID is not null        
                        ) 

                update @AUCTIONRESULTS
                set PROFITLOSS = coalesce(TOTALPURCHASE - SOLDFMV,0) --JamesWill WI162777 2011-06-17 Don't consider items not sold as part of the loss


                select 
                    AUCTIONID,
                    AUCTIONNAME,
                    TOTALITEMSFORBID,
                    TOTALITEMSSOLD,
                    TOTALFMV,
                    TOTALPURCHASE,
                    PROFITLOSS,
                    ISOCURRENCYCODE,
                    CURRENCYSYMBOL,
                    SYMBOLDISPLAYSETTINGCODE,
                    DECIMALDIGITS 
                from @AUCTIONRESULTS order by AUCTIONNAME

            end