Использование поля даты для сопоставления SQL-запроса

Question

У меня возникла небольшая проблема, связанная с логикой этого меняющегося измерения. Я хотел бы связать эти две таблицы ниже. Мне нужно сопоставить таблицу фактов "Стоимость - период" с измерением затрат на основе идентификатора и даты вступления в силу.

Как видите, если поле месяца и года больше даты вступления в силу соответствующего измерения затрат, оно должно принять это значение. После того, как новая дата вступления в силу введена в измерение, она должна использовать это значение для любого периода, превышающего указанную дату в будущем.

РЕДАКТИРОВАТЬ: Я прошу прощения за отсутствие детализации, но измерение затрат будет на самом деле имеет уникальное значение индекса и изменяющиеся поля для ссылки для сопоставления будут Resource, Project, Cost. Я попытался сопоставить запрос, который вы указали с моими полями, но я получаю неправильный вывод.

К сведению: изменение соглашения об именах: EngagementId - это Id, Resource - это ConsultantId, а Project - ProjectId

Я изменил изображения ниже, и вот мой запрос

     ,_cte(HoursWorked, HoursBilled, Month, Year, EngagementId, ConsultantId, ConsultantName, ProjectId, ProjectName, ProjectRetainer, RoleId, Role, Rate, ConsultantRetainer, Salary,  amount, EffectiveDate)
     as
     (
     select sum(t.Duration), 0, Month(t.StartDate), Year(t.StartDate),   t.EngagementId, c.ConsultantId, c.ConsultantName, c.ProjectId, c.ProjectName, c.ProjectRetainer, c.RoleId, c.Role, c.Rate, c.ConsultantRetainer, 
c.Salary, 0, c.EffectiveDate
      from timesheet t
      left join Engagement c on t.EngagementId = c.EngagementId and Month(c.EffectiveDate) = Month(t.EndDate) and Year(c.EffectiveDate) = Year(t.EndDate)
      group by Month(t.StartDate), Year(t.StartDate), t.EngagementId, c.ConsultantName, c.ConsultantId, c.ProjectId, c.ProjectName, c.ProjectRetainer, c.RoleId, c.Role, c.Rate, c.ConsultantRetainer, 
c.Salary, c.EffectiveDate

    )
    select * from _cte where EffectiveDate is not null
    union
    select _cte.HoursWorked, _cte.HoursBilled, _cte.Month, _cte.Year,  _cte.EngagementId, _cte.ConsultantId, _cte.ConsultantName, _cte.ProjectId, _Cte.ProjectName, _cte.ProjectRetainer, _cte.RoleId, _cte.Role, sub.Rate, _cte.ConsultantRetainer,_cte.Salary, _cte.amount, sub.EffectiveDate 
        from _cte
        outer apply (
                select top 1 EffectiveDate, Rate
                from Engagement e
                where e.ConsultantId = _cte.ConsultantId and e.ProjectId = _cte.ProjectId and e.RoleId = _cte.RoleId
                and Month(e.EffectiveDate) < _cte.Month and Year(e.EffectiveDate) < _cte.Year
                order by EffectiveDate desc
            ) sub
    where _cte.EffectiveDate is null

Пример:

Я борюсь с написанием запроса, который согласуется с этим. Сначала я попытался разделить по величине дате. Однако, когда я выполнил объединение, я получил самую высокую дату вступления в силу для каждого периода (даже до даты вступления в силу).

Это то, что может быть выполнено в запросе, или я должен сосредоточиться на инкрементных обновлениях таблицы назначения, чтобы любой эффективный период даты / времени в прошлом оставался один?

Любые советы будут великолепны!

Спасибо ченнинг

Answer 1

Попробуй это:

; with _CTE as(
    select p.* , c.EffectiveDate, c.Cost
    from period p
      left join CostDimension c on p.id = c.id and p.Month = DATEPART(month, c.EffectiveDate) and p.year = DATEPART (year, EffectiveDate)
)
select * from _CTE Where EffectiveDate is not null

Union

select _CTE.id, _CTE.Month, _CTE.Year, sub.EffectiveDate, sub.Cost
from _CTE 
     outer apply (select top 1 EffectiveDate, Cost 
                  from CostDimension as cd
                  where cd.Id = _CTE.id and cd.EffectiveDate < DATETIMEFROMPARTS(_CTE.Year, _CTE.Month, 1, 0, 0, 0, 0) 
                  order by EffectiveDate desc
                  ) sub
where _Cte.EffectiveDate is null