I am super new to SQL so my apologies if I'm doing everything wrong, but is there any way I could modify this code
select e.*,
sum(case when (o.svcdate_form >= e.pre_date AND o.svcdate_form < e.svcdate_form AND (o.dx1 IN(e.dx1, e.dx2, e.dx3,e.dx4) OR o.dx2 IN(e.dx1, e.dx2, e.dx3,e.dx4)
OR o.dx3 IN(e.dx1, e.dx2, e.dx3,e.dx4))) then pay else 0 end) as pre_pay,
sum(case when (o.svcdate_form > e.svcdate_form AND o.svcdate_form <= e.post_date AND (o.dx1 IN(e.dx1, e.dx2, e.dx3,e.dx4) OR o.dx2 IN(e.dx1, e.dx2, e.dx3,e.dx4)
OR o.dx3 IN(e.dx1, e.dx2, e.dx3,e.dx4))) then pay else 0 end) as post_pay,
sum(case when (o.svcdate_form >= e.pre_date AND o.svcdate_form <= e.post_date AND (o.dx1 IN(e.dx1, e.dx2, e.dx3,e.dx4) OR o.dx2 IN(e.dx1, e.dx2, e.dx3,e.dx4)
OR o.dx3 IN(e.dx1, e.dx2, e.dx3,e.dx4))) then o.pay else 0 end) as total_pay
from eoc_dx_lookup_member_wide_new e
left join ccaeo190_ky o
on o.enrolid = e.enrolid
and (
e.dx1 in (o.dx1, o.dx2, o.dx3)
or e.dx2 in (o.dx1, o.dx2, o.dx3)
or e.dx3 in (o.dx1, o.dx2, o.dx3)
or e.dx4 in (o.dx1, o.dx2, o.dx3)
)
group by e.svcdate_form;
Or at least have a code that would give me a similar result to what I am trying to do? I have multiple conditions for my case statements and don't know if this is possible. Thanks in advance!
"eoc_dx_lookup_member_wide_new e" table looks like so:
enrolid | msclmid | svcdate_form | proc2 | pre_date | post_date | dx1 | dx2 | dx3 | dx4
------------------------------------------------------------------------------------------------------
1234 | 22 | 2019-03-18 | 20600 | 2019-03-08 | 2019-03-28 | M2021 | NULL | NULL | NULL
1234 | 23 | 2019-05-08 | 28291 | 2019-02-07 | 2019-08-06 | M2021 | M21611 | M25571 | NULL
1234 | 20610 | 2019-09-19 | 20680 | 2019-09-09 | 2019-09-29 | Z4789 | M21611 | M65871 | M1990
My "ccaeo190_ky o" table looks something like this:
enrolid | msclmid | svcdate_form | proc1 | dx1 | dx2 | dx3 | pay
--------------------------------------------------------------------------
1234 | 45 | 2019-03-10 | 20600 | M2021 | NULL | NULL | 45
1234 | 47 | 2019-03-26 | 74987 | NULL | NULL | M2021 | 62
1234 | 24 | 2019-06-20 | 34567 | M2021 | M21611 | M25571 | 123
1234 | 78 | 2019-05-08 | 27459 | NULL | NULL | M2021 | 62
1234 | 60 | 2019-09-19 | 97860 | M1990 | NULL | NULL | 54
what I'm hoping to achieve would be an output like this:
enrolid | msclmid | svcdate_form | proc2 | pre_date | post_date | dx1 | dx2 | dx3 | dx4 | pre_pay | post_pay | total_pay
--------------------------------------------------------------------------------------------------------------------------------------
1234 | 22 | 2019-03-18 | 20600 | 2019-03-08 | 2019-03-28 | M2021 | NULL | NULL | NULL | 45 | 62 | 107
1234 | 23 | 2019-05-08 | 28291 | 2019-02-07 | 2019-08-06 | M2021 | M21611 | M25571 | NULL | 0 | 123 | 185
1234 | 20610 | 2019-09-19 | 20680 | 2019-09-09 | 2019-09-29 | Z4789 | M21611 | M65871 | M1990 | 0 | 0 | 54
I want to sum the pay column in the o table only if they fall within the pre_date and post_date window and contain any of the dx codes for a specific row in my mapping table
How about that?
CREATE TABLE test.a (
enrolid INT
, msclmid INT
, svcdate_form DATE
, proc2 INT
, pre_date DATE
, post_date DATE
, dx1 VARCHAR(50)
, dx2 VARCHAR(50)
, dx3 VARCHAR(50)
, dx4 VARCHAR(50)
);
CREATE TABLE test.b (
enrolid INT
, msclmid INT
, svcdate_form DATE
, proc1 INT
, dx1 VARCHAR(50)
, dx2 VARCHAR(50)
, dx3 VARCHAR(50)
, pay INT
);
INSERT INTO test.a VALUES
(1234,22,str_to_date('2019-03-18','%Y-%m-%d'),20600,str_to_date('2019-03-08','%Y-%m-%d'),str_to_date('2019-03-28','%Y-%m-%d'),'M2021',NULL,NULL,NULL),
(1234,23,str_to_date('2019-05-08','%Y-%m-%d'),28291,str_to_date('2019-02-07','%Y-%m-%d'),str_to_date('2019-08-06','%Y-%m-%d'),'M2021','M21611','M25571',NULL),
(1234,20610,str_to_date('2019-09-19','%Y-%m-%d'),20680,str_to_date('2019-09-09','%Y-%m-%d'),str_to_date('2019-09-29','%Y-%m-%d'),'Z4789','M21611','M65871','M1990');
INSERT INTO test.b VALUES
(1234,45,str_to_date('2019-03-10','%Y-%m-%d'),20600,'M2021',NULL,NULL,45),
(1234,47,str_to_date('2019-03-26','%Y-%m-%d'),74987,NULL,NULL,'M2021',62),
(1234,24,str_to_date('2019-06-20','%Y-%m-%d'),34567,'M2021','M21611','M25571',123),
(1234,78,str_to_date('2019-05-08','%Y-%m-%d'),27459,NULL,NULL,'M2021',62),
(1234,60,str_to_date('2019-09-19','%Y-%m-%d'),97860,'M1990',NULL,NULL,54);
and the query:
SELECT e.enrolid
, e.msclmid
, e.svcdate_form
, e.proc2
, e.pre_date
, e.post_date
, e.dx1
, e.dx2
, e.dx3
, e.dx4
, sum(CASE
WHEN o.svcdate_form >= e.pre_date AND o.svcdate_form < e.svcdate_form THEN pay
ELSE 0
END) as pre_pay
, sum(CASE
WHEN o.svcdate_form > e.svcdate_form AND o.svcdate_form <= e.post_date THEN pay
ELSE 0
END) as post_pay
, sum(CASE
WHEN o.svcdate_form >= e.pre_date AND o.svcdate_form <= e.post_date THEN pay
ELSE 0
END) as total_pay
FROM test.a e
LEFT JOIN test.b o
ON e.enrolid = o.enrolid
WHERE 1=1
AND CASE
WHEN e.dx1 = o.dx1 OR e.dx1 = o.dx2 OR e.dx1 = o.dx3 THEN 1
WHEN e.dx2 = o.dx1 OR e.dx2 = o.dx2 OR e.dx2 = o.dx3 THEN 1
WHEN e.dx3 = o.dx1 OR e.dx3 = o.dx2 OR e.dx3 = o.dx3 THEN 1
WHEN e.dx4 = o.dx1 OR e.dx4 = o.dx2 OR e.dx4 = o.dx3 THEN 1
ELSE 0
END = 1
GROUP BY e.enrolid
, e.msclmid
, e.svcdate_form
, e.proc2
, e.pre_date
, e.post_date
, e.dx1
, e.dx2
, e.dx3
, e.dx4
;
tho, there is a little difference in one column/row .. I don't really get what exactly are your conditions.. anyway, this might be a good starting point for you
enrolid|msclmid|svcdate_form|proc2|pre_date |post_date |dx1 |dx2 |dx3 |dx4 |pre_pay|post_pay|total_pay|
-------|-------|------------|-----|----------|----------|-----|------|------|-----|-------|--------|---------|
1234| 22| 2019-03-18|20600|2019-03-08|2019-03-28|M2021| | | | 45| 62| 107|
1234| 23| 2019-05-08|28291|2019-02-07|2019-08-06|M2021|M21611|M25571| | 107| 123| 292|
1234| 20610| 2019-09-19|20680|2019-09-09|2019-09-29|Z4789|M21611|M65871|M1990| 0| 0| 54|