Ggtcf

I am trying to add a 0, 1, or null to a column in a specific category where a relativepersonid of a person has a diagdate up to a person's servicedate. Here are my tables:

 DROP TABLE ICDCodes_w;
 GO
 CREATE TABLE ICDCodes_w
(
 AnxietyDisorder VARCHAR(6),
 DepressiveDisorder VARCHAR(6),
 PTSD VARCHAR(6)
);

INSERT INTO ICDCodes_w
(
 AnxietyDisorder,
 DepressiveDisorder,
 PTSD
)
VALUES
('293.84', '296.2', '309.81'),
('300', '296.21', 'F43.1'),
('305.42', 'F11.28', 'F31.76'),
('305.81', 'F43.8', 'F31.78'),
('F40.00', 'F43.10', '305.52');
GO
DROP TABLE DiagHX_w;
GO
CREATE TABLE DiagHX_w
(
 ArchiveID VARCHAR(10),
 RelativePersonID VARCHAR(10),
 ICDCode VARCHAR(6), 
 DiagDate DATE 
);

INSERT INTO DiagHX_w
(
 ArchiveID,
 RelativePersonID,
 ICDCode, 
 DiagDate
)
VALUES
('1275741', '754241', '293.84', '1989-01-03'),
('2154872', '754241', '293.84', '1995-04-07'),
('4587215', '754241', '998.4', '1999-12-07'),
('4588775', '711121', 'F11.28', '2001-02-07'),
('3545455', '711121', NULL, NULL),
('9876352', '323668', '400.02', '1988-04-09'),
('3211514', '112101', 'F31.78', '2005-09-09'),
('3254548', '686967', 'F40.00', '1999-12-31'),
('4411144', '686967', '305.52', '2000-01-01'),
('6548785', '99999999','F40.00', '2000-02-03');
GO
DROP TABLE PatientFlags_w;
GO
CREATE TABLE PatientFlags_w
(
 PersonID VARCHAR(10),
 RelativePersonID VARCHAR(10),
 AnxietyDisorder VARCHAR(2),
 DepressiveDisorder VARCHAR(2),
 PTSD VARCHAR(2),
);

INSERT INTO PatientFlags_w
(
 PersonID,
 RelativePersonID
)
VALUES
('99999999', '754241'),
('88888888', '754241'),
('77777777', '754241'),
('66666666', '711121'),
('55555555', '711121'),
('44444444', '323668'),
('33333333', '112101'),
('22222222', '686967'),
('11111111', '686967'),
('32151111', '887878'),
('78746954', '771125'),
('54621333', '333114'),
('55648888', '333114');
GO
DROP TABLE Person_w;
GO
CREATE TABLE Person_w
(
 PersonID VARCHAR(10),
 ServiceDate date
);

INSERT INTO Person_w
(
 PersonID,
 ServiceDate
)
VALUES
('99999999', '2000-12-31'),
('88888888', '2000-11-01'),
('69876541', '2000-09-04'),
('66666666', '2000-01-15'),
('55555555', '2000-07-22'),
('44444444', '2000-07-20'),
('65498711', '2000-11-17'),
('22222222', '2000-09-02'),
('11111111', '2000-02-04'),
('32151111', '2000-02-17'),
('78746954', '2000-03-29'),
('54621333', '2000-08-22'),
('55648888', '2000-10-20');

Here is my update statement:

UPDATE a
SET AnxietyDisorder = CASE
 WHEN ICDCode IN
 (
 SELECT AnxietyDisorder FROM 
 Project..ICDCodes_w
 ) THEN
 1
 ELSE
 0
 END,
DepressiveDisorder = CASE
 WHEN ICDCode IN
 (
 SELECT DepressiveDisorder FROM 
 Project..ICDCodes_w
 ) THEN
 1
 ELSE
 0
 END,
PTSD = CASE
 WHEN ICDCode IN
 (
 SELECT PTSD FROM Project..ICDCodes_w
 ) THEN
 1
 ELSE
 0
 END
FROM PatientFlags_w a
JOIN DiagHX_w b
 ON a.relativepersonid = b.RelativePersonID
JOIN Person_w p 
ON a.personid = p.PersonID
WHERE diagdate <= p.servicedate; 

This works on some values, but there are some that don't get updated. I know the issue is with my case statement and probably a join issue. What is a better way to write this? Here is an example query I used to check. The PTSD column should have a 1.

SELECT * FROM project..patientflags_w a 
JOIN project..diaghx_w b 
ON a.relativepersonid = b.RelativePersonID
JOIN project..person_w p 
ON a.personid = p.personid 
WHERE b.icdcode IN (SELECT PTSD FROM Project..ICDCodes_w) 
AND b.diagdate <= p.servicedate 

I did ask this question the other day, but my sample tables were all messed up, so I've verified that they work this time.

At first glance, the problem with your query is that you update the target (PatientFlags_w) multiple times: once for each flag. In some cases you seem to be ending up with the correct result, but its just by luck.

It's hard to tell if you want one row per person in the flag table, or one row per flag.

Can you review these queries and let us know if they are close to your desired results:

-- If you want one row per Person:
select RelativePersonID, 
 [AnxietyDisorder] = max(case when c.AnxietyDisorder is not null then 1 else 0 end),
 [DepressiveDisorder] = max(case when c.DepressiveDisorder is not null then 1 else 0 end),
 [PTSD] = max(case when c.PTSD is not null then 1 else 0 end)
from DiagHX_w d
left 
join ICDCodes_w c on d.ICDCode in (c.AnxietyDisorder, c.DepressiveDisorder, c.PTSD)
group
by RelativePersonID;


-- If you want one row per Flag:
select RelativePersonID, 
 d.ICDCode,
 [AnxietyDisorder] = case when c.AnxietyDisorder is not null then 1 else 0 end,
 [DepressiveDisorder] = case when c.DepressiveDisorder is not null then 1 else 0 end,
 [PTSD] = case when c.PTSD is not null then 1 else 0 end
from DiagHX_w d
left 
join ICDCodes_w c on d.ICDCode in (c.AnxietyDisorder, c.DepressiveDisorder, c.PTSD);

If the diagnoses are not related to each other (I assumed since they are in the same table), you might want this instead:

select RelativePersonID, 
 [AnxietyDisorder] = max(case when c.AnxietyDisorder = d.ICDCode then 1 else 0 end),
 [DepressiveDisorder] = max(case when c.DepressiveDisorder = d.ICDCode then 1 else 0 end),
 [PTSD] = max(case when c.PTSD = d.ICDCode then 1 else 0 end)
from DiagHX_w d
left 
join ICDCodes_w c on d.ICDCode in (c.AnxietyDisorder, c.DepressiveDisorder, c.PTSD)
group
by RelativePersonID;