-
SQL Coding- adding a new variable to an existing query
I have an existing query that works really well- it takes a set of claims and creates a minimum start and max end date for a given member with a given provider, along with a sum of paid- it strings along for hospital stays where there are interim bills. I need to add in adjudication date- I need the min adjudication date for each stay. I tried adding it in and it isn't working. Can anyone help me? Both queries are below. thank you!
1st query- works- no adjudication date
Create Table IPClaimsGrouped2 as
(SELECT Member_ID , MedID
, Start_Date ,End_Date,
(SELECT sum(r2.paid)
FROM IPClaims2 AS r2
WHERE r2.Member_ID = r1.Member_ID
AND r2.MedID = r1.MedID
AND r2.Start_Date >= r1.Start_Date
AND r2.End_Date <= r1.End_Date
) AS sum_paid
FROM (SELECT Member_ID , MedID
, Start_Date
, (SELECT MIN(t3.End_Date)
FROM IPClaims2 AS t3
WHERE NOT EXISTS
(SELECT *
FROM IPClaims2 AS t4
WHERE t4.Member_ID = t3.Member_ID
AND t4.MedID = t3.MedID
AND t4.Start_Date = t3.End_Date + interval 1 day
)
AND t3.Member_ID = t1.Member_ID
AND t3.MedID = t1.MedID
AND t3.End_Date >= t1.Start_Date
) as end_date
FROM IPClaims2 AS t1
WHERE NOT EXISTS
(SELECT *
FROM IPClaims2 AS t2
WHERE t2.Member_ID = t1.Member_ID
AND t2.MedID = t1.MedId
AND t2.End_Date = t1.Start_Date - interval 1 day
)
) AS r1
ORDER BY
Member_ID , MedID
, Start_Date);
2nd Query- adding in adjudication date- broken
Create Table IPClaimsGrouped2 as
(SELECT Member_ID , MedID
, Start_Date ,End_Date, min(Adj_Date) as min_adj_date,
(SELECT sum(r2.paid)
FROM IPClaims2 AS r2
WHERE r2.Member_ID = r1.Member_ID
AND r2.MedID = r1.MedID
AND r2.Start_Date >= r1.Start_Date
AND r2.End_Date <= r1.End_Date
) AS sum_paid
FROM (SELECT Member_ID , MedID
, Start_Date, min(Adj_Date) as Adj_Date
, (SELECT MIN(t3.End_Date)
FROM IPClaims2 AS t3
WHERE NOT EXISTS
(SELECT *
FROM IPClaims2 AS t4
WHERE t4.Member_ID = t3.Member_ID
AND t4.MedID = t3.MedID
AND t4.Start_Date = t3.End_Date + interval 1 day
)
AND t3.Member_ID = t1.Member_ID
AND t3.MedID = t1.MedID
AND t3.End_Date >= t1.Start_Date
) as end_date
FROM IPClaims2 AS t1
WHERE NOT EXISTS
(SELECT *
FROM IPClaims2 AS t2
WHERE t2.Member_ID = t1.Member_ID
AND t2.MedID = t1.MedId
AND t2.End_Date = t1.Start_Date - interval 1 day
)
) AS r1
ORDER BY
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|