USP_DATALIST_STOCKDETAILANDSALES

                    CREATE procedure dbo.USP_DATALIST_STOCKDETAILANDSALES
                    (
                        @REVENUEPAYMENTMETHODID uniqueidentifier
                    )
                    as
                        set nocount on

                        -- Select the base stock information

                        select
                            STOCKDETAIL.ID,
                            'Stock' as TYPE,
                            REVENUE.DATE as [DATE],
                            REVENUE.TRANSACTIONAMOUNT as AMOUNT,
                            null as GAINLOSS,
                            null as FEE,
                            null as NETPROCEEDS,
                            STOCKDETAIL.NUMBEROFUNITS,
                            STOCKDETAIL.TRANSACTIONLOWPRICE as LOWPRICE,
                            STOCKDETAIL.TRANSACTIONMEDIANPRICE as MEDIANPRICE,
                            STOCKDETAIL.TRANSACTIONHIGHPRICE as HIGHPRICE,
                            null as SALEPOSTSTATUSCODE,
                            null as STOCKDETAILID,
                            0 as LISTORDER,
                            REVENUE.TRANSACTIONCURRENCYID
                        from dbo.REVENUEPAYMENTMETHOD
                        inner join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
                        inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        where REVENUEPAYMENTMETHOD.ID = @REVENUEPAYMENTMETHODID

                        union all

                        -- Select the sales made for the stock

                        select
                            STOCKSALE.ID,
                            'Sold stock' as TYPE,
                            STOCKSALE.SALEDATE as [DATE],
                            STOCKSALE.TRANSACTIONSALEAMOUNT as [AMOUNT],
                            STOCKSALE.TRANSACTIONSALEAMOUNT - (STOCKDETAIL.TRANSACTIONMEDIANPRICE * STOCKSALE.NUMBEROFUNITS) as GAINLOSS,
                            STOCKSALE.TRANSACTIONFEE,
                            STOCKSALE.TRANSACTIONSALEAMOUNT - STOCKSALE.TRANSACTIONFEE as NETPROCEEDS,
                            STOCKSALE.NUMBEROFUNITS,
                            STOCKSALE.TRANSACTIONLOWPRICE,
                            STOCKSALE.TRANSACTIONMEDIANPRICE,
                            STOCKSALE.TRANSACTIONHIGHPRICE,
                            STOCKSALE.SALEPOSTSTATUSCODE,
                            STOCKSALE.STOCKDETAILID,
                            1 as LISTORDER,
                            STOCKSALE.TRANSACTIONCURRENCYID
                        from dbo.REVENUEPAYMENTMETHOD
                        inner join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
                        inner join dbo.STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
                        where REVENUEPAYMENTMETHOD.ID = @REVENUEPAYMENTMETHODID

                        order by LISTORDER, [DATE]