Hello All,
I need to convert this sql to DAX , can anyone please help me in this, let me know if anoyone requires sample data and
:fromDate and :toDate can more than 24hrs gap.
SELECT ROUND(SUM(EXTRACT (EPOCH FROM OFT - ON)) / 60) diff,
ROUND(sum(idle_diff) / 60) idle_time_min
FROM (SELECT eq_no, ON, OFT, SUM(EXTRACT (EPOCH FROM AET - AST)) idle_diff
FROM (SELECT g.equip eq_no, GREATEST (g.eg_on, :fromDate) ON, LEAST (g.OFT, :toDate) OFT,
i.start_time start_time, i.end_time end_time,
CASE WHEN i.start_time IS NOT NULL THEN GREATEST (i.start_time, g.eg_on, :fromDate) ELSE NULL END AST,
CASE WHEN i.start_time IS NOT NULL THEN LEAST(COALESCE(i.end_time, NOW()), COALESCE(g.OFT, NOW()), :toDate) ELSE NULL END AET
FROM ignition g
LEFT OUTER JOIN
(SELECT *
FROM movement m
WHERE m.tml_id = :tmlId
AND m.eq_type = :eqType
AND m.start_time <= :toDate
AND (m.end_time >= :fromDate OR m.end_time IS NULL)
) i
ON ( g.tml_id = i.tml_id AND g.eq_type = i.eq_type AND g.equip = i.bi_em_eq_no
AND i.start_time <= COALESCE (g.OFT, NOW())
AND (i.end_time >= g.eg_on OR i.end_time IS NULL))
WHERE g.tml_id = :tmlId
AND g.eq_type = :eqType
AND g.eg_on <= :toDate
AND (g.OFT >= :fromDate OR g.OFT IS NULL)
) m
GROUP BY eq_no, ON, OFT
) m;
