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

 

[lbass]

I think you could do something like the following, using number parameters for the year and month:

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

-LB

 

[aushia]

Thanks so much, I will give it a try, and let you know

 

[aushia]

LB,

I think I am more of a newbie user than I even realized,

I can understand where you are going witht the solution you gave me but I have a confusion around the {?year}
{?Month} parameters,

Would these parameters be based on a field within the database?

the {@ICU dis date time} and {@ICU dis date time} are formulas I had to create...I had to initially pull apart a Date time field , then pull apart a time date field then put them back together to get a real date time...I cannot base my parameter on the formula fields,

I have not don't much work with crystal around parameters but obviously need more practice....how do I create the number parameters you suggested???

Thanks for your help, this is such an important report for the ICU/CCU area and we have very little internal crystal support. Sadly enough I am it...thanks again for your suggestions

 

[lbass]

Go to the field explorer->parameter->new and first create a year parameter where you select "number" as the datatype, and then create a month parameter, again choosing number as the datatype. Then in your record selection formula, in order to pull for records for a specific month, you would use a formula like:

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

This will pull records for people who are admitted before the end of the month (possibly in an earlier month), and who were discharged sometime on or after the first of the month. Then you can use the earlier formula to determine the length of stay within the selected month.

-LB

 

[aushia]

LB you are amazing, I think I am starting to understand It is doing something, I have to verify that the numbers it is getting is correct...but I think I am so close.

Now teach, how do I create this parameter for a range of months...

cause we have a fiscal that runs from April 1 to March 31 and that would be our 04-05 fiscal

but I would also need Q1 through Q4 numbers

The parameter I did, which incidentally I didn't realize you could do without a field , that worked well but how do I now tell it I want not just one month but the 3 that make up Q1 or how do choose the years for the fiscal...

this has been the best experience I have had on this forum I really appreciate the help, I can see this will work and I will really learn from what you have shown me.

 

[lbass]

Please explain what you want your final report to look like, including the group structure. Maybe show a sample. Also, please explain more about the date ranges you want to run this for. Your statement that you would ALSO need Q1 to Q4 numbers is confusing--is this just another example of a possible range, or do you mean you want to be able to compare one month to a quarter or set of quarters? Also is your fiscal year based on yearend? In other words does FY 05 end in March 05?

-LB

 

[aushia]

Hi LB,

Did some reports and what is happen is that each case seems to be only counting once, the first instance that the case is reference...it will calculate the days in april that the case was in the ICU and even though it says the case has a discharge date in May, when I look through the numbers in May the case doesn't appear..

I wish I could show you the report..my explaination may not be clear

 

[lbass]

You need to respond to my last post. Note that one record cannot appear more than once on a report, so if you are grouping by date and hoping that a record will appear once for each group covered by the admission period, it won't happen. There are ways to evaluate one record more than once, however, but you need to provide much more information.

-LB

 

[aushia]

Grouped by month
Grouped by ICU or CCU

Details Section

refnum - is the unique identifier for each individual seen
Patient days- days the patient was in the unit during a particular month, quarter , or fiscal

The end reports must be around the following date periods

Fiscal - which runs between April 1, 2004 and March 31 2005
Q1 is the first quarter in the fiscal April 1, 2004 - June 30 2004
Q2 is the second quarter in the fiscal July 1 2004 -Sept 30, 2004
Q3 is the 3rd quarter Oct 1, 2004 - Dec 31, 2004
Q4 is the 4th January 1 2005 -March 31 2005

What I need is a monthly report of patient days down to hours in the ICU/CCU
Then I need to roll that up to quarters
Then I need to roll that up to fiscal

I think I answered my own question about why the numbers are showing up only in the month of admission..there would be no reference to the individual refnum for the month of May,

 

[aushia]

Wow LB, I am actually getting this, I see what the report is doing, even if it does show under the april catagory it is giving me the number of days in May that the person was in the ICU, this is amazing...

 

[lbass]

You are not being specific enough--you should identify fields or formulas when identifying the group field. I'm guessing you do not have a "month" field, but instead that you are grouping on your admission date. I think you should remove the group on month and instead do a series of formulas like the one in my post of 17 Feb 15:10, only replacing the {?month} with an actual month number. I would change the {?year} parameter to {?fiscalyear}, and for months 4 to 12, use {?fiscalyear}-1 in the formulas. After creating 12 formulas, one for each month, you can insert summaries at the group and grand total level. You could also then sum the formulas to create quarterly totals.

//{@Quarter1}:
{@Aprgrptot}+{@Maygrp}+{@Jungrp}

Place {@Quarter1} and the other quarterly formulas in the detail section and then insert summaries on them also.

You can then add the Quarters together for a full year:

//{@totalgrp}:
{@Quarter1} + {@Quarter2}+{@Quarter3}+{@Quarter4}

Again, insert summaries on this at both group and report levels.

-LB

 

[aushia]

I think I understand, I will give it a try, I was grouping around admission date. I will remove that and do exactly as you suggest. LB, I really appreciate the help, as you give me these solutions I am also going over each in detail so I can learn exactly what you are showing me..

I will give this a try and let you know how it works

Thanks

 

[lbass]

Note that the above solution is dependent upon the refnum not repeating, i.e., it assumes there is no row inflation. You might try a couple of formuas first and see if the results appear to be accurate.

-LB

 

[aushia]

Lb,

I am starting with the January formula

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

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

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

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

then I created the same formula for Feb & March changing the month number in the formula.

in the Record selection I put the formula

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

What is happening is that in January I start out with 151 records when I put in February it reduces to 56 ( because 56 of the 151 patients were still there in February) when I put in March I am down to 4 records ( on 4 patients from the orginal 151 were still there in March)

Do I have something wrong.

I tryed leaving it the orginal way you suggested and creating 11 subforms ...that worked well except I couldn't graph the year month by month..nor could I roll up to quarter or year...

I think I am just missing something on the last solution you gave me...maybe I shouldn't have the record selection
but when I don't everything is a negitive number

Is this the type of report that really needs to have the records evaluated 12 times..and is that possible..

to clarify..I have removed all grouping from this report...it is simply the above formulas for the three months...

Thanks

 

[aushia]

I am a little closer,
I have changed the "and" to an "or" in the record selection statement

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

Now I am getting these results..

Jan. .69
Feb. -30.31
Mar. -58.31

Admit date time Dis Date Time
12/12/2004 1:20:00am 1/1/2005 4:30:00pm


Another example of results

Jan. -38.48
Feb. -10.48
Mar. 0.67

Amit date time Dis date Time
3/10/2005 11:30:00am 3/11/2005 3:30:00 am


I understand the math it is doing somehow I need to tell crystal not to do the math if it is before or after discharge date.

 

[aushia]

LB,
I feel like a snowball going down hill fast

I seem to be finding more questions with this solution. I get an email this morning asking me to add average and median Patient days to the report and the average apache score...the appache would be a problem except with the report set up this way the appache doesn't seem to be as time specific....I think I would have to now create a formula that says if there is an appache score during the month of "x" then total and average...I think I need someone to just shot me.
Here is what I get:
Now I get one row for each line in the data base so if patient refnum 123 has patient days in September and October it gives me the number (that works great) but in Nov. I get 0 December I get0. Which I expect but when it comes to getting my average patient days for Sept or Median Patient Stay days it is including the 0s as part of the calculation...I have tried suppressing 0s nothing works ...I sure it is staring me in the face...

I am almost thinking that I need to figure out how to evaluate a record more than once....

 

[lbass]

Assuming you are pulling data for the fiscal year, your selection formula should just be:

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

I don't see how you could be getting negative numbers. How are you converting the seconds to days?

Before tackling the average, etc., you need to get the basic month formulas calculating correctly. Instead of showing the results per month, please show some sample data at the detail level for one of the monthly formulas--this is how to check for accuracy. Or are you saying these are now working correctly?

To exclude zero values from an average, insert a running total that averages one of the formulas(month formula or total formula), evaluate using a formula:

{@formula} <> 0

Reset at group level or never. Place the running total in the group or report footer, depending upon where you are doing the calculation. Same for median.

I don't know what an appache score is or how it fits in with your data.

-LB

 

[aushia]

I couldn't get the full refnum to copy over or the time field but they are there and the math is correct...it just seems to be doing a calculation from a point in time for each record. I could do the If {@January}>0 then {@January} else 0 and that would get rid of the negative values but I wasn't sure if that was the way to go..to go from seconds I added /60/60/24 on to the end of the formula (that I did for each month)

RefnApr. May. June.July.Aug. Sept. Oct. Nov. Dec. JAN. Feb. March
13, 29. 8. -22 -52. -83 -114 -144. -175. -205. -236. -267. -295 2/10/2005 5/9/2005
13, 6. -23 -54 -84. -11 -146 -176. -207. -237. -268. -299. -327 2/21/2005 4/7/2005
13, 26. -3. -34 -64. -95 -126 -156. -187. -217. -248. -279. -307 2/25/2005 4/27/2005
13, 17. -12 -43 -73. -10 -135 -165. -196. -226. -257. -288. -316 2/27/2005 4/18/2005
13, 10. -19 -50 -80. -11 -142 -172. -203. -233. -264. -295. -323 3/4/2005 4/11/2005
13, 0. -29 -60 -90. -12 -152 -182. -213. -243. -274. -305. -333 3/7/2005 4/1/2005
13, 14. -15 -46 -76. -10 -138 -168. -199. -229. -260. -291. -319 3/10/2005 4/15/2005
13, 0. -29 -60 -90. -12 -152 -182. -213. -243. -274. -305. -333 3/11/2005 4/1/2005
13, 29. 12. -18 -48. -79 -110 -140. -171. -201. -232. -263. -291 3/14/2005 5/13/2005
13, 20. -9. -40 -70. -10 -132 -162. -193. -223. -254. -285. -313 3/16/2005 4/21/2005
13, 5. -24 -55 -85. -11 -147 -177. -208. -238. -269. -300. -328 3/16/2005 4/6/2005
13, 29. 15. -15 -45. -76 -107 -137. -168. -198. -229. -260. -288 3/18/2005 5/16/2005
13, 0. -29 -60 -90. -12 -152 -182. -213. -243. -274. -305. -333 3/21/2005 4/1/2005
13, 29. 16. -14 -44. -75 -106 -136. -167. -197. -228. -259. -287 3/22/2005 5/17/2005
13, 17. -12 -43 -73. -10 -135 -165. -196. -226. -257. -288. -316 3/24/2005 4/18/2005
13, 1. -28 -59 -89. -12 -151 -181. -212. -242. -273. -304. -332 3/25/2005 4/2/2005
13, 1. -28 -59 -89. -12 -151 -181. -212. -242. -273. -304. -332 3/25/2005 4/2/2005
13, 26. -3. -34 -64. -95 -126 -156. -187. -217. -248. -279. -307 3/28/2005 4/27/2005
13, 4. -25 -56 -86. -11 -148 -178. -209. -239. -270. -301. -329 3/30/2005 4/5/2005
13, 1. -28 -59 -89. -12 -151 -181. -212. -242. -273. -304. -332 3/31/2005 4/2/2005
13, 0. -29 -60 -90. -12 -152 -182. -213. -243. -274. -305. -333 3/31/2005 4/1/2005
13, 7. -22 -53 -83. -11 -145 -175. -206. -236. -267. -298. -326 3/31/2005 4/8/2005
13, 5. -25 -56 -86. -11 -148 -178. -209. -239. -270. -301. -329 3/31/2005 4/5/2005
13, 4. -25 -56 -86. -11 -148 -178. -209. -239. -270. -301. -329 3/31/2005 4/5/2005
13, 29. 5. -25 -55. -86 -117 -147. -178. -208. -239. -270. -298 3/31/2005 5/6/2005
13, 2. -27 -58 -88. -11 -150 -180. -211. -241. -272. -303. -331 4/1/2005 4/3/2005
13, 2. -26 -57 -87. -11 -149 -179. -210. -240. -271. -302. -330 4/1/2005 4/4/2005
13, 2. -26 -57 -87. -11 -149 -179. -210. -240. -271. -302. -330 4/1/2005 4/4/2005
13, 16. -12 -43 -73. -10 -135 -165. -196. -226. -257. -288. -316 4/2/2005 4/18/2005
13, 1. -26 -57 -87. -11 -149 -179. -210. -240. -271. -302. -330 4/2/2005 4/4/2005
13, 2. -25 -56 -86. -11 -148 -178. -209. -239. -270. -301. -329 4/2/2005 4/5/2005
13, 6. -21 -52 -82. -11 -144 -174. -205. -235. -266. -297. -325 4/2/2005 4/9/2005
13, 1. -27 -58 -88. -11 -150 -180. -211. -241. -272. -303. -331 4/2/2005 4/3/2005
13, 7. -19 -50 -80. -11 -142 -172. -203. -233. -264. -295. -323 4/4/2005 4/11/2005
13, 1. -24 -55 -85. -11 -147 -177. -208. -238. -269. -300. -328 4/4/2005 4/6/2005
13, 0. -25 -56 -86. -11 -148 -178. -209. -239. -270. -301. -329 4/4/2005 4/5/2005
13, 0. -25 -56 -86. -11 -148 -178. -209. -239. -270. -301. -329 4/4/2005 4/5/2005
13, 2. -24 -55 -85. -11 -147 -177. -208. -238. -269. -300. -328 4/4/2005 4/6/2005
13, 2. -23 -54 -84. -11 -146 -176. -207. -237. -268. -299. -327 4/4/2005 4/7/2005
13, 0. -24 -55 -85. -11 -147 -177. -208. -238. -269. -300. -328 4/5/2005 4/6/2005
13, 0. -24 -55 -85. -11 -147 -177. -208. -238. -269. -300. -328 4/5/2005 4/6/2005
13, 9. -16 -47 -77. -10 -139 -169. -200. -230. -261. -292. -320 4/5/2005 4/14/2005
13, 1. -23 -54 -84. -11 -146 -176. -207. -237. -268. -299. -327 4/5/2005 4/7/2005
13, 0. -24 -55 -85. -11 -147 -177. -208. -238. -269. -300. -328 4/5/2005 4/6/2005
13, 0. -23 -54 -84. -11 -146 -176. -207. -237. -268. -299. -327 4/6/2005 4/7/2005
13, 15. -8. -39 -69. -10 -131 -161. -192. -222. -253. -284. -312 4/6/2005 4/22/2005
13, 8. -15 -46 -76. -10 -138 -168. -199. -229. -260. -291. -319 4/6/2005 4/15/2005
13, 0. -23 -54 -84. -11 -146 -176. -207. -237. -268. -299. -327 4/6/2005 4/7/2005
13, 1. -22 -53 -83. -11 -145 -175. -206. -236. -267. -298. -326 4/6/2005 4/8/2005
13, 1. -21 -52 -82. -11 -144 -174. -205. -235. -266. -297. -325 4/7/2005 4/9/2005

(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},4,1),dateserial({?fiscalyear},4+1,1)-1) else

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},4,1),{@ICU dis date time}) else

if {@ICU admit date time} >= dateserial({?fiscalyear}, 4,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) else

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

I made April through to December {?fiscalyear}
and Jan through March 31 ({?fiscalyear}+1

Above is April code

Below is a March code

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

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

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

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

In the Record Selector formula I have the following

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

Hope this helps you help me, I really do appreciate your help

 

[lbass]

Try changing the second and third sections as follows--I think it is picking up dates outside of the month because of the two > signs:

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

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

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

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

Also, if you mean to include both the start and end time between the two datetimes, I think you should add a 1 after each datediff formula in order to add one second as I did above.

Sorry about this--I didn't test it (still haven't, but I think this should fix the problem).

-LB