Sure, I'll include the real query that I'm using. The problem with it is the same person can have multiple providers but I still need to return the MAX(enc_timestamp) for each provider. So for example, if I use the IN clause but my test patient has 2 or more encounters w/ different providers, the program is the Max timestamp to the first timestamp while I need it to look at the encounter before the first one.
INSERT INTO #tmpCountPatEnc
SELECT pe.person_id,
pe.practice_id,
1,
MAX(enc_timestamp),
NULL,
pe.enc_id
FROM dbo.patient_encounter pe INNER JOIN patient_procedure pp
ON pe.person_id = pp.person_id
AND pe.enc_id = pp.enc_id INNER JOIN dbo.ngkbm_custom_dbp_item_dtl_ ncd
ON pp.cpt4_code_id = ncd.txt_qualifier_2
WHERE (pe.enc_timestamp BETWEEN @Start_Date AND DATEADD(second,-1,DateAdd(day,1, @End_Date))) AND
pe.rendering_provider_id = @Provider_ID AND
pe.practice_id = '0002' AND
ncd.txt_dbpicklist_name = 'Visit Codes' AND
ncd.txt_qualifier_1 = 'Office Visit' AND
pp.delete_ind = 'N'
GROUP BY pe.person_id,pe.practice_id,pe.enc_id
--return previous encounterdate
UPDATE #tmpCountPatEnc
SET prev_enc_timestamp = a.prev_enc_time
FROM (SELECT pe.person_id,
MAX(pe.enc_timestamp) AS prev_enc_time
FROM dbo.patient_encounter pe INNER JOIN #tmpCountPatEnc tpe
ON pe.person_id = tpe.person_id
AND tpe.practice_id = pe.practice_id INNER JOIN patient_procedure pp
ON pe.person_id = pp.person_id
AND pe.enc_id = pp.enc_id INNER JOIN dbo.ngkbm_custom_dbp_item_dtl_ ncd
ON pp.cpt4_code_id = ncd.txt_qualifier_2
WHERE pe.enc_timestamp < tpe.enc_timestamp AND
ncd.txt_dbpicklist_name = 'Visit Codes' AND
ncd.txt_qualifier_1 = 'Office Visit' AND
pp.delete_ind = 'N'
GROUP BY pe.person_id) a INNER JOIN #tmpCountPatEnc t
ON a.person_id = t.person_id
Loop over multivalue Parameter?
Posted - 2012-08-14 : 15:24:31
