Имам маса с различни обекти и обектите се развиват с времето. Един обект се идентифицира с номер_на_обект и можем да го проследим с номер_на_обект. И всяка еволюция на обекта има статус.
Искам да изчисля времето, изминало между някои състояния.
По-долу е моята таблица за един object_number "us1":
В жълто са редовете, съдържащи началната дата. Те се намират, ако (status_id = 0 и (old_status ‹> 0 или object_line_number = 1) и аварийно_ниво = 1).
В зелено са редовете, съдържащи крайната дата. Те се намират, ако (status_id =2,3,4,5 и old_status = 0).
Колоната old_status не съществува в таблицата. Това е състоянието на предишния ред (според обекта) номер_на_ред). Възстановявам го благодарение на следната мярка:
old_status = CALCULATE (
MAX(fact_object[status_id]),
FILTER (
ALL(fact_object),
fact_object[object_line_number] = IF(fact_object[object_line_number]=1, fact_object[object_line_number], MAX (fact_object[object_line_number])-1)),
VALUES (fact_object[object_number]))
Аз съм в режим DirectQuery, така че много функции не присъстват за изчисляеми колони, затова използвам мерки.
След като това стане, искам след това да мога да получа за всеки зелен ред date_modification на предишния жълт ред.
В този пример резултатът ще бъде 4/4 след това 1. Така че мога да изчисля разликата във времето между date_modification на текущия зелен ред и date_modification на предишния жълт ред.
Така че мислех да добавя нова колона с име date_received, която е date_modification на предишния жълт ред;
Оттам нататък просто трябва да запазя само зелените редове и да изчисля разликата между date_modification и date_received.
Последното ми изчисление всъщност е да имам това:
Резултат = (брой зелени редове, чиято разлика в датата между date_modification и date_received ‹= 15 минути) / (брой зелени редове, които DAY(date_modification) = DAY(date_received))
Но не знам как да го направя. Опитах се в същия дух на мярката old_status да направя това:
date_received = CALCULATE (
MAX(fact_object[date_modification]),
FILTER (
ALL(fact_object),
(fact_object[object_line_number] = MAX (fact_object[object_line_number])-1) && MY OTHER FILTERS
),
VALUES (fact_object[object_number])
)
Но не успя.
В SQL еквивалентът би бил така:
SELECT
SUM(CASE WHEN (DATEDIFF(MINUTE, T.date_received, T.date_planification) <= 15) THEN 1 ELSE 0 END) /
SUM(CASE WHEN (DAY(T.date_received) = DAY(T.date_planification)) THEN 1 ELSE 0 END) as result
FROM (
SELECT *, T.status_id as current_status,
LAG(T.date_modification) OVER(PARTITION BY T.object_number ORDER BY T.object_line_number) as date_received,
T.date_modification as date_planification
FROM
(
select *,
LAG (status_id) OVER(PARTITION BY object_number ORDER BY object_line_number) AS old_status
from dbo.fact_object
) AS T
WHERE ((T.status_id = 0 AND (T.old_status <> 0 OR T.object_line_number = 1) AND T.emergency_level = 1) OR (T.old_status = 0 AND T.status_id IN (2,3,4,5)))--974
) AS T
WHERE old_status = 0
(Е, може би има по-добър начин да го направя в SQL, който направих).
Как мога да постигна това?