Query to find out total patients for 15 days

Question

I have a question . I have a query which finds out the number of patients in that particular day

Select wtt1.wrt_ip_num, wtt1.wrt_from_ward_cd, wbm.wbm_room_num, wbm.wbm_bed_num, wtt1.wrt_in_dt, wtt1.wrt_out_dt
FROM W_TRANSFER_TXN wtt1, w_ward_master_base wmb, w_bed_master_base wbm
where wtt1.wrt_from_ward_cd=wmb.wwm_ward_cd and wmb.wwm_ward_locn='TMH' 
  and wbm.wbm_bed_num=wtt1.wrt_from_bed_num and wbm.wbm_room_num=wtt1.wrt_from_room_num and wbm.wbm_ward_cd=wtt1.wrt_from_ward_cd 
  and (wtt1.wrt_ip_num, wtt1.wrt_sl_num ) in 
    (Select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
    FROM W_TRANSFER_TXN wtt
    where /*wtt.wrt_ip_num='IP/20/034619' and*/ trunc(wtt.wrt_in_dt)<=TO_DATE('29-Sep-2020','DD-MON-YYYY') 
          and (wtt.wrt_out_dt is null or trunc(wtt.wrt_out_dt)>=TO_DATE('29-Sep-2020','DD-MON-YYYY'))
    group by wtt.wrt_ip_num);

Currently it takes out the ip number for the date 29th . It gives the no. of IP nums for that particular date. What I want to do is take this whole query in a loop where it finds out the query for the previous 15 days. Is that possible . Can the whole query be looped for the previous 15 days? Can we put in a loop where the date field is? I can take out the number of patients everyday by changing the date but can I find that out for 15 days?

Answer 1

As you fixed the wtt.wrt_in_dt to date '2020-09-29', query returns data for that particular date.

Consider changing the condition to

trunc(wtt.wrt_in_dt) >= trunc(sysdate) - 15

which will return rows whose wrt_in_dt is within the last 15 days.

---

How to generate dates?

SQL> select date '2020-10-20' + level - 1 datum
  2  from dual
  3  connect by level <= 5;

DATUM
----------
20.10.2020
21.10.2020
22.10.2020
23.10.2020
24.10.2020

SQL>

---

If you want to use dates in a FOR loop, you'll have to do a "conversion" to a Julian date (whose datatype is NUMBER) as FOR loop won't allow dates as boundaries. It also means that you'd have to convert that Julian date back to be used in query. I don't have any suitable table soDBMS_OUTPUT.PUT_LINE will have to do.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     l_datum         DATE := TRUNC (SYSDATE);
  3     l_datum_julian  NUMBER := TO_NUMBER (TO_CHAR (l_datum, 'j'));
  4  BEGIN
  5     FOR i IN l_datum_julian .. l_datum_julian + 5
  6     LOOP
  7        DBMS_OUTPUT.put_line (TO_CHAR (TO_DATE (i, 'j'), 'dd.mm.yyyy'));
  8     END LOOP;
  9  END;
 10  /
16.10.2020
17.10.2020
18.10.2020
19.10.2020
20.10.2020
21.10.2020

PL/SQL procedure successfully completed.

SQL>