SELECT     tbl_text.patient_id, tbl_text.item_text, tbl_encounters.encounter_date, tbl_text.date_created
FROM         tbl_text INNER JOIN
                      tbl_encounters ON tbl_encounters.encounter_id = tbl_text.encounter_id AND tbl_text.patient_id = tbl_encounters.patient_id
WHERE     (tbl_text.item_id = 129) AND (tbl_encounters.record_active = 1)
ORDER BY tbl_text.patient_id

patient_id     item_text           encounter_date     date_created
10076          Cal PERS PPO        1/1/1980           8/8/2011
10077          Cal PERS PPO        1/1/1980           8/8/2011
10077          PPO Not Billable    3/16/2012          4/3/2012
10078          Cal PERS PPO        1/1/1980           8/9/2011

declare @tbl_text TABLE (
	[patient_id] [int] NOT NULL,
	[encounter_id] [int] NOT NULL,
	[item_id] [int] NOT NULL,
	[item_text] [varchar](128) NULL,
	[date_created] [datetime] NULL
)

declare @tbl_encounters TABLE (
	[encounter_id] [int] NOT NULL,
	[patient_id] [int] NOT NULL,
	[encounter_date] [datetime] NULL,
	[record_active] [bit] NOT NULL DEFAULT ((0))
)


insert into @tbl_text
values
  (10076, 1, 129, 'Cal PERS PPO', '8/8/2011')

insert into @tbl_text
values
  (10077, 2, 129, 'Cal PERS PPO', '8/8/2011')
insert into @tbl_text
values
  (10077, 3, 129, 'PPO Not Billable', '4/3/2012')
insert into @tbl_text
values
  (10078, 4, 129, 'Cal PERS PPO', '8/9/2011')
  
insert into @tbl_encounters
values
  (1, 10076, '1/1/1980', 1)
insert into @tbl_encounters
values
  (2, 10077, '1/1/1980', 1)
insert into @tbl_encounters
values
  (3, 10077, '3/16/2012', 1)
insert into @tbl_encounters
values
  (4, 10078, '1/1/1980', 1)
  

SELECT t.[item_text],COUNT(t.[patient_id]) AS PatientCnt
FROM @tbl_text t
INNER JOIN @tbl_encounters e
ON e.[encounter_id] = t.[encounter_id]
AND e.[patient_id] = t.[patient_id]
WHERE  e.[encounter_date] <= @date
GROUP BY t.[item_text]

quote:

---

Originally posted by GregDDDD

Hmmm, that seems too easy or maybe I was over thinking it. If a patient moves twice in the month being checked or has multiple encounter dates/Health Plans prior to the user defined date I think they end up in multiple groups.

I think I need to take the MAX(encounter_date) that is prior to the user defined date and then count.

I'll play around with it.

Greg

---

quote:

---

Originally posted by GregDDDD

Not twice in the same plan, but twice in the same month to different plans. I'm not sure they would, but technically that is possible, even if it is a data entry error. Not likely, I'll admit. Even if they didn't move twice in the same month they could change multiple times prior to the date supplied by the user, so WHERE e.[encounter_date] <= @date could return multiple rows for a single patient and rows are only added when there is a change.

Greg

---

SELECT t.[item_text],COUNT(t.[patient_id]) AS PatientCnt
FROM @tbl_text t
CROSS APPLY (SELECT TOP 1 *
             FROM  @tbl_encounters 
             WHERE [patient_id] = t.[patient_id]
             AND [encounter_date] <= @date
             ORDER BY [encounter_date] DESC
            )e
GROUP BY t.[item_text]