Rows multiply when using Datediff

megasupermoon — Tue, 12 Sep 2023 18:16:31 GMT

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!

Re: Rows multiply when using Datediff

Or — Tue, 12 Sep 2023 19:29:26 GMT

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.

topic Re: Rows multiply when using Datediff in App Development

Rows multiply when using Datediff

Re: Rows multiply when using Datediff