First Time poster...

I have a procedure that looks at admit_date and discharge_date along with account number and medical record number. The medical record number is set to each patient. One per patient. The account number is for each visit to hospital. I want to see each record if the admit date was less than 7 days from the last discharge date. This report is obviously for patients who have been patients before. So we need to exclude thos records that don't have more than one account no I'm guessing.

I just went to a new class and I'm not sure how to do this.

I just came up with this real quick...see if you can play with it.

DEFINE FILE CAR
TODAY/MDYY = '&DATEMDYY';
CHECKOUT/MDYY = TODAY - 7;
CHECKIN/MDYY = '07182006';
THIS/A3 = IF CHECKIN GT CHECKOUT THEN 'YES' ELSE 'NO';
END
TABLE FILE CAR
PRINT CAR
TODAY CHECKOUT CHECKIN THIS
-*IF THIS EQ 'YES'
-*IF THIS EQ 'NO'
END

I want only records where checkout looks back 7 days to see if it had a different checkin. Not seven days from the date entered. So how can I tell it to look for a different account#? I don't want to see where it checks it's own checkin date from the checkout date of the same account#. Sorry, I'm not explaining this very well.

You can check whether a patient has more than one account number by doing the following:

TABLE FILE filename
SUM
CNT.Acc_No
BY Patient_No
WHERE TOTAL CNT.Acc_No GT 1;
END

This will check if there is more than one account number for each patient and only include those for which there is.

To check the difference between the checkout and checkin dates, I would think this would work:

TABLE FILE filename
SUM
COMPUTE CHECKDATE/A3=IF ((MAX.Checkin - MAX.Checkout) GT 0) AND ((MAX.Checkin - MAX.Checkout) LT 8) THEN 'YES' ELSE 'NO';
BY Patient_No
END

This will check the highest checkout date against the highest checkin date, and sees if they are within 7 days of each other. Note your dates will need to be Smartdates for this to work. So for your code try:

TABLE FILE filename
SUM
CNT.Acc_No
MAX.CHECKOUT
MAX.CHECKIN
COMPUTE CHECKDATE/A3=IF ((MAX.Checkin - MAX.Checkout) GT 0) AND ((MAX.Checkin - MAX.Checkout) LT 8) THEN 'YES' ELSE 'NO';
BY Patient_No
WHERE TOTAL CNT.Acc_No GT 1;
WHERE TOTAL CHECKDATE EQ 'YES';
END


If however you wanted to check all checkin dates against the previous checkout date, rather that just their last checkin date, you need to use the LAST command to compare them.

Regards

Tewy

Thanks you I know were getting closer. I'm sure It has to be how I'm explaining myself right.

MedRecNO AccntNo FirstName CheckinDate CheckoutDate print

5 - 200 - John - 6/20/2006 - 6/23/2006 - no
10 - 300 - Matthew - 6/22/2006 - 6/30/2006 - no
5 - 400 - John - 7/20/2006 - 7/23/2006 - no
20 - 500 - James - 7/21/2006 - 7/21/2006 - no
25 - 600 - Matthew - 7/3/2006 - 7/25/2006 - yes


Print only those medrecno patients who have returned if thier last checkout date was within 7 days. Even if you were already counted once.

I hope this helps. Thanks, all

Here's another option:

TABLE FILE TABLENAME
PRINT
MEDRECNO
ACCOUNTNO
ADMIT_DATE
DISCHARGE_DATE
BY MEDRECNO NOPRINT
BY HIGHEST DISCHARGE_DATE NOPRINT
ON TABLE HOLD AS DATASORT
END

DEFINE FILE DATASORT
PULL/A1 = IF MEDRECNO EQ LAST MEDRECNO AND
YMD(ADMIT_DATE, LAST DISCHARGE_DATE) LT 7
THEN 'Y' ELSE 'N';
END

TABLE FILE DATASORT
PRINT
MEDRECNO
ACCOUNTNO
ADMIT_DATE
DISCHARGE_DATE
WHERE PULL EQ 'Y'
ON TABLE PCHOLD FORMAT EXL2K
END

I'm liking Glenda's but I might try it this way. I'm assuming that your date fields are YYMD format.

TABLE FILE TABLENAME
PRINT
DISCHARGE_DATE
COMPUTE
PULL/A1 = IF MEDRECNO EQ LAST MEDRECNO AND
((ADMIT_DATE - LAST DISCHARGE_DATE) LE 7)
THEN 'Y' ELSE 'N';
BY MEDRECNO
BY ACCOUNTNO
BY ADMIT_DATE
WHERE TOTAL PULL EQ 'Y';
ON TABLE PCHOLD FORMAT EXL2K
END

Two questions...
Should it be LE or LT 7 days?
Do you need to print the info on their previous stay, or stays, or just the one that is within the 7 days? That's more complicated.

Pipster,

I noticed you didn't sort first. Did you just exclude this step or do you know something I don't?

The COMPUTEs get evaluated after the sorting is done based on the BY fields in the TABLE request so you don't need to pre-sort. If you use DEFINEd fields you would have to sort first.

Is this right?

JOIN
BADPLPU.BADPLPU.LPACCT AND BADPLPU.BADPLPU.LPHSP# IN badplpu TO UNIQUE
CADMMIU.CADMMIU.MMACCT AND CADMMIU.CADMMIU.MMHSP# IN cadmmiu AS J3
END
JOIN
BADPLPU.BADPLPU.LPACCT AND BADPLPU.BADPLPU.LPHSP# IN badplpu TO UNIQUE
CADPPIU4.CADPPIU4.PIACCT AND CADPPIU4.CADPPIU4.PIHSP# IN CADPPIU4 AS J0
END
JOIN
CADPPIU4.CADPPIU4.PIHSP# AND CADPPIU4.CADPPIU4.PICD01 IN badplpu TO UNIQUE
BSYMMOU.BSYMMOU.MOHSP# AND BSYMMOU.BSYMMOU.MOPRO# IN BSYMMOU AS J1
END
DEFINE FILE BADPLPU
ADMO/I2=LPADT1;
ADDA/I2=LPADT2;
ADYR/I2=LPADT3;
ADCEN/I2=LPCEN1;
ADMITDT_A/A8=EDIT(ADCEN) || EDIT(ADYR) || EDIT(ADMO) || EDIT(ADDA);
ADMITDT_I/I8YYMD=EDIT(ADMITDT_A);
ADMIT_DATE/MDYY=ADMITDT_I;
ADMIT_MO_DESC/Mtr=ADMITDT_I;
DISCMDY_I/I6=LPLDD;
DISCMDY_A/A6=EDIT(DISCMDY_I);
DISCMO/A2=EDIT(DISCMDY_A,'99$$$$');
DISCDA/A2=EDIT(DISCMDY_A,'$$99$$');
DISCYR/A2=EDIT(DISCMDY_A,'$$$$99');
DISCCEN_I/I2=LPCEN2;
DISCEN/A2=EDIT(DISCCEN_I);
DISCDT_A/A8=DISCEN || DISCYR || DISCMO || DISCDA;
DISCDT_I/I8YYMD=EDIT(DISCDT_A);
DISCHARGE_DATE/MDYY=DISCDT_I;
DISCHARGE_MO_DESC/Mtr=DISCDT_I;
CURR_DATE/MDYY='&DATEMDYY';
TOMORROW/MDYY=CURR_DATE + 1;
DISCHARGE_STATUS/A3=IF DISCHARGE_DATE IS 0 THEN 'No' ELSE 'Yes';
LENGTH_STAY/I3=IF DISCHARGE_DATE IS 0 THEN (CURR_DATE - ADMIT_DATE) ELSE (DISCHARGE_DATE - ADMIT_DATE);
MIN_HOURS/I2=MMMNH1;
MAX_HOURS/I2=MMMXH1;
MIN_DAYS/I2=MMMND1;
MAX_DAYS/I2=MMMXD1;
MAX_DATE/MDYY=ADMIT_DATE + MAX_DAYS;
60DAYSBACK/MDYY=CURR_DATE - 120;
LPROOM_I/I4=LPROOM;
LPROOM_A/A4=EDIT(LPROOM_I);
END
TABLE FILE BADPLPU
PRINT
LPMRC#
ACCOUNTNO
ADMIT_DATE
DISCHARGE_DATE
BY LPMRC# NOPRINT
BY HIGHEST DISCHARGE_DATE NOPRINT
ON TABLE HOLD AS DATASORT
END
TABLE FILE DATASORT
PRINT
LPMRC#
ACCOUNTNO
ADMIT_DATE
DISCHARGE_DATE
WHERE PULL EQ 'Y'
ON TABLE PCHOLD FORMAT EXL2K
END
DEFINE FILE DATASORT
PULL/A1 = IF LPMRC# EQ LAST LPMRC# AND
YMD(ADMIT_DATE, LAST DISCHARGE_DATE) LT 7
THEN 'Y' ELSE 'N';
END
TABLE FILE BADPLPU
BY
LPMRC# AS 'MedRecNo'
BY
ADMIT_DATE AS 'Admit ,Date'
BY
DISCHARGE_DATE AS 'Discharge ,Date'
BY
LPPFNM AS 'First Name'
BY
LPPLNM AS 'Last Name'
BY
LPACCT AS 'Acct #'
BY
LPDIAG AS 'Admitting Diagnosis'
BY
MMPWD AS 'Working Diagnosis'
BY
PISEQ AS 'Final Primary, Diagnosis SEQ No'
BY
ADMIT_DATE AS 'Admit ,Date'
BY
PICD01 AS 'Diagnosis Code'
BY
MODE30 AS 'Final Diagnosis Desc'
WHERE LPHSP# EQ 1;
END

quote:

60DAYSBACK/MDYY=CURR_DATE - 120;

Well as in correct coding? I noticed the above in your code - you call it 60daysback, but subtract 120.

Does your data source store the date as zero when no entry? DB2 defaults to some strange date and I'm not familiar with oracle if that is your data source. Are your admit dates stored as numeric year, month and day in separate fields?

Do you get output?

Move:

DEFINE FILE DATASORT
PULL/A1 = IF LPMRC# EQ LAST LPMRC# AND
YMD(ADMIT_DATE, LAST DISCHARGE_DATE) LT 7
THEN 'Y' ELSE 'N';
END

Before TABLE FILE DATASORT

That will make the code do what you want through ON TABLE PCHOLD FORMAT EXL2K.

I'm not sure what you are trying to do after that point.

Disregard the 60days back - I commented it out.

I changed the Define to the proper place. (I fat copied it to the wrong place.)

I did get output before regardless. It was wrong... remember I new to WebFocus and it syntax. I can't develop with the painter because I'm getting an error parsing the table and N.Y. is aware of this problem for 7.1.3 and they are sending me a patch. In the mean time, I can only edit the procedure in text mode. That is way I'm having problems understanding this complex report layout. Thx for everyone's patients and trying to help. Thanks everyone.

Because this pulls from the AS400 (DB2) it takes a very long time to run. Thx

I can empathize. Been at it for over 12 years now and still learning. You are having such a long run partially because you are going against a relational database but not selecting on a any field. This puts a real burden on the WebFOCUS server as you are pulling a lot of data back to select against. Is there any way you can limit the records in your first table request? You are pulling it all back and sorting. Is your code posted a bit out of order here? Or did you just 'tack on' to your original. Going against a large relational database with a defined field as the selection can cause some real slow down issues.

Yes I tacked on to an exsisting procedure because a lot of the fields are still needed and so is the date conversions I do from the 400. The last post is my procedure as is. How can I limit the records? Remember I can only edit in text mode.

To limit output:
WHERE RECORDLIMIT EQ 100

To limit input:
WHERE READLIMIT EQ 100

(or what ever number you want to use)

Sorry I still can't get any results that work the way I want it to. I get me results faster but the records in my answer set is incorrect. Not sure what to do now.

Perhaps if you brought back the min.discharge and max.discharge max.admit and do some fancy comparisons on the max and min values you could limit the pool and go back to get who you want.

If min and max of the discharge was the same, then only one record. Then you could look at msx.admit against max.discharge to get the pool of accounts then join back to accounts and pull the 'last' record entered.