---------------------------------------------------------------------------------------------------------------------------------
/* Dates */
---------------------------------------------------------------------------------------------------------------------------------
--SELECT * FROM ANALYTICS.DIM_DATES_SOURCE LIMIT 100
WITH DATE_T AS
(
SELECT
FULL_DATE,
YEAR_MONTH
--WEEK_START::DATE,
--END_OF_WEEK
FROM (
SELECT
FULL_DATE,
YEAR_MONTH
--CASE WHEN DAY_OF_WEEK = 7 THEN FULL_DATE - INTERVAL '6 days' END AS WEEK_START,
--CASE WHEN DAY_OF_WEEK = 7 OR IS_MONTH_START THEN FULL_DATE END AS END_OF_WEEK
FROM ANALYTICS.DIM_DATES_SOURCE
WHERE FULL_DATE = CURRENT_DATE - 1
order by full_date desc
) AS d
ORDER BY FULL_DATE DESC
)
---------------------------------------------------------------------------------------------------------------------------------
/* Auctions */
---------------------------------------------------------------------------------------------------------------------------------
/*
SELECT * FROM AUCTION_LEDGERS order by date desc LIMIT 100
SELECT * FROM AUCTIONS LIMIT 100
*/
, AUCTNS AS
(
WITH AUC AS
(
SELECT DISTINCT F.ID AS FACILITY_ID
, F.NAME AS FACILITY_NAME
, AL.RENTAL_ID
, A.TIME::DATE AS AUCTION_DATE
FROM AUCTION_LEDGERS AL
JOIN AUCTIONS A
ON AL.AUCTION_ID = A.ID
JOIN FACILITIES F
ON A.FACILITY_ID = F.ID
WHERE AL.DATE = CURRENT_DATE - 1
AND F.LIVE
AND F.NAME NOT LIKE 'ZZZKO Storage%'
AND F.NAME NOT LIKE 'KO Storage - Training DB%'
AND F.NAME NOT LIKE 'zKO Storage of%'
AND F.TIMEZONE <> ''
)
, UM AS
(
SELECT DISTINCT R.ID AS RENTAL_ID
, R.TENANT_ID
, UM.CURRENT_RATE AS RENT_ROLL
FROM RENTALS R
JOIN UNIT_METRICS UM
ON R.UNIT_ID = UM.UNIT_ID
WHERE UM.TIME::DATE = CURRENT_DATE - 1
)
SELECT AUC.FACILITY_ID
, AUC.FACILITY_NAME
, AUC.AUCTION_DATE
, COUNT(DISTINCT UM.RENTAL_ID) AS AUCTION_UNITS
, COUNT(DISTINCT UM.TENANT_ID) AS AUCTION_TENANTS
, SUM(UM.RENT_ROLL) AS AUCTION_RENT_ROLL
FROM AUC
JOIN UM
ON AUC.RENTAL_ID = UM.RENTAL_ID
GROUP BY AUC.FACILITY_ID
, AUC.FACILITY_NAME
, AUC.AUCTION_DATE
)
---------------------------------------------------------------------------------------------------------------------------------
/* Consolidated Occupancy */
---------------------------------------------------------------------------------------------------------------------------------
, CO AS
(
SELECT F.ID AS FACILITY_ID
, F.NAME AS FACILITY_NAME
, F.ACQUIRED_AT::DATE
, F.SOLD_ON::DATE
, (UM.TIME::DATE AT TIME ZONE F.TIMEZONE)::DATE AS UM_DATE
, CASE
WHEN F.ACUMATICA_BRANCH = 'KOF1' THEN 'Fund I'
WHEN F.ACUMATICA_BRANCH = 'KOF2' THEN 'Fund II'
WHEN F.ACUMATICA_BRANCH = 'KOF3' THEN 'Fund III'
WHEN F.ACUMATICA_BRANCH LIKE 'FS%' THEN '3PM'
WHEN F.ACUMATICA_BRANCH LIKE 'LC%' THEN '3PM'
WHEN F.ACUMATICA_BRANCH LIKE 'PS%' THEN '3PM'
ELSE 'SPE'
END AS FUND
, CASE WHEN LIVE THEN F.ASSET_MANAGER[1] ELSE NULL END AS ASSET_MANAGER
, SUM(UM.CURRENT_RATE) AS RENT_ROLL
, COUNT(UM.ID) AS TOTAL_UNITS
, SUM(UM.RENTED) AS OCCUPIED_UNITS
FROM UNIT_METRICS UM
LEFT JOIN UNITS U
ON U.ID = UM.UNIT_ID
LEFT JOIN FACILITIES F
ON F.ID = U.FACILITY_ID
LEFT JOIN ENTITIES E
ON F.ENTITY = E.NAME
RIGHT JOIN ANALYTICS.DIM_DATES_SOURCE DD
ON DD.FULL_DATE = (UM.TIME::DATE AT TIME ZONE F.TIMEZONE)::DATE
WHERE DD.FULL_DATE = CURRENT_DATE - 1
AND F.LIVE
AND F.NAME NOT LIKE 'ZZZKO Storage%'
AND F.NAME NOT LIKE 'KO Storage - Training DB%'
AND F.TIMEZONE <> ''
GROUP BY F.ID
, F.NAME
, UM.TIME::DATE AT TIME ZONE F.TIMEZONE
, CASE
WHEN F.ACUMATICA_BRANCH = 'KOF1' THEN 'Fund I'
WHEN F.ACUMATICA_BRANCH = 'KOF2' THEN 'Fund II'
WHEN F.ACUMATICA_BRANCH = 'KOF3' THEN 'Fund III'
WHEN F.ACUMATICA_BRANCH LIKE 'FS%' THEN '3PM'
WHEN F.ACUMATICA_BRANCH LIKE 'LC%' THEN '3PM'
WHEN F.ACUMATICA_BRANCH LIKE 'PS%' THEN '3PM'
ELSE 'SPE'
END
, CASE WHEN LIVE THEN F.ASSET_MANAGER[1] ELSE NULL END
)
---------------------------------------------------------------------------------------------------------------------------------
/* Scheduled Move Outs */
---------------------------------------------------------------------------------------------------------------------------------
--SELECT * FROM RENTALS LIMIT 100
, SCHED_MO AS
(
SELECT SMO.FACILITY_ID
, SMO.FACILITY_NAME
--, SMO.SCHEDULED_MOVE_OUT_DATE
, DATE_TRUNC('MONTH',DATES.FULL_DATE) AS SMO_MONTH
, COUNT(SMO.*) AS SMO_UNIT_COUNT
, SUM(SMO.SMO_CURRENT_RATE) AS SMO_RENT_ROLL
FROM (
SELECT DISTINCT F.ID AS FACILITY_ID
, F.NAME AS FACILITY_NAME
, R.UNIT_ID
, UM.DESIRED_MOVE_OUT::DATE AS SCHEDULED_MOVE_OUT_DATE
, SUM(UM.CURRENT_RATE) AS SMO_CURRENT_RATE
FROM UNIT_METRICS UM
JOIN RENTALS R
ON UM.UNIT_ID = R.UNIT_ID
JOIN UNITS U
ON U.ID = UM.UNIT_ID
JOIN FACILITIES F
ON F.ID = U.FACILITY_ID
WHERE DATE_TRUNC('MONTH', DESIRED_MOVE_OUT::DATE) = '2026-09-01'
AND R.ACTIVE
AND UM.DESIRED_MOVE_OUT IS NOT NULL
AND UM.TIME::DATE = CURRENT_DATE - 1
AND R.START_TIME::DATE <> '0001-12-31'
AND F.LIVE IS TRUE
AND F.NAME NOT LIKE 'ZZZKO Storage%'
AND F.NAME NOT LIKE 'KO Storage - Training DB%'
AND F.NAME NOT LIKE 'zKO Storage of%'
AND F.TIMEZONE <> ''
GROUP BY F.ID
, F.NAME
, R.UNIT_ID
, UM.DESIRED_MOVE_OUT::DATE
) SMO
JOIN ANALYTICS.DIM_DATES_SOURCE DATES
ON SMO.SCHEDULED_MOVE_OUT_DATE::DATE = DATES.FULL_DATE
AND DATE_TRUNC('MONTH',DATES.FULL_DATE) = DATE_TRUNC('MONTH',CURRENT_DATE)::DATE
--WHERE DATE_TRUNC('MONTH',SMO.SCHEDULED_MOVE_OUT_DATE)::DATE = DATE_TRUNC('MONTH',CURRENT_DATE)::DATE
GROUP BY SMO.FACILITY_ID
, SMO.FACILITY_NAME
--, SMO.SCHEDULED_MOVE_OUT_DATE
, DATE_TRUNC('MONTH',DATES.FULL_DATE)
)
---------------------------------------------------------------------------------------------------------------------------------
/* Final */
---------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT DATE_T.YEAR_MONTH
, DATE_T.FULL_DATE
--, DATE_T.WEEK_START::DATE
--, DATE_T.END_OF_WEEK
, CO.FACILITY_ID
, CO.FACILITY_NAME
, CO.ASSET_MANAGER
, CO.FUND
, CO.ACQUIRED_AT::DATE AS FACILITY_ACQUIRED_AT
, CO.SOLD_ON::DATE AS FACILITY_SOLD_ON
, AUCTNS.AUCTION_DATE
, SUM(COALESCE(CO.RENT_ROLL,0)) AS RENT_ROLL
, SUM(COALESCE(CO.OCCUPIED_UNITS,0)) AS OCCUPIED_UNITS
, SUM(COALESCE(CO.TOTAL_UNITS,0)) AS TOTAL_UNITS
, SUM(COALESCE(AUCTNS.AUCTION_UNITS,0)) AS AUCTION_UNITS
, SUM(COALESCE(AUCTNS.AUCTION_RENT_ROLL,0)) AS AUCTION_RENT_ROLL
, SUM(COALESCE(SCHED_MO.SMO_UNIT_COUNT,0)) AS SMO_UNIT_COUNT
, SUM(COALESCE(SCHED_MO.SMO_RENT_ROLL,0)) AS SMO_RENT_ROLL
FROM DATE_T
JOIN CO
ON DATE_T.FULL_DATE = CO.UM_DATE::DATE
--AND DATE_T.DAY_OF_WEEK = 7
LEFT JOIN AUCTNS
ON CO.FACILITY_ID = AUCTNS.FACILITY_ID
--AND CO.UM_DATE::DATE = AUCTNS.AL_DATE::DATE
LEFT JOIN SCHED_MO
--ON DATE_TRUNC('MONTH',DATE_T.FULL_DATE) = SCHED_MO.SMO_MONTH
ON CO.FACILITY_ID = SCHED_MO.FACILITY_ID
GROUP BY DATE_T.YEAR_MONTH
, DATE_T.FULL_DATE
--, DATE_T.WEEK_START::DATE
--, DATE_T.END_OF_WEEK
, CO.FACILITY_ID
, CO.FACILITY_NAME
, CO.ASSET_MANAGER
, CO.FUND
, CO.ACQUIRED_AT::DATE
, CO.SOLD_ON::DATE
, AUCTNS.AUCTION_DATE
--)