How to count 2nd record in query

[kawi6rr]

I searched the forums and noticed a few that might help but I’m sorry my SQL isn’t strong enough to solve this. I’m having a hard time trying to get this query to pull exactly what I need. Keep in mind SQL is not a strong point for me so I apologize if I’m not making sense. I’m looking for people who had an original surgery and then had a subsequent surgery within 10 years or the first. How do I go about counting just the second surgery?
Here’s the original request I was sent.
Criteria
1)date range January 1, 2004 through December 31, 2013
2) age 18 to 99 ( must be 18 years old or greater at first surgery)
3)Diagnosis codes for initial surgery and type (i,e 17.11 laparoscopic)

Inguinal hernia with gangrene 550.00, 550.01, 550.02, 550.03
Inguinal hernia, with obstruction, without mention of gangrene 550.10 , 550.11, 550.12, 550.13
Inguinal hernia, without mention of obstruction or gangrene 550.90, 550.91, 550.92, 550.93
Diagnosis codes for femoral hernias 553.00,553.01,553.02,553.03,551.00,551.01,551.02, 551.03, 552.00, 552.01, 552.02, 552.03

4)17.11,17.12, 17.13 17.21, 17.22, 17.23, 17.24,
53.00, 53.01, 53.02,53.03, 53.04, 53.05, 53.10, 53.11,53.12, 53.13,53.14, 53.15,53.16,53.17

For second surgery need to see this code with the above codes
for repair looking for these codes
53.29, 53,21, 53,39, 53.31

I’ve gotten this far.
SELECT
PAT.PAT_MRN_ID AS MRNR ,
PAT.PAT_NAME,
( CAST(CAST((CURRENT_DATE (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT) - CAST(CAST((CAST(BIRTH_DATE AS DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT) )/10000 AS AGE,
HSP.HSP_ACCOUNT_ID,
HSP.INP_ADM_DATE AS HOSP_ADMIT_DATE,
HSP.HOSP_DISCH_TIME ,
PXL.PROC_DATE,
PXI.REF_BILL_CODE AS ICD9_PROC,
PXI.PROCEDURE_NAME,
EDG.CURRENT_ICD9_LIST,
EDG.DX_NAME
FROM PAT_ENC_HSP HSP
INNER JOIN HSP_ACCT_PX_LIST PXL ON (HSP.HSP_ACCOUNT_ID = PXL.HSP_ACCOUNT_ID)
INNER JOIN CL_ICD_PX PXI ON (PXL.FINAL_ICD_PX_ID = PXI.ICD_PX_ID) AND PXI.CODE_SET_C = 1 -- ICD9
INNER JOIN HSP_ACCT_DX_LIST DXLIST ON (HSP.HSP_ACCOUNT_ID = DXLIST.HSP_ACCOUNT_ID)
INNER JOIN CLARITY_EDG EDG ON (DXLIST.DX_ID = EDG.DX_ID)
INNER JOIN PATIENT PAT ON (HSP.PAT_ID = PAT.PAT_ID)
WHERE
HSP.ADT_PAT_CLASS_C IN ('1204','12113', '1203') -- INPATIENT, ADMIT TO INPATIENT FROM SURGERY, HOSPITAL AMBULATORY SURGERY
AND HSP.ADMIT_CONF_STAT_C NOT IN (2,3) -- EXCLUDE PENDING, CANCELED ADMISSIONS
AND PXL.PROC_DATE BETWEEN CAST('2004-01-01' AS DATE) AND CAST('2013-12-31' AS DATE)
AND ( CAST(CAST((CURRENT_DATE (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT) -
CAST(CAST((CAST(BIRTH_DATE AS DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT) )/10000 BETWEEN 18 AND 99
AND EDG.CURRENT_ICD9_LIST IN('550.00', '550.01', '550.02', '550.03','550.10' , '550.11', '550.12', '550.13', '550.90', '550.91', '550.92', '550.93','553.00','553.01','553.02','553.03','551.00',
'551.01','551.02', '551.03', '552.00', '552.01', '552.02', '552.03') -- LIST OF REQUIRED DXS
AND PXI.REF_BILL_CODE IN ('17.11','17.12', '17.13', '17.21', '17.22', '17.23', '17.24', '53.00', '53.01', '53.02','53.03', '53.04', '53.05', '53.10', '53.11','53.12', '53.13','53.14', '53.15','53.16','53.17') -- LIST OF REQUIRED PROCEDURES
ORDER BY pat.pat_name, PXL.PROC_DATE ,HSP.HSP_ACCOUNT_ID;
Thanks in advance for any help.

 

[plog]

Yeah, that's a horrible mess of SQL that I'm not trying to debug, so I will give you a general approach: Divide and conquer. There's no bonus points for getting this into one query, so build it step by step.

1. Build a query to find those with a first type surgery. This query will identify all MRN and the surgery date of the first type of surgery.

2. Build a query to find those with a second second type surgery. This will identify all MRN and surgery dates of the second type of surgery.

At this point #1 & #2 above are independent of each other. You just want to identify people who qualify for each type of surgery. The next steps will compare the two data sets.

3. Link #1 & #2 above in a new query by MRN. Then compare the surgery dates and apply your criteria to that comparision. The result of this query will list all your MRN numbers that you want.

While I said I wouldn't debug your SQL I see some things that give me pause. Mostly, you're using too many fields and tables. You wanted just a count of second surgeries, so pull in the least amount of data required to get there. Those INNER JOINS are really troubling, because if no records exist in those joined tables, it won't include any unmatched data from HSP. You may or may not want that, but I've seen queries like this before and people always wonder why their counts are low--INNER JOINS are usually the culprit. Use only what you need.