Are you here or not ? (not using iterno())

[#1]

Hi everyone,

I have got a bizarre situation :

I have a list of patients in a table :

Patients_appntmt:

LOAD

name,

id_patient,

date_in,//start date

date_out, // end date

resident......

I want to know for each day ,how many patients were there during a month or any weekday() and at a precise date

Explanations may not be clear.

I succeed, but I used iterno() in the script. It multiplies lines of data.

LOAD

...

date_in -1 + iterno() as super_date

RESIDENT ....

WHILE date_out-date_in <=iterno() ;

If there any other way, than using iterno(),I'll be very glad to know.

Thx,

Will

[johnw]

This would typically be handled with an intervalmatch, but the net result is pretty much the same - one row for every date in the range. I probably use iterno() just as often as intervalmatch. So you're probably good as is.

I wouldn't do the following for just dates due to possible performance problems. However, lets say you had timestamps, so it wasn't possible to use iterno() or intervalmatch, since you'd get one row for every fraction of a second for every patient. You could then create an island_calendar with an island_timesamp disconnected from any of your data. If you then selected the range of 5 PM April 16, 2010 to 3 AM April 20, 2010, I think you could get the count of patients treated in that range like this:

count(distinct if(timestamp_in < max(island_timestamp)
and timestamp_out > min(island_timestamp),id_patient))

[#3]

I used intervall match and it works pretty well.

Thanks John