Rows multiply when using Datediff

[megasupermoon]

Hello,

I’m making a report that calculates treatment days in different departments.

My current SQL query is this (except that I limited the departments to one to simplify my post):

SELECT
CONCAT(TP.OWNER, '#', TP.PART) AS UniqueID,
TP.STARTED AS PERIOD_STARTED,
CASE WHEN TP.ENDED IS NULL THEN GETDATE()
ELSE TP.ENDED END AS PERIOD_ENDED,
D.NAME AS DEPARTMENT_NAME,
TP.AREA_OF_SERVICE,
TPV.STARTED AS VACATION_STARTED,
CASE WHEN TPV.STARTED IS NOT NULL AND TPV.ENDED IS NULL THEN GETDATE()
ELSE TPV.ENDED END AS VACATION_ENDED,
DATEDIFF("DAY", TP.STARTED, ISNULL(TP.ENDED, CURRENT_TIMESTAMP)) + (
CASE
WHEN TP.DELETED = 'N' AND TP.STARTED = TP.ENDED THEN 1
ELSE 0
END
) AS PERIOD_DURATION,
DATEDIFF
(
DAY,
TPV.STARTED,
TPV.ENDED
) AS VACATION_DAYS

FROM TREATMENTPERIODS TP

LEFT JOIN TREATMENTPERIODVACATIONS TPV
ON TP.OWNER = TPV.OWNER
AND TP.PART = TPV.PART
AND (
TPV.STARTED < TP.ENDED
or TP.ENDED is null
) AND (
TP.STARTED < TPV.ENDED
or TPV.ENDED is null
) AND TPV.DELETED = 'N'

INNER JOIN PATIENTS P
on TP.OWNER = P.OWNER

JOIN DEPARTMENTS D
ON D.OWNER = TP.DEPARTMENT

WHERE (
TP.ENDED >= '2023-01-01'
OR TP.ENDED IS NULL
)
AND TP.DELETED IN ('N', 'M')
AND TP.STARTED <= GETDATE()
AND TP.STARTED > '2022-01-01'
AND P.TEST_PATIENT = 'N'
AND D.NAME = 'DEP16';

And the table looks like this:

Treatment Periods Table 

My problem is that because, for example, UniqueID 14568#1000020 has 4 different vacations within the same treatment period 2023/03/14 – 2023/06/11 (99 days), query gives 4 rows for UniqueID 14568#1000020 (1 per vacation date range) so period duration multiplies by 4.

Is there a way to make this so that every other row than the first for distinct UniqueID gives 0 to PERIOD_DURATION? Or is there any other way to do something similar so that PERIOD_DURATION would come only once per UniqueID? Or something similar to that?

Thanks in advance!

[Or]

Seems like Period Duration should be a measure, not a dimension that's pre-calculated at the SQL level? Likewise you would want to sum Vacation_Days as a measure, and other fields may need to be either removed or converted to a measure.