Query filter problem

[Tom Patten]

Re: Query filter problem

Getting Close.

Changed the filter below to: bm ="None", but its still picking up residents who hand "none" BM in any of the last 3 days. It needs to select residents who have NOT had a BM over ALL three days (or 72 hour with no BM). So a resident who had a BM yesterday, but didn't have one the day before yesterday, is not on the list.

[Code]

'tbl.query_create("T","bm <> 'none'","pteid+invert(cdate(t_date))")
tbl.query_create("T","bm = 'none'","pteid+invert(cdate(t_date))")

'lbm_arr_var = lbm_arr_var+ crlf()+if(tbl.t_date < date()-3,tbl.pteid + " " + (tbl.t_date+1),"")
lbm_arr_var = lbm_arr_var+ crlf()+if(tbl.t_date > date()-3,tbl.pteid + " " + (tbl.t_date+1),"")

[Code]

I got a report to print that could work, but I'd like to see if we can get this to work.

~ Tom

[Ray in Capetown]

Re: Query filter problem

tbl.query_create("T","bm = 'none' .and. t_date>=date()-2","pteid")

Then any Patient with three records returns a hit
I could research a few alternatives other than a record loop, but off the top of my head don't know of a function or combination - that would probably include record count

[Tom Patten]

Re: Query filter problem

Hi Ray,

Yes, that's the filter I'm working with; but getting the count right in the inner most nested "While" statement is causing me to pull my hair out.

~ tom

[Stan Mathews]

Re: Query filter problem

tbl.query_create("T","bm <> 'none'","pteid+invert(cdate(t_date))")

This says select all records where bm is acceptable (not none) and put them in order by patient and descending by date. This tells us the last acceptable date for each patient (first record for each patient in the query order). When we know that we can check that date against the current date -3. If the last acceptable date is earlier than three days ago

lbm_arr_var = lbm_arr_var+ crlf()+if(tbl.t_date < date()-3,tbl.pteid + " " + (tbl.t_date+1),"")

then a bm reading of none has been recorded for the past three days and the first none reading was two days ago.

I don't use arrays very much and I am not visualizing what your array contains. Does it contain

pteid - t_date - none

?

[Stan Mathews]

Re: Query filter problem

Try this. You'd have to add the patient name lookup but that shouldn't be too hard.

dim choices[0] as c
tbl = table.open("yourtablename")
'query for none in descending date order by patient
tbl.query_create("T","bm = 'none'","pteid+invert(cdate(t_date))")
tbl.fetch_first()
tid = tbl.pteid
while .not. tbl.fetch_eof()
	if tbl.t_date = date()
		tbl.fetch_next()
		if tbl.t_date = date()-1 .and. tbl.pteid = tid
			tbl.fetch_next()
			if tbl.t_date = date()-2 .and. tbl.pteid = tid
				'patient none for three days
				choices[] = tbl.pteid + " - " + (tbl.t_date) + " - " + tbl.bm
				choices[] = tbl.pteid + " - " + (tbl.t_date+1) + " -  " + tbl.bm
				choices[] = tbl.pteid + " - " + (tbl.t_date+2) + " -  " + tbl.bm
			end if
		end if
	end if
	while tbl.pteid = tid .and. .not. tbl.fetch_eof()
		tbl.fetch_next()
	end while
	tid = tbl.pteid
end while
tbl.close()

sort_array("choices")
var->v_Char = ui_get_list_array("NO BM in any of the past 3 Days", 1,"Choices")

[Tom Patten]

Re: Query filter problem

Thanks Stan. Your code didn't work the first times I tried. I'll have to further edit later.

I got the following to work, based on your earlier suggestion.

dim tbl as P
tbl = table.open("Vitals")
tbl.query_create("T","bm='none'.and.t_date>=Date()-3","pteid")
tbl.fetch_first()
v_Pteid = tbl.pteid
vCount = 0
lbm_arr_var=""
v_pteid = tbl.pteid
while .not. tbl.fetch_eof()
	vCount = IF(tbl.pteid<>v_pteid,0,vCount)
	v_pteid = tbl.pteid
	IF tbl.pteid = v_pteid then
		vCount = vCount+1
 		lbm_arr_var=IF(vCount=3,lbm_arr_var+ crlf()+LookupC("F",tbl.pteid,"Fullname","Patients","Pteid"),lbm_arr_var)
 	 	'ui_msg_box("Pteid and Count",lbm_arr_var+" - "+alltrim(str(vcount,2,0)))
	end if
	tbl.fetch_next()
end while

tbl.close()
msgbox("Constipated Residents",lbm_arr_var)

END

Much thanks to you all.

~ Tom

[Tom Patten]

Re: Query filter problem

Stan,

Regarding your previous post. Very interesting.

What you were doing went right over my head.

Thanks for explaining.

~ Tom