I am developing a Crystal report and I need to identify records where a single-medication-dosage was dispensed 5 or more times in one day.
There are many issues related to accomplishing this but the one I wanted to ask you about is this:
The field that contains the "single medication dosage" is a VARCHAR.
- This field can contain values like 120 MG, 3.5 MG, etc.
The field that indicates the amount dispensed is also a VARCHAR and it can contain values like: 40 MG, etc.
The discrete amount dispensed will not always equal the single medication dosage. Maybe the single medication dosage is 120 MG... well, the amount dispensed each time in one day, may only be 40 MG.
* What I was hoping to do was:
1) Convert these fields to a number but I don't see how this is possible since it would require stripping the free text portion, "MG" for example, and (since there can be decimals in the actual number itself) you don't want to strip the decimal.
This is my question. How can I work with these VARCHAR fields as numbers and remove the MG free text but maintain the decimals?
BACKGROUND INFO IF NEEDED:
If I was able to convert to a number, then I wanted to sum the dispenses per day, divide by 5 and compare the result to the single dosage strength. If equal to or greater, then the record should be displayed.
Example:
Strength: 120 mg
Dispensed discrete dosage for Feb 20, 2014
9am: 40 MG
12pm: 40 MG
3pm: 120 MG
6pm: 40 MG
The result: For this day, a total of 240 MG was dispensed. 240 divided by 5 = 48 (which is less than the strength of 120) so we know there were not 5 or more discrete dosages dispensed. This record would not be displayed in the report.
Thank you
- Mike
IP Logged
