I have a logic to calculate number of patients and med dosage per period, however my redhsift recursive SQL is not returning full scope of data:
WITH recursive build (period_start_date, peq, prev_peq, repeated_patient, cur_rn, max_rn) AS
(
SELECT
period_start_date,
ROUND(peq, 1) peq,
ROUND(CAST(prev_peq AS FLOAT), 1) prev_peq,
ROUND(CAST(repeated_patient AS FLOAT), 1) repeated_patient,
cur_rn,
max_rn
FROM
(SELECT
period_start_date,
peq,
CAST(prev_peq AS FLOAT),
CAST(repeated_patient AS FLOAT),
ROW_NUMBER() OVER (ORDER BY period_start_date) AS rn,
2::INT AS cur_rn ,
COUNT(1) OVER () AS max_rn
FROM
(SELECT
period_start_date,
ROUND(1.0 * su_value / 6, 1)::float peq,
LAG(ROUND(1.0 * su_value / 6, 1), 1) OVER (ORDER BY period_start_date)::float prev_peq,
CAST(0 AS FLOAT) AS repeated_patient
FROM testoss
)
WHERE peq != prev_peq
)
WHERE rn = 1
UNION ALL
SELECT b.period_start_date,
round(b.peq, 1),
round(b.prev_peq, 1),
round(cast(
CASE
WHEN b.prev_peq - nvl(t.repeated_patient,0) > b.peq THEN b.peq
ELSE b.prev_peq - nvl(t.repeated_patient,0)
END AS FLOAT), 1) AS repeated_patient,
b.cur_rn + 1 AS cur_rn,
b.max_rn
FROM build b
join
(
SELECT period_start_date,
peq,
prev_peq,
repeated_patient,
lag(period_start_date) over(ORDER BY period_start_date) prev_period,
row_number() over (ORDER BY period_start_date) AS rn
FROM (
SELECT period_start_date,
round(1.0*su_value /6, 1)::float peq,
lag(round(1.0*su_value/6,1 ),1) over (ORDER BY period_start_date )::float prev_peq,
cast(0 AS FLOAT) AS repeated_patient
FROM testoss )
) t
ON t.prev_period = b.period_start_date
WHERE t.rn = b.cur_rn
AND b.cur_rn <= b.max_rn
)
SELECT period_start_date,
peq,
prev_peq,
repeated_patient
FROM build;
it returns only one row of one period
instead of full scope of periods
This is the input test data:
CREATE TABLE TESTOSS
( PERIOD_START_DATE date,
SU_VALUE integer
);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-SEP-17','DD-MON-YY'),69);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-OCT-17','DD-MON-YY'),263);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-NOV-17','DD-MON-YY'),684);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-DEC-17','DD-MON-YY'),938);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JAN-18','DD-MON-YY'),1352);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-FEB-18','DD-MON-YY'),1174);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAR-18','DD-MON-YY'),1123);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-APR-18','DD-MON-YY'),1649);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAY-18','DD-MON-YY'),1402);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUN-18','DD-MON-YY'),1548);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUL-18','DD-MON-YY'),1448);
commit;
Could you please help me to correct this query?
I have a logic to calculate number of patients and med dosage per period, however my redhsift recursive SQL is not returning full scope of data:
WITH recursive build (period_start_date, peq, prev_peq, repeated_patient, cur_rn, max_rn) AS
(
SELECT
period_start_date,
ROUND(peq, 1) peq,
ROUND(CAST(prev_peq AS FLOAT), 1) prev_peq,
ROUND(CAST(repeated_patient AS FLOAT), 1) repeated_patient,
cur_rn,
max_rn
FROM
(SELECT
period_start_date,
peq,
CAST(prev_peq AS FLOAT),
CAST(repeated_patient AS FLOAT),
ROW_NUMBER() OVER (ORDER BY period_start_date) AS rn,
2::INT AS cur_rn ,
COUNT(1) OVER () AS max_rn
FROM
(SELECT
period_start_date,
ROUND(1.0 * su_value / 6, 1)::float peq,
LAG(ROUND(1.0 * su_value / 6, 1), 1) OVER (ORDER BY period_start_date)::float prev_peq,
CAST(0 AS FLOAT) AS repeated_patient
FROM testoss
)
WHERE peq != prev_peq
)
WHERE rn = 1
UNION ALL
SELECT b.period_start_date,
round(b.peq, 1),
round(b.prev_peq, 1),
round(cast(
CASE
WHEN b.prev_peq - nvl(t.repeated_patient,0) > b.peq THEN b.peq
ELSE b.prev_peq - nvl(t.repeated_patient,0)
END AS FLOAT), 1) AS repeated_patient,
b.cur_rn + 1 AS cur_rn,
b.max_rn
FROM build b
join
(
SELECT period_start_date,
peq,
prev_peq,
repeated_patient,
lag(period_start_date) over(ORDER BY period_start_date) prev_period,
row_number() over (ORDER BY period_start_date) AS rn
FROM (
SELECT period_start_date,
round(1.0*su_value /6, 1)::float peq,
lag(round(1.0*su_value/6,1 ),1) over (ORDER BY period_start_date )::float prev_peq,
cast(0 AS FLOAT) AS repeated_patient
FROM testoss )
) t
ON t.prev_period = b.period_start_date
WHERE t.rn = b.cur_rn
AND b.cur_rn <= b.max_rn
)
SELECT period_start_date,
peq,
prev_peq,
repeated_patient
FROM build;
it returns only one row of one period
instead of full scope of periods
This is the input test data:
CREATE TABLE TESTOSS
( PERIOD_START_DATE date,
SU_VALUE integer
);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-SEP-17','DD-MON-YY'),69);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-OCT-17','DD-MON-YY'),263);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-NOV-17','DD-MON-YY'),684);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-DEC-17','DD-MON-YY'),938);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JAN-18','DD-MON-YY'),1352);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-FEB-18','DD-MON-YY'),1174);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAR-18','DD-MON-YY'),1123);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-APR-18','DD-MON-YY'),1649);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAY-18','DD-MON-YY'),1402);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUN-18','DD-MON-YY'),1548);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUL-18','DD-MON-YY'),1448);
commit;
Could you please help me to correct this query?
Share Improve this question edited Nov 27, 2024 at 21:35 marc_s 756k184 gold badges1.4k silver badges1.5k bronze badges asked Nov 27, 2024 at 21:29 KondjitsuKondjitsu 211 gold badge1 silver badge6 bronze badges 2- The output you have shown is it the whole output? – samhita Commented Nov 27, 2024 at 22:00
- What is the expected output? – samhita Commented Nov 27, 2024 at 22:17
1 Answer
Reset to default 0Your code fetches just one row because you put the where clause condition to WHERE rn = 1 (Line: 29).
There are some other issues with the code too, and there is no explanation about the logic for the column repeated_patient.
-- S a m p l e D a t a :
CREATE TABLE TESTOSS ( PERIOD_START_DATE date, SU_VALUE integer );
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-SEP-17','DD-MON-YY'),69);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-OCT-17','DD-MON-YY'),263);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-NOV-17','DD-MON-YY'),684);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-DEC-17','DD-MON-YY'),938);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JAN-18','DD-MON-YY'),1352);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-FEB-18','DD-MON-YY'),1174);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAR-18','DD-MON-YY'),1123);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-APR-18','DD-MON-YY'),1649);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAY-18','DD-MON-YY'),1402);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUN-18','DD-MON-YY'),1548);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUL-18','DD-MON-YY'),1448);
... the way I understood your question, one of the options is to create a cte with basic calculated data and later self join the cte by previous month/date and use case expressions to fetch what you want.
WITH
grid AS
( Select period_start_date,
Row_Number() Over(Partition By period_start_date) as rn_month,
Count(1) Over(Partition By period_start_date) as cnt_month,
su_value,
Round(1.00 * su_value / 6, 2) as peq,
period_start_date - INTERVAL '1' Month as prev_peq_date
From TESTOSS
)
-- M a i n S Q L :
SELECT period_start_date, peq, prev_peq,
Case When prev_peq > Coalesce(g2_next_peq, 0.00)
Then prev_peq - Coalesce(g2_next_peq, 0.00)
Else 0.00
End as repeated_patient -- this column is most likely wrong and you should adjust it ...
FROM ( Select g.period_start_date, g.peq,
Case When g.rn_month = g.cnt_month
Then Round(Coalesce(g2.peq, 0.0), 1)
Else 0.0
End as prev_peq,
LAG(g2.peq) Over(Order By g.period_start_date, g.rn_month Desc) as g2_next_peq
From grid g
Left Join grid g2 ON( g2.period_start_date = g.prev_peq_date And
g2.rn_month = 1 And -- joining first row of prev month
g.rn_month = g.cnt_month -- with last row of actual month (in case that is needed as your expected result shows multiple rows per month)
)
Order By g.period_start_date, g.rn_month
)
R e s u l t :
period_start_date | peq | prev_peq | repeated_patient |
---|---|---|---|
2017-09-01 | 11.50 | 0.0 | 0.00 |
2017-10-01 | 43.83 | 11.5 | 11.50 |
2017-11-01 | 114.00 | 43.8 | 32.30 |
2017-12-01 | 156.33 | 114.0 | 70.17 |
2018-01-01 | 225.33 | 156.3 | 42.30 |
2018-02-01 | 195.67 | 225.3 | 68.97 |
2018-03-01 | 187.17 | 195.7 | 0.00 |
2018-04-01 | 274.83 | 187.2 | 0.00 |
2018-05-01 | 233.67 | 274.8 | 87.63 |
2018-06-01 | 258.00 | 233.7 | 0.00 |
2018-07-01 | 241.33 | 258.0 | 24.33 |
fiddle