USP_DATALIST_STOCKDETAILANDSALESHIERARCHY

CREATE procedure dbo.USP_DATALIST_STOCKDETAILANDSALESHIERARCHY
(
    @REVENUEID uniqueidentifier
)
as
set nocount on

-- Select the base stock information

select
    STOCKDETAIL.ID,
    null as PARENTID,
    '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 REVENUEID = @REVENUEID

union all

-- Select the sales made for the stock

select
    STOCKSALE.ID,
    REVENUEPAYMENTMETHOD.ID as PARENTID,
    'Sold stock' as TYPE,
    STOCKSALE.SALEDATE as [DATE],
    STOCKSALE.TRANSACTIONSALEAMOUNT as [AMOUNT],
    STOCKSALE.TRANSACTIONSALEAMOUNT - (REVENUEPAYMENTMETHOD.AMOUNT/coalesce(nullif(STOCKDETAIL.NUMBEROFUNITS,0), 1)) * 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.REVENUE on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
inner join dbo.STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
where REVENUEID = @REVENUEID

order by LISTORDER, [DATE]