Date to Date interval count - Need assistance 1

[aushia]

I am using Crystal version 9
Access Database connection

Situation

I am trying to calculate the length of stay of patients in an ICU unit for a specific fiscal, and month

I am clear regarding the straight formula
{@ICU dis date time}-{@ICU admit date time} this will give me the length of stay.

Now I want to find out how may patients "days" (Length of Stay days) fall within a specific fiscal or a specific month..a patient could be admitted into the ICU on April 1, 2005 but not discharged until June 15th..all I want to get is the days in June for a Month of June report. Same situation if I am looking for a fiscal...they might be admitted before the fiscal started but all I am interested in is the days for that fiscal they were in the Icu...also this holds true for the discharge piece..they may be discharged in a different month or fiscal but I need to calculate just the days

Any help appreciated

 

[aushia]

Hi LB,

Well I thing something is a miss, but not sure what

Here is the Record selection formula

{@ICU admit date time} < dateserial({?fiscalyear}+1,4,1) and
{@ICU dis date time} >= dateserial({?fiscalyear},4,1)

Here is April (start of fiscal) formula

(if {@ICU admit date time} < dateserial({?fiscalyear}, 4,1) and
{@ICU dis date time} > dateserial({?fiscalyear},4+1,1)-1 then
datediff(("s"), date({?fiscalyear}+1,4,1),dateserial({?fiscalyear},4+1,1)-1)+1 else

if {@ICU admit date time} < dateserial({?fiscalyear}, 4,1) and
{@ICU dis date time} <= dateserial({?fiscalyear},4+1,1)-1  and
{@ICU dis date time} >= dateserial({?fiscalyear},4,1) then
datediff(("s"), date({?fiscalyear}+1,4,1),{@ICU dis date time})+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}, 4,1) and
{@ICU admit date time} < dateserial({?fiscalyear},4+1,1)-1 and
{@ICU dis date time} > dateserial({?fiscalyear},4+1,1)-1 then
datediff(("s"),{@ICU admit date time},dateserial({?fiscalyear},4+1,1)-1)+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1,4,1) and
{@ICU dis date time} <= dateserial({?fiscalyear},4+1,1)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

Here is a January formula(same fiscal but new year)

(if {@ICU admit date time} < dateserial({?fiscalyear}+1, 1,1) and
{@ICU dis date time} > dateserial({?fiscalyear}+1,1+1,1)-1 then
datediff(("s"), date({?fiscalyear}+1,1,1),dateserial({?fiscalyear}+1,1+1,1)-1)+1 else

if {@ICU admit date time} < dateserial({?fiscalyear}+1, 1,1) and
{@ICU dis date time} <= dateserial({?fiscalyear}+1,1+1,1)-1  and
{@ICU dis date time} >= dateserial({?fiscalyear}+1,1,1) then
datediff(("s"), date({?fiscalyear}+1,1,1),{@ICU dis date time})+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1, 1,1) and
{@ICU admit date time} < dateserial({?fiscalyear}+1,1+1,1)-1 and
{@ICU dis date time} > dateserial({?fiscalyear}+1,1+1,1)-1 then
datediff(("s"),{@ICU admit date time},dateserial({?fiscalyear}+1,1+1,1)-1)+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1,1,1) and
{@ICU dis date time} <= dateserial({?fiscalyear}+1,1+1,1)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

Think I did it correctly, copied and pasted from your last post except remove the {?fiscalyear}+1, and replaced it with just {?fiscalyear} for months April through December (which is the way I had it before)

Now I get primarily zeros everywhere and some negative values

Here is a sample of the returning data

Apr. May. June. July. Aug. Sept. Oct. Nov. Dec. JAN. Feb. March
Refnum
11,875 -336.00 30.00 29.00 4.55 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1/20/2004 1:44:00PM 7/5/2004 1:10:00PM
12,014 -337.51 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2/23/2004 8:05:00PM 4/28/2004 11:45:00AM
12,044 -358.29 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2/29/2004 6:30:00PM 4/7/2004 5:00:00PM
12,051 -360.23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/1/2004 7:40:00PM 4/5/2004 6:30:00PM
12,084 -336.00 27.65 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/9/2004 3:50:00PM 5/28/2004 3:30:00PM
12,100 -336.00 25.63 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/12/2004 7:45:00PM 5/26/2004 3:00:00PM
12,117 -353.12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/17/2004 6:30:00PM 4/12/2004 9:00:00PM
12,118 -336.00 4.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/17/2004 10:00:00PM 5/5/2004 4:00:00AM
12,122 -363.26 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/18/2004 12:30:00AM 4/2/2004 5:40:00PM
12,133 -345.35 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/20/2004 7:20:00AM 4/20/2004 3:30:00PM
12,138 -348.23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/20/2004 9:00:00PM 4/17/2004 6:30:00PM
12,144 -363.46 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/23/2004 1:00:00PM 4/2/2004 1:00:00PM
12,152 -357.35 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/25/2004 1:35:00AM 4/8/2004 3:43:00PM
12,167 -364.37 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/28/2004 10:15:00PM 4/1/2004 3:00:00PM
12,169 -363.16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/28/2004 11:10:00PM 4/2/2004 8:10:00PM
12,170 -359.49 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/29/2004 2:00:00AM 4/6/2004 12:15:00PM
12,177 -363.42 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/30/2004 1:00:00PM 4/2/2004 1:55:00PM
12,180 -360.28 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 1:50:00PM 4/5/2004 5:15:00PM
12,181 -364.33 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 4:10:00PM 4/1/2004 4:00:00PM
12,182 -364.35 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 5:45:00PM 4/1/2004 3:30:00PM
12,183 -362.36 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 6:10:00PM 4/3/2004 3:20:00PM
12,184 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/1/2004 1:04:00PM 4/4/2004 2:48:00PM
12,185 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/1/2004 6:00:00PM 4/3/2004 2:35:00PM
12,186 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/1/2004 6:15:00PM 4/6/2004 3:20:00PM
12,188 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/2/2004 12:37:00PM 4/3/2004 3:05:00PM
12,189 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/2/2004 2:15:00PM 4/5/2004 3:00:00PM
12,192 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/2/2004 4:20:00PM 4/16/2004 5:30:00PM
12,190 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/2/2004 6:20:00PM 4/4/2004 4:10:00PM
12,193 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/2/2004 6:30:00PM 4/6/2004 2:30:00PM
12,191 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/2/2004 7:55:00PM 4/4/2004 5:30:00PM
12,194 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/3/2004 7:15:00PM 4/5/2004 11:30:00AM
12,195 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/4/2004 5:25:00AM 4/4/2004 10:39:00AM
12,196 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/4/2004 6:15:00AM 4/4/2004 5:30:00PM
12,197 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/4/2004 6:30:00AM 4/16/2004 3:00:00PM
12,198 25.52 18.59 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/4/2004 11:30:00AM 5/19/2004 2:05:00PM
12,200 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/4/2004 2:15:00PM 4/6/2004 4:00:00PM
12,201 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/4/2004 6:05:00PM 4/7/2004 11:55:00AM
12,202 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/5/2004 12:07:00PM 4/6/2004 1:30:00PM
12,203 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/5/2004 1:21:00PM 4/8/2004 11:00:00AM
12,205 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/5/2004 2:15:00PM 4/8/2004 6:55:00PM
12,206 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/5/2004 4:15:00PM 4/16/2004 11:37:00AM
12,204 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/5/2004 6:40:00PM 4/10/2004 1:37:00PM
12,207 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/6/2004 12:16:00PM 4/8/2004 4:35:00PM
12,209 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/6/2004 4:06:00PM 4/7/2004 2:20:00PM
12,208 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/6/2004 5:42:00PM 4/8/2004 12:50:00PM
12,210 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/7/2004 7:28:00AM 4/9/2004 5:15:00PM
12,211 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/7/2004 2:20:00PM 4/9/2004 1:00:00PM
12,212 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/7/2004 6:35:00PM 4/8/2004 10:15:00PM
12,213 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/7/2004 8:00:00PM 4/9/2004 3:30:00PM
12,214 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/8/2004 1:00:00AM 4/8/2004 6:30:00PM
12,215 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/8/2004 11:55:00AM 4/9/2004 12:05:00PM
12,216 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/8/2004 3:45:00PM 4/10/2004 3:00:00PM
12,217 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/8/2004 5:30:00PM 4/9/2004 4:30:00PM
12,218 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/8/2004 6:50:00PM 4/15/2004 2:00:00PM
12,219 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/8/2004 9:10:00PM 4/11/2004 11:30:00AM
12,220 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/9/2004 7:50:00AM 4/10/2004 3:00:00PM
12,221 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/9/2004 9:15:00AM 4/13/2004 11:00:00PM
12,222 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/9/2004 2:23:00PM 4/9/2004 3:28:00PM
12,223 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/9/2004 11:00:00PM 4/10/2004 2:00:00PM
12,224 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/10/2004 1:15:00PM 4/15/2004 3:00:00PM
12,225 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/10/2004 6:40:00PM 4/11/2004 3:05:00PM
12,226 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/11/2004 3:45:00AM 4/12/2004 3:15:00PM
12,228 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/11/2004 11:40:00AM 4/12/2004 4:10:00PM
12,227 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/11/2004 1:45:00PM 4/14/2004 2:00:00PM
12,229 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/11/2004 3:45:00PM 4/16/2004 4:15:00PM
12,230 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/12/2004 1:00:00AM 4/12/2004 7:30:00PM
12,231 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/12/2004 1:15:00PM 4/15/2004 2:30:00PM
12,232 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/12/2004 6:35:00PM 4/14/2004 2:00:00PM
12,234 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/13/2004 12:50:00PM 4/14/2004 4:00:00PM
12,233 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/13/2004 4:30:00PM 4/13/2004 9:40:00PM
12,235 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/13/2004 5:15:00PM 4/15/2004 11:11:00AM
12,236 16.25 17.56 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/13/2004 6:00:00PM 5/18/2004 1:30:00PM
12,237 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/13/2004 6:50:00PM 4/27/2004 5:30:00PM
12,238 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/14/2004 12:40:00AM 4/15/2004 3:00:00PM
12,239 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/14/2004 1:10:00AM 4/22/2004 7:00:00PM
12,241 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/14/2004 1:14:00PM 4/16/2004 1:30:00PM
12,240 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/14/2004 1:40:00PM 4/15/2004 4:30:00PM
12,243 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/14/2004 6:05:00PM 4/16/2004 2:55:00PM
12,242 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/14/2004 6:20:00PM 4/15/2004 3:00:00PM
12,244 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 9:15:00AM 4/17/2004 1:35:00PM
12,246 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 11:40:00AM 4/16/2004 3:20:00PM
12,245 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 1:10:00PM 4/28/2004 1:30:00PM
12,247 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 3:50:00PM 4/18/2004 10:45:00AM
12,250 14.29 10.60 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 5:03:00PM 5/11/2004 2:20:00PM
12,249 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 6:00:00PM 4/17/2004 11:30:00AM
12,248 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/15/2004 7:00:00PM 4/16/2004 2:53:00PM
12,253 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/16/2004 12:07:00PM 4/16/2004 6:00:00PM
12,251 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/16/2004 12:55:00PM 4/19/2004 10:40:00AM
12,252 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/16/2004 1:05:00PM 4/26/2004 2:30:00PM
12,254 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/16/2004 3:00:00PM 4/18/2004 2:30:00PM
12,256 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/16/2004 5:20:00PM 4/19/2004 4:58:00PM
12,255 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/16/2004 5:35:00PM 4/17/2004 2:45:00PM
12,257 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/17/2004 4:10:00AM 4/17/2004 8:30:00PM
12,258 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/18/2004 1:00:00AM 4/19/2004 2:30:00PM

 

[aushia]

Fixed the first negative in April, there was 2 {?fiscalyear}+1 where there should not have been now they are not negatives..

Looking at the pattern it seems that as long as the admit and discharge dates are separate months then it is calculating if in the same month we are getting "0" value..

Sorry about the April code, but I checked all others and they were fine...

Actually, I am surprizing myself that I actually am starting to get this..a little...at least enough to find my booboo...

Now we just need to figure out why all the 0s

 

[lbass]

Please clarify whether your fiscal year is identified by the beginning or the end year. Please also specify the year you used when showing the last data.

-LB

 

[aushia]

Well LB,

Don't know what I did, by the time I started //ing things out and un //ing , not really changing anything that I know of anyway...things are substainially better, all calculations are working correctly and the negatives are gone and are now 0s the only thing left with this inital verification of data is there is still an error around the last day of each month not calcing correctly here are the samples of data not working but these are the only things now not working



Apr. May. June. July. Aug. Sept. Oct. Nov. Dec. JAN. Feb. March
Refnum

10,690 0.92 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/29/2003 3:55:00PM 4/30/2003 2:00:00PM
10,691 0.57 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 12:10:00AM 4/30/2003 1:45:00PM
10,692 0.00 0.63 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 6:40:00AM 5/1/2003 3:00:00PM
10,693 0.00 0.53 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 2:05:00PM 5/1/2003 12:39:00PM
10,694 0.00 0.43 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 2:20:00PM 5/1/2003 10:15:00AM
10,695 0.25 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 2:40:00PM 4/30/2003 8:40:00PM
10,696 0.00 5.72 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 3:20:00PM 5/6/2003 5:20:00PM
10,698 0.00 10.74 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 4:30:00PM 5/11/2003 5:45:00PM
10,697 0.00 0.65 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4/30/2003 7:20:00PM 5/1/2003 3:30:00PM

10,841 0.00 0.90 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5/30/2003 8:20:00PM 5/31/2003 5:50:00PM
10,842 0.00 0.00 1.46 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5/31/2003 12:30:00PM 6/2/2003 11:00:00AM
10,844 0.00 0.00 1.38 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5/31/2003 6:25:00PM 6/2/2003 9:10:00AM
10,843 0.00 0.00 0.36 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5/31/2003 7:35:00PM 6/1/2003 8:35:00AM

10,965 0.00 0.00 0.90 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 6/28/2003 4:40:00PM 6/29/2003 2:10:00PM
10,967 0.00 0.00 0.38 1.56 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 6/29/2003 2:55:00PM 7/2/2003 1:30:00PM
10,968 0.00 0.00 0.00 2.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 6/30/2003 6:25:00AM 7/3/2003 4:00:00AM
10,969 0.00 0.00 0.00 30.00 1.79 0.00 0.00 0.00 0.00 0.00 0.00 0.00 6/30/2003 8:50:00PM 8/2/2003 7:00:00PM

11,090 0.00 0.00 0.00 0.66 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7/30/2003 11:20:00PM 7/31/2003 3:15:00PM
11,091 0.00 0.00 0.00 0.00 18.81 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7/31/2003 1:40:00AM 8/19/2003 7:30:00PM
11,092 0.00 0.00 0.00 0.00 1.68 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7/31/2003 4:25:00PM 8/2/2003 4:22:00PM
11,093 0.00 0.00 0.00 0.00 0.63 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7/31/2003 5:45:00PM 8/1/2003 3:00:00PM

11,217 0.00 0.00 0.00 0.00 1.47 0.00 0.00 0.00 0.00 0.00 0.00 0.00 8/30/2003 2:45:00AM 8/31/2003 2:00:00PM
11,218 0.00 0.00 0.00 0.00 0.00 29.00 30.00 17.63 0.00 0.00 0.00 0.00 8/31/2003 5:45:00AM 11/18/2003 3:00:
11,220 0.00 0.00 0.00 0.00 0.14 0.00 0.00 0.00 0.00 0.00 0.00 0.00 8/31/2003 5:34:00PM 8/31/2003 9:00:00PM

11,356 0.00 0.00 0.00 0.00 0.00 0.07 3.15 0.00 0.00 0.00 0.00 0.00 9/29/2003 10:15:00PM 10/4/2003 3:30:00AM
11,357 0.00 0.00 0.00 0.00 0.00 0.00 0.58 0.00 0.00 0.00 0.00 0.00 9/30/2003 12:36:00PM 10/1/2003 2:00:00PM
11,358 0.00 0.00 0.00 0.00 0.00 0.00 1.44 0.00 0.00 0.00 0.00 0.00 9/30/2003 1:15:00PM 10/2/2003 10:35:00AM
11,360 0.00 0.00 0.00 0.00 0.00 0.00 5.42 0.00 0.00 0.00 0.00 0.00 9/30/2003 6:45:00PM 10/6/2003 10:05:00AM
11,359 0.00 0.00 0.00 0.00 0.00 0.00 2.67 0.00 0.00 0.00 0.00 0.00 9/30/2003 9:08:00PM 10/3/2003 4:10:00PM

11,496 0.00 0.00 0.00 0.00 0.00 0.00 0.28 0.63 0.00 0.00 0.00 0.00 10/30/2003 5:15: 11/1/2003 3:12:00PM
11,498 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4.54 0.00 0.00 0.00 0.00 10/31/2003 7:20: 11/5/2003 1:00:00PM
11,501 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.54 0.00 0.00 0.00 0.00 10/31/2003 11:15: 11/1/2003 1:00:00PM
11,503 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3.95 0.00 0.00 0.00 0.00 10/31/2003 2:30: 11/4/2003 10:55:00PM
11,502 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12.65 0.00 0.00 0.00 0.00 10/31/2003 3:20: 11/13/2003 3:30:
11,504 0.00 0.00 0.00 0.00 0.00 0.00 0.27 0.00 0.00 0.00 0.00 0.00 10/31/2003 4:30: 10/31/2003 11:00:

11,656 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.08 30.00 30.00 10.70 0.00 11/29/2003 10:00: 2/11/2004 4:45:00PM
11,655 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.02 2.60 0.00 0.00 0.00 11/29/2003 11:30: 12/3/2003 2:30:00PM
11,657 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 17.58 0.00 0.00 0.00 11/30/2003 12:15: 12/18/2003 2:00:
11,658 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.56 0.00 0.00 0.00 11/30/2003 7:10: 12/1/2003 1:30:00PM

11,792 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.27 1.06 0.00 0.00 12/30/2003 5:35: 1/2/2004 1:30:00AM
11,793 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.65 0.00 0.00 12/31/2003 1:10: 1/1/2004 3:41:00PM
11,794 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 11.63 0.00 0.00 12/31/2003 8:10: 1/12/2004 3:00:00PM

11,924 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.02 0.00 0.00 1/30/2004 2:30:00PM 1/31/2004 3:00:00PM
11,925 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5.48 0.00 1/31/2004 9:05:00AM 2/6/2004 11:25:00AM
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.70 0.00 2/28/2004 6:10:00PM 2/29/2004 11:00:00AM
12,044 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 30.00 2/29/2004 6:30:00PM 4/7/2004 5:00:00PM

12,179 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.96 3/30/2004 5:50:00PM 3/31/2004 4:54:00PM
12,180 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 1:50:00PM 4/5/2004 5:15:00PM
12,181 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 4:10:00PM 4/1/2004 4:00:00PM
12,182 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 5:45:00PM 4/1/2004 3:30:00PM
12,183 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3/31/2004 6:10:00PM 4/3/2004 3:20:00PM

Wow, I think you need to be on the hospitals payroll, LB after I get this all straight would you mind if I debriefed with you, so that I can verify that the lessons learned with you are straight in my mind so I can take this experience and apply it else where or who knows maybe even help someone else...wouldn't that be something

 

[aushia]

The fiscals are April 1 2003-2004 March 31
April 1 2004-2005 March 31
April 1 2005-2006 March 31

First set of Data was the April 2004-Mar 31 2005
The above data was April 2003-2004 Mar 31

 

[lbass]

I think you need to change the formulas to use datetimes, instead of just dates:

(if {@ICU admit date time} < dateserial({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} > dateserial({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"), date({?fiscalyear}+1,1,1,0,0,0),dateserial({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} < dateserial({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} <= dateserial({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} >= dateserial({?fiscalyear}+1,1,1,0,0,0) then
datediff(("s"), date({?fiscalyear}+1,1,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU admit date time} <= dateserial({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} > dateserial({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},dateserial({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1,1,1,0,0,0) and
{@ICU dis date time} <= dateserial({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

-LB

 

[aushia]

Error message: too many arguments have been given to this function see red

Question: in the last formula for January a "1" was where the green two now is..are we saying Feb. 1st 23:59:59 and the the -1 takes us back to Jan. 31 same time?

(if {@ICU admit date time} < dateserial({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} > dateserial({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"), date({?fiscalyear}+1,1,1,0,0,0),dateserial({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} < dateserial({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} <= dateserial({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} >= dateserial({?fiscalyear}+1,1,1,0,0,0) then
datediff(("s"), date({?fiscalyear}+1,1,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU admit date time} <= dateserial({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} > dateserial({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},dateserial({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= dateserial({?fiscalyear}+1,1,1,0,0,0) and
{@ICU dis date time} <= dateserial({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

 

[aushia]

(if {@ICU admit date time} < datetime({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} > datetime({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"), datetime({?fiscalyear}+1,1,1,0,0,0),datetime({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear}+1,2,1,23,59,59)-1  and
{@ICU dis date time} >= datetime({?fiscalyear}+1,1,1,0,0,0) then
datediff(("s"), datetime({?fiscalyear}+1,1,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} > datetime({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},datetime({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}+1,1,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

I think this works..gonna try it with a couple more months..what do you think?

 

[lbass]

Oops. Replace the dateserial with datetime.

(if {@ICU admit date time} < datetime({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} > datetime({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"), datetime({?fiscalyear}+1,1,1,0,0,0),datetime({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} >= datetime({?fiscalyear}+1,1,1,0,0,0) then
datediff(("s"), datetime({?fiscalyear}+1,1,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}+1, 1,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear}+1,2,1,23,59,59)-1 and
{@ICU dis date time} > datetime({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},datetime({?fiscalyear}+1,2,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}+1,1,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear}+1,2,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

The 2 is just the monthnumber--I think it's easier to follow if you use the actual number instead of the 1 +1, which is just a holdover from when I thought you were going to use a parameter for the month.

-LB

 

[aushia]

Ha, see that I beat you too it, it is so scarey when you start to get it...

Here is the next question,

a few posts ago you said something about averages and running totals..how do you insert the running total and {@formula}<>0 where does that go...

I figured I better get these questions in before you are completely tired of me.

 

[lbass]

Please reread my post and try the suggestion. It's all there.

-LB

 

[aushia]

Thanks so much, it worked,I so much appreciate all the time you have spend with me on this, I will work away on the rest and let you know how I did.

This has been the most I have learned and the best experience I have had on this site.

Again, Thanks LB

 

[aushia]

I was hoping that I could get a little additional help.

The support and solution I have received in this thread has worked perfectly,

however,

I have realized I have missed a select group of ICU patients

These are patients that have been admitted to the ICU/CCU unit and have been there a long time either awaiting transplant or other reasons. These patients could have been in the unit for up to 6 months but do not have a discharge date to calculate on.

Is there a way to add a piece to this formula that says if there is no discharge date then calculate based on the current date..this way I will get numbers for the patients in the ICU which have not been discharged from the unit but should be counted in the months count of pt days

I have made 12 formula of which this is an example...I am looking to add or modify to include patients not discharged yet but still in the unit

(if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU dis date time} > datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear},5,1,23,59,59)-1  and
{@ICU dis date time} >= datetime({?fiscalyear},4,1,0,0,0) then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
{@ICU dis date time} > datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= datetime({?fiscalyear},4,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

 

[lbass]

Try:

(if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
(
isnull({@ICU dis date time}) or
{@ICU dis date time} > datetime({?fiscalyear},5,1,23,59,59)-1
) then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
{@ICU dis date time} >= datetime({?fiscalyear},4,1,0,0,0) then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
(
isnull({@ICU dis date time}) or
{@ICU dis date time} > datetime({?fiscalyear},5,1,23,59,59)-1
) then
datediff(("s"),{@ICU admit date time},datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= datetime({?fiscalyear},4,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1)/60/60/24

-LB

 

[aushia]

Thanks LB,

I will give it a try, The report is amazing by the way, I did all the running totals as you described and it is providing the unit with the correct information for the first time in 2 years...

 

[aushia]

LB,

I have changed the formulas but my record selection formula is still

{@ICU dis date time} >= dateserial({?fiscalyear},4,1) and
{@ICU admit date time} < dateserial({?fiscalyear}+1,4,1)

I have tried to modify myself but I must be doing something wrong because I get data in Mar 06 which hasn't happened yet...how do I change this to pick up if there isn't a discharge date

 

[aushia]

LB should I add somewhere in the month cal formula that either I do a datediff

datediff(("s"),{@ICU admit date time},datetime({?fiscalyear},5,1,23,59,59)-1)+1 or

somehow tell it if not discharge date do a date diff to current date???

 

[lbass]

Change the record selection formula to:

(
isnull({@ICU dis date time}) or
{@ICU dis date time} >= dateserial({?fiscalyear},4,1)
) and
{@ICU admit date time} < dateserial({?fiscalyear}+1,4,1)

For the monthly formulas, let's change it back again and do it a little differently:

(
if isnull({@ICU dis date time}) and
{@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
currentdatetime >= datetime({?fiscalyear},4,1,0,0,0) and
currentdatetime <= datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff("s", datetime({?fiscalyear},4,1,0,0,0), currentdatetime)+1 else

if isnull({@ICU dis date time}) and
{@ICU admit date time} >= datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
currentdatetime >= datetime({?fiscalyear},4,1,0,0,0) and
currentdatetime <= datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff("s",{@ICU admit date time},currentdatetime)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
isnull({@ICU dis date time}) and
currentdatetime > datetime({?fiscalyear},5,1,23,59,59)-1
then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
isnull({@ICU dis date time}) and
currentdatetime > datetime({?fiscalyear},5,1,23,59,59)-1
then
datediff(("s"),{@ICU admit date time},datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU dis date time} > datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} >= datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU admit date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
{@ICU dis date time} > datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},datetime({?fiscalyear},5,1,23,59,59)-1)+1 else

if {@ICU admit date time} < datetime({?fiscalyear}, 4,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 and
{@ICU dis date time} >= datetime({?fiscalyear},4,1,0,0,0) then
datediff(("s"), datetime({?fiscalyear},4,1,0,0,0),{@ICU dis date time})+1 else

if {@ICU admit date time} >= datetime({?fiscalyear},4,1,0,0,0) and
{@ICU dis date time} <= datetime({?fiscalyear},5,1,23,59,59)-1 then
datediff(("s"),{@ICU admit date time},{@ICU dis date time})+1
)/60/60/24

-LB

 

[aushia]

Wow, I will give it a try..and let you know...learning curve is sure getting steep...

 

[aushia]

Absolutely Perfect...now I just have to spend some time really looking at what you did so I can repeat it for other reports...

Thanks LB