--NOTE: Parameters @dStart, @dEnd and @mySites are automatically declared and set.
--DECLARE @dStart as datetime
--DECLARE @dEnd as datetime
--DECLARE @mySites as int
--SET @dStart = '01/01/2008'
--SET @dEnd = '12/31/2008'
--SET @mySites = 13

SET @dStart = dbo.fnMonthStrt(@dStart)
SET @dEnd = dbo.fnDayEnd(@dEnd)

--Show Tenants that moved-out between @dStart and @dEnd.
SELECT COUNT(DISTINCT Ledgers.LedgerID) AS NumberOfMoveOuts, SUM(Payments.dcPmtAmt) AS LifeTimeRevenue,
       SUM(Payments.dcPmtAmt) / COUNT(DISTINCT Ledgers.LedgerID) AS AverageLifeTimeValueOfCustomer
FROM Payments 
INNER JOIN Ledgers ON Payments.LedgerID = Ledgers.LedgerID
INNER JOIN Units ON Units.UnitID = Ledgers.UnitID 
INNER JOIN Access ON Access.LedgerID = Ledgers.LedgerID 
INNER JOIN Tenants ON Tenants.TenantID = Access.TenantID
WHERE (Ledgers.dDeleted Is NULL) 
    AND (Access.bPrimary = 1) 
    AND (Ledgers.dMovedOut >= @dStart)
    AND (Ledgers.dMovedOut <= @dEnd)
    AND (iTferToLedID IS Null)
    AND (sUnitName <> N'POS$')
    AND (Ledgers.SiteID IN (@mySites))   --SiteLink will fill in your SiteID(s)


SELECT CONVERT(nvarchar(10), Units.dcWidth) + 'x' + CONVERT(nvarchar(10), Units.dcLength) AS UnitSize,
       COUNT(DISTINCT Ledgers.LedgerID) AS NumberOfMoveOuts, SUM(Payments.dcPmtAmt) AS LifeTimeRevenue,
       SUM(Payments.dcPmtAmt) / COUNT(DISTINCT Ledgers.LedgerID) AS AverageLifeTimeValueOfCustomer
FROM Payments 
INNER JOIN Ledgers ON Payments.LedgerID = Ledgers.LedgerID
INNER JOIN Units ON Units.UnitID = Ledgers.UnitID 
INNER JOIN Access ON Access.LedgerID = Ledgers.LedgerID 
INNER JOIN Tenants ON Tenants.TenantID = Access.TenantID 
WHERE (Ledgers.dDeleted Is NULL) 
    AND (Access.bPrimary = 1) 
    AND (Ledgers.dMovedOut >= @dStart)
    AND (Ledgers.dMovedOut <= @dEnd)
    AND (iTferToLedID IS Null)
    AND (sUnitName <> N'POS$')
    AND (Ledgers.SiteID IN (@mySites))   --SiteLink will fill in your SiteID(s)
GROUP BY Units.dcWidth, Units.dcLength
ORDER BY UnitSize