I have a some rows where it points to another row in same table based on RelativeId column value. I need to find the difference of these two records date values using MDX.
for ex. sample table structure with some values
ID - HasRelative - Date - RelativeId
1 - False - 2023-01-01 - NULL
2 - True - 2023-08-01 - 1
Here record 2 has a relative record 1. The output should be difference of Date values between 2 records which are mapped with RelativeId.
Thank you very much
Assuming the ID field is the primary key of the table, I come up with a solution like this:
create table #T
, HASRELATIVE BIT
, [DATE] DATE
, RELATIVEID INT
INSERT INTO #T (ID, HASRELATIVE, [DATE], RELATIVEID) VALUES
(1, 0, '2023-01-01', NULL)
, (2, 1, '2023-08-01', 1)
; WITH CTE AS
CASE WHEN HASRELATIVE = 1 THEN
DATEDIFF(DAY, (SELECT [DATE] FROM #T T2 WHERE T2.ID=T1.RELATIVEID), T1.[DATE])
) AS RELATIVE_DATEDIFF
FROM #T T1
SELECT * FROM CTE
DROP TABLE #T
But it seems too simple to me. Did I really understand what you need?
Hi gdl, thanks for the reply.
I need it using SSAS MDX query.
Sorry satya, I missed SSAS MDX.
I can't help with that.