Count how many patients there are in the departments on filtered date

[megasupermoon]

Hey,

I have a report in the works, where the user researches how many patients have recordings with a certain type of procedure.

However, the report should also show (after filtering by department) how many customers have been in the department at 9 o'clock on the requested date, so that a single day is selected with the date picker, after which another table shows the entries recorded on the selected date and another how many patients have been in the selected department on that same day at 9 o'clock. The SQL query I'm using already retrieves only those patients who have been in different departments at 9 o'clock.

The tablets look like this:

The one on the right (not all columns are visible in the screenshot) shows the recordings with registry dates by customer. The number of recordings is counted in the first column.

The one on the left should count the number of customers in the department at 9 o’clock on the requested registry day. For example, the user selects 2023-11-15 from the date picker and from the filter pane he selects department D1 and the rightmost table gives one entry with the posting date 2023-11-15. The table on the left should give, let’s say for example, 3 as the number of customers in the department:

1) customer 1B, in the department between 2023-11-14 10:00.00 -- 2023-11-16 07:15.00

2) customer 3A, in the department between 2023-11-15 08:00.00 -- 2023-11-16 06:00.00

3) customer 4C, in department between 2023-11-15 08:00.00 -- 2023-11-15 16:00.00

Other customers of the Department have been in the department at 9 o'clock outside the selected date.

It is enough for the table on the left to show only the department and number of customers in it.

My problem is that I don't know how to get the table on the left to count how many customers there have been in the department on the selected registry date, so that the table on the right only shows and counts the recordings entered on the requested date. Currently, it counts how many customers the department has had in general.

So, in short, the user wants to know how many people in the department have recordings.

My sql queries look like this:

MAIN_TABLE:
Select 
CW.DEPARTMENT AS DEPARTMENT_ID
CW.OWNER AS PAT_ID,
DATE(CWR.REGISTRY_DATE) AS REGISTRY_DATE,
CP.START AS CAREPERIOD_START,
CP.END AS CAREPERIOD_END,
CP.PART AS CAREPERIOD_PART,
DE.NAME AS DEPARTMENT_NAME,
CASE WHEN CWR.PHASE = 15 THEN 'Execution' ELSE CWR.PHASE END AS PHASE,
CASE WHEN CWR.COMPONENT = 1540 THEN 'Component 1' ELSE CWR.COMPONENT END AS COMPONENT,
E.NAME + ' ' + O.SPECIFIER AS RECORDER,
CWR.TO_SUMMARY,
CASE WHEN CWR.CLASS = 1541 THEN 'Monitoring and treatment' ELSE CWR.CLASS END AS FUNCTION,
CW.FORM AS CAREWORK_FORM,
CW.CAREPERIOD AS CAREPERIOD,
CWR.TEXT AS RECORDING
FROM CAREWORK_RECORDING CWR
JOIN CAREWORK CW
	ON CW.OWNER = CWR.OWNER
	AND CW.FORM = CWR.FORM
     	AND CW.PART = CWR.PART
JOIN DEPARTMENTS DE
	ON DE.OWNER = CW.DEPARTMENT
JOIN EMPLOYEES E
	ON E.OWNER = CWR.RECORDER
LEFT JOIN CAREPERIODS CP
	ON CP.OWNER = CW.OMISTAJA
	AND CP.PART = CW.CAREPERIOD
WHERE CWR.COMPONENT = 1540
	AND CWR.CLASS = 1541
	AND CWR.REGISTRY_DATE >= '2023-01-01'
	AND CWR.REGISTRY_DATE <= curdate()
	AND CWR.PHASE NOT IN ('14', '16')
	AND CWR.FORM = 1000125
	AND CWR.DELETED = 'E';

DEPARTM_LINK:
SELECT DISTINCT
DL.OWNER AS PATIENT_ID,
DL.START,
DL.END AS END_DATE,
DE.NAME AS DEPARTMENT

FROM DIM_DATE DA

JOIN DEPARTMENT_LINK DL
	ON DL.DELETED = 'N'
	AND DL.START <= DATEADD(HOUR, 9, CONVERT(DATETIME, (DA.FULLDATE)))
	AND ISNULL(DL.END, '2100-01-01') >= DATEADD(HOUR, 9, CONVERT(DATETIME, (DA.FULLDATE)))

JOIN DWD_YKSIKKO DE
	ON DL.DEPARTMENT = DE.OWNER
    
WHERE 
DL.START >= '2023-01-01'
AND DL.START < GETDATE();

And load script (don't know if unnecessary):

DEP_MAP:
LOAD 
PATIENT_ID AS PAT_ID,
DATE(START) AS START,
MIN(DATE(END_DATE)) AS END_DATE

Resident DEPARTM_LINK
GROUP BY PATIENT_ID, START, END_DATE
;

Drop field END_DATE from DEPARTM_LINK;

Must I modify the queries of make a new one (and what kind of)? Or can it be done in the front-end (and how)?

My model viewer:

DEP_MAP is from load script created when I tried to make this report work correctly.

DEPARTMLINK has PATIENT_ID, the department where she/he has been, the date and time of entering the department and the date and time of leaving the department.

MAIN_TABLE contains information about the entries

Clarifying questions are welcome if they need to be asked. Thanks in advance for your help!

[ricky_roman]

So I need help to understand what is in the main table. Is this historical information of patients' location by department?

[megasupermoon]

Like I described at the end of the post, MAIN_TABLE contains information about the entries in the registry. Table on on the right has almost exactly the same columns as variables of MAIN_TABLE's SQL query.

Truncated example table:

Department is where patient was when entry was recorded and REGISTRY_DATE is the date when entry was recorded.

Example of the final result :

There is much more information than in the example table, but let's agree that the user selects 15.11.2023 from the date picker and 4DK as the Filter department. Let's also agree that both patients in the example table have been in the department on the selected day (15.11.2023) at 9 o'clock. Table of the left should show that department D4K had two patients and table on the right that one recording was written 15.11.2023.