В настоящее время у нас есть хранилище данных (Azure SQL Server), к которому я подключаюсь. Проблема: мне нужно иметь месячный подсчет свободных владений, разделенных по регионам, в которых они расположены. Недвижимость можно разделить на многоквартирную (более одной двери) или на одну семью (одна дверь). Если недвижимость пустует в течение одного месяца и продолжает оставаться вакантной в следующем месяце, нам необходимо ее посчитать. Существует вероятность того, что запись останется бездействующей, поэтому у меня не будет строки в БД, которую я могу связать с более поздним месяцем.
Контекст (упрощенный):
tblPWDimBuilding
tblPWDimUnit
RowEffectiveDate — поскольку у нас есть исторические данные в тусклых таблицах, этот столбец указывает, что запись активна с этой конкретной даты.
RowExpirationDate — это снова для исторических данных. В этом столбце указано, что срок действия записи истек в эту конкретную дату, т. е. неактивная запись, для активных записей значение равно нулю.
RowIsActive - 1 означает активную запись, тогда как 0 означает неактивную запись (исторические данные).
Ожидаемые результаты из таблицы выше:
(обратите внимание, что статус собственности в многоквартирном доме игнорируется, и вместо этого рассматривается единица)
Где в апреле у нас есть результаты для дома на одну семью с идентификатором 1235 и для многоквартирного дома, где одна дверь свободна с идентификатором здания 1238 и идентификатором единицы 2124. В мае у нас нет любые новые записи в таблице DIM для зданий или квартир, так что 2 двери с апреля все еще свободны в мае и должны быть учтены вместе с новым свободным зданием с идентификатором 1237. Этот процесс необходимо будет использовать при просмотре замыкающего списка. 6-12 месяцев, так что мы не можем просто смотреть на период последнего месяца. Я не уверен, как подражать этому водопаду, как процесс подсчета. Моя неудачная попытка ниже:
SELECT
DATEPART(YEAR, RowEffectiveDate) AS Year,
DATEPART(MONTH, RowEffectiveDate) As Month,
OrganizationName,
COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM curated.tblPWDimBuilding b
WHERE b.Status = 'Vacant'
AND b.Active = 'True'
GROUP BY
DATEPART(YEAR, RowEffectiveDate),
DATEPART(MONTH, RowEffectiveDate),
OrganizationName
UNION
SELECT
DATEPART(YEAR, u.RowEffectiveDate) AS Year,
DATEPART(MONTH, u.RowEffectiveDate) As Month,
u.OrganizationName,
COUNT(DISTINCT u.UnitID) AS [Vacant Count]
FROM curated.tblPWDimBuilding b
INNER JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
AND u.Status = 'Vacant'
WHERE b.Active = 'True'
GROUP BY
DATEPART(YEAR, u.RowEffectiveDate),
DATEPART(MONTH, u.RowEffectiveDate),
u.OrganizationName;
В приведенном выше коде у меня есть союз между перспективой для одной семьи и перспективой для нескольких семей на уровне единицы. Затем я группирую по году и месяцу, когда запись была включена в базу данных, и группирую по организации или региону, в котором находится недвижимость. Флаг Active=True — это флаг, специфичный для исходной системы, чтобы убедиться, что она не была деактивирована.
Вышеприведенное близко, но при использовании даты вступления в силу строки не учитывается случай, когда свободная недвижимость должна быть отражена в следующем месяце и простаивает (где запись в прошлом месяце будет помечена как активная) группировка больше не будет работать корректно. Я застрял! Как это можно решить? Где я мог ошибиться?
По вашему запросу были добавлены простые дополнения @PaulMaxwell. Спасибо за ответ!
Для записей, которые остаются бездействующими и которые необходимо подсчитать в следующем месяце, используйте таблицу измерения даты или создайте список месяцев в CTE, а затем соедините его с текущим запросом. Под CTE MonthList находится необходимый список начала месяца, созданный из столбца RowEffectiveDate в обеих таблицах. Затем для каждого месяца запрос проверяет, активна ли запись в течение этого месяца, если RowEffectiveDate меньше или равно началу месяца, а RowExpirationDate больше начала месяца или NULL, что будет включать незанятые записи в следующем месяце. счет месяца:
WITH MonthList
AS (
SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, RowEffectiveDate), 0) AS MonthStart
FROM (
SELECT RowEffectiveDate FROM curated.tblPWDimBuilding
UNION
SELECT RowEffectiveDate FROM curated.tblPWDimUnits
) AS Dates
)
SELECT DATEPART(YEAR, m.MonthStart) AS Year, DATEPART(MONTH, m.MonthStart) AS Month, OrganizationName, COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM MonthList m
INNER JOIN curated.tblPWDimBuilding b ON b.STATUS = 'Vacant'
AND b.Active = 'True'
AND b.RowEffectiveDate <= m.MonthStart
AND (
b.RowExpirationDate > m.MonthStart
OR b.RowExpirationDate IS NULL
)
GROUP BY DATEPART(YEAR, m.MonthStart), DATEPART(MONTH, m.MonthStart), OrganizationName
UNION /* should this be UNION ALL ?? */
SELECT DATEPART(YEAR, m.MonthStart) AS Year, DATEPART(MONTH, m.MonthStart) AS Month, u.OrganizationName, COUNT(DISTINCT u.UnitID) AS [Vacant Count]
FROM MonthList m
LEFT JOIN curated.tblPWDimBuilding b ON b.Active = 'True'
AND b.RowEffectiveDate <= m.MonthStart
AND (
b.RowExpirationDate > m.MonthStart
OR b.RowExpirationDate IS NULL
)
INNER JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
AND u.STATUS = 'Vacant'
AND u.RowEffectiveDate <= m.MonthStart
AND (
u.RowExpirationDate > m.MonthStart
OR u.RowExpirationDate IS NULL
)
GROUP BY DATEPART(YEAR, m.MonthStart), DATEPART(MONTH, m.MonthStart), u.OrganizationName;
обратите внимание, что без образцов данных или ожидаемого результата невозможно проверить этот запрос, но я думаю, что важная часть его заключается в том, чтобы: создать и использовать таблицу измерения даты или продолжить создание списка месяцев - затем присоединиться к вашему данные к этому, так как это обеспечивает необходимую логику «заглянуть вперед».
Я также не уверен, следует ли вам использовать UNION или UNION ALL - возможно, UNION неверен, поскольку он может подавлять строки, о которых следует сообщать, - но опять же, без данных трудно судить.
Спасибо за ваш вклад! Умное решение. В качестве вопроса, когда у вас есть внутреннее соединение, где RowEffectiveDate <= m.monthStart, не будут ли пропущены значения из текущего отчетного периода, если, скажем, они были вставлены и вступили в силу через пару дней после начала месяца? Возможно, было бы лучше сделать b.RowEffectiveDate <= DateAdd(MONTH,1, m.MonthStart), чтобы гарантировать включение строк в текущий отчетный период? Спасибо!
Очень вероятно, что мое предложение нуждается в доработке, поэтому наличие реалистичных данных имеет значение. Теперь, когда это доступно, я скоро вернусь, чтобы рассмотреть это снова.
насколько я понял: dbfiddle.uk/0Vx39QYa Недопустимое имя столбца «Активно». и Недопустимое имя столбца «RowExpirationDate».
Кажется, что вам нужно отслеживать свободную недвижимость месяц за месяцем, учитывая как односемейные, так и многоквартирные свойства. Чтобы решить проблему свободных свойств, которые остаются свободными в следующем месяце, но могут не иметь соответствующей строки в базе данных, вы можете изменить свой запрос, включив подзапрос, который идентифицирует свойства, которые были свободными в предыдущем месяце.
Вот пример того, как вы можете изменить свой запрос, чтобы добиться этого:
WITH PreviousMonthVacant AS (
SELECT
DATEADD(MONTH, -1, RowEffectiveDate) AS PreviousMonth,
CASE
WHEN Status = 'Vacant' THEN BuildingId
WHEN u.Status = 'Vacant' THEN u.BuildingId
ELSE NULL
END AS VacantBuildingId,
CASE
WHEN Status = 'Vacant' THEN NULL
WHEN u.Status = 'Vacant' THEN u.UnitId
ELSE NULL
END AS VacantUnitId
FROM curated.tblPWDimBuilding b
LEFT JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
WHERE
b.Active = 'True'
OR u.Active = 'True'
)
SELECT
DATEPART(YEAR, RowEffectiveDate) AS Year,
DATEPART(MONTH, RowEffectiveDate) AS Month,
OrganizationName,
COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM curated.tblPWDimBuilding b
LEFT JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
WHERE
b.Active = 'True'
OR u.Active = 'True'
OR b.BuildingId IN (
SELECT VacantBuildingId
FROM PreviousMonthVacant
WHERE VacantBuildingId IS NOT NULL
AND DATEPART(YEAR, PreviousMonth) = DATEPART(YEAR, b.RowEffectiveDate)
AND DATEPART(MONTH, PreviousMonth) = DATEPART(MONTH, b.RowEffectiveDate)
)
GROUP BY
DATEPART(YEAR, RowEffectiveDate),
DATEPART(MONTH, RowEffectiveDate),
OrganizationName
Я использовал общее табличное выражение (CTE) для определения свободных свойств в предыдущем месяце. CTE включает дату предыдущего месяца, идентификатор свободного здания и идентификатор свободной единицы.
Левое соединение выполняется для таблиц Building и Unit, чтобы включить в результат свободные свойства. При этом добавляется условие для включения свободных свойств за предыдущий месяц путем проверки наличия идентификатора здания в результатах подзапроса.
Включив эту логику, вы должны иметь возможность фиксировать свободные свойства месяц за месяцем, даже если они простаивали в предыдущем месяце и не имеют соответствующей строки в базе данных.
Спасибо за ваш ответ! Основная проблема с этим запросом заключается в том, что он не обрабатывает случаи, когда недвижимость свободна в течение x месяцев. Если это было 2, запрос выше, похоже, не обрабатывает этот случай.
Если бы вы могли предоставить некоторые образцы данных и ожидаемый результат (из этих данных), у вас гораздо больше шансов быстро получить работоспособное решение. Необходимо некоторое объяснение, но слова действительно не могут адекватно заменить данные. О, данные не означают результат неработающего запроса — это небольшие образцы данных для каждой таблицы.