//Every month customer has its own status. It's stored in CustomerStatusMonth.
//Sometimes we are trying to find out the life time value of the customer grouping by status:
select
    ReportMonth = CustomerStatusMonth.DateID,
    CustomerStatus = Status.StatusName,
    CustomerNumber = count(distinct CustomerStatusMonth.CustomerID),
    RollingAmount = sum(InvoiceD.Amount),
    CustomerActiveMonth = count(distinct CustomerStatusMonth.CustomerID + eomonth(InvoiceH.DateID)),
    AVGLifeTime = CustomerActiveMonth / CustomerNumber,
    AVGAmount = RollingAmount / CustomerNumber
from CustomerStatusMonth

    inner join InvoiceH
    on InvoiceH.CustomerID = CustomerStatusMonth.CustomerID
    and InvoiceH.DateID <= CustomerStatusMonth.DateID

    inner join InvoiceD
    on InvoiceH.InvoiceID = InvoiceD.InvoiceID

    left join Status
    on Status.StatusID = CustomerStatusMonth.StatusID

where CustomerStatusMonth.DateID = 20171231