Rounding giving wrong data.

[Dandas]

I am trying to get the :mm: of a time field (which is hh:mm:ss)to round up or down to the nearest half hour depending on which period it is in. So I need the minutes to be "00" if it is between 08:01 and 08:30 or "30" if between 08:31 and 09:00.

I am using the following formula

if (TrimRight ({@StartTime} [4 to 5])) >= "1" and
(TrimRight ({@StartTime} [4 to 5])) < &quot;31&quot; Then
&quot;30&quot; else
&quot;00&quot;

Now it almost works but it is doing something a little wierd that I can't work out. Anything between &quot;01&quot; and &quot;09&quot; brings up a value of &quot;00&quot; when it should be &quot;30&quot;

Any suggestions? I'm stumped.

 

[Ngolem]

I imagine you are converting the time field to a string in {@StartTime}

don't do that....use the time filed directly in a formula like the following

@RoundingTime
WhilePrintingRecords;
timeVar tempTime := {table.timefield};

If minute(tempTime) >= 30 then
tempTime := time(Hour(tempTime),30,0)
else
tempTime := time(Hour(tempTime),0,0);

totext(temptime,&quot;hh:mm:ss&quot;

that should work fine



Jim Broadbent

 

[Dandas]

So are you saying to use a time stamp &quot;field&quot; instead of a &quot;calculated time field&quot;?

Where you wrote

timeVar tempTime := {table.timefield};
I tried to insert the following timestamp field {VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP}

in place of {table.timefield}; and I got an error message saying that a time was expected here. Is replacing this field what I was supposed to do?

Sorry to be a nuisance. Still trying to learn

 

[Ngolem]

yes...there is no need for the intermediate step as you can see from my formula....Unless of course you are using this formula elsewhere for something and even then....

And don't worry about asking questions... You are not a nuisance Jim Broadbent

 

[Ngolem]

Also I read your other thread...since the result of my formula I gave you is text there is no modification required for contatenation (sp?)

In fact you could combine the 2 formulas into one

{@RoundedPeriod}+{VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE}

@RoundingTime
WhilePrintingRecords;
timeVar tempTime := {table.timefield};

If minute(tempTime) >= 30 then
tempTime := time(Hour(tempTime),30,0)
else
tempTime := time(Hour(tempTime),0,0);

totext(temptime,&quot;hh:mm:ss&quot + &quot; &quot; +
{VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE};

If you don't want the seconds in your formula make it

ToText(tempTime,&quot;hh:mm&quot

Jim Broadbent

 

[Dandas]

OK.

I have copied your formula and replaced &quot;{table.timefield}&quot; with a time stamp field and it keeps giving me an error message saying that a &quot;a time is required here&quot; and the cursor moves to the begining of the time stamp field.

I have cheked the data base schema and the field I am using which happens to be called {VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP} is definately a time stamp.

Do you know where I am going wrong?

Thanks
Daniel

 

[Ngolem]

hmmm....when you Browse the field in Crystal what does it say about the field....

Simplest way to do this is to place the field in the design report...right click on the field and select Brouse Datefield or something like that....it will show somme (not all) of the data and describe the datatype Jim Broadbent

 

[Dandas]

OK.

I did that and it says

Type: String
Length: 22

and the format is yyyy/mm/dd hh:mm:ss.msms

So from your response earlier i am guessing I don't want this to be a string. I just had an attempt at converting it from a string but it was a completely wild guess that got me nowhere. Oh well it was worth the try.

What is the best way to convert it (that is if I am on the right track).

Also as a matter of interest what is YOUR definition of a string? If its not too much to ask.

 

[Naith]

Hey Dandas & J.B,

Dandas, just for the record a TimeStamp field is not usually a literal date/time field. It's normally a string which is populated with date/time data (plus additional information if required - like split seconds, in some cases). Some databases actually support a datatype of TimeStamp. Timestamps which are either String or TimeStamp datatypes will give you a 'Time Expected Here' error if you try to assign them to a Time variable.

In the case of your TimeStamp field, you would have to split the field up with functions like mid/trim/etc, (like you've done in your initial post) to derive the actual time data of the field.

Going back to your first post, I'm wondering what the {@StartTime} formula does. Is it a conversion formula, or what? Can you paste it up here?

Additionally, I'm wondering about:

&quot;So I need the minutes to be &quot;00&quot; if it is between 08:01 and 08:30 or &quot;30&quot; if between 08:31 and 09:00.&quot;
...
&quot;Anything between &quot;01&quot; and &quot;09&quot; brings up a value of &quot;00&quot; when it should be &quot;30&quot; &quot;

Your first statement suggests that you want to round down, but your formula appears to round up.

Aaaanyway, I think that {@StartTime} is probably a string of HH:MI:SS; in which case, you want to have the first line of your formula reading:

if (TrimRight ({@StartTime} [4 to 5])) >= &quot;01&quot; and

not >= &quot;1&quot;. [4 to 5] is a length 2 search. 'kay?

Naith

 

[Ngolem]

Now that we are clear on the Time Stamp Datatype (thanks for your explanation Naith)

I would like to take another look at what exactly @StartTime really is....personally I still think it is an extra step though

******************

Type: String
Length: 22

and the format is yyyy/mm/dd hh:mm:ss.msms

***********************

I think you typo'd since the above is 24 characters

yyyy/mm/dd hh:mm:ss.ms

Ok let us modify my original formula....there is no need to extract the time out twice I think

Do you just want the minutes??? valued at either 00 or 30? On rereading your formula that seems to be all you want so I will modify this formaula to do this.

@RoundingTime
WhilePrintingRecords;
//extracting just the minute portion of the time stamp
StringVar tempTime :=
mid({VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP}, 15,2);

If tonumber(tempTime) >= 30 then
tempTime := &quot;30&quot;
else
tempTime := &quot;00&quot;;

totext(temptime,&quot;hh:mm:ss&quot + &quot; &quot; +
{VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE};




Jim Broadbent

 

[Dandas]

Hi Guys.

IT IS FIXED. Woohoooo. I just did what Naith said and wrote

>if (TrimRight ({@StartTime} [4 to 5])) >= &quot;01&quot; and
>not >= &quot;1&quot;. [4 to 5] is a length 2 search. 'kay?&quot;

But incase you still wanted to know I will answer questions you both had.

>Additionally, I'm wondering about:

>&quot;So I need the minutes to be &quot;00&quot; if it is between 08:01
>and 08:30 or &quot;30&quot; if between 08:31 and 09:00.&quot;
>...
>&quot;Anything between &quot;01&quot; and &quot;09&quot; brings up a value of &quot;00&quot; >when it should be &quot;30&quot; &quot;

>Your first statement suggests that you want to round down, >but your formula appears to round up.

Ah yes you are right, I meant to write that in reverse. In short I want to capture everything that happens in a half hour period by half hour.

>Going back to your first post, I'm wondering what the >{@StartTime} formula does. Is it a conversion formula, or >what? Can you paste it up here?&quot;

Yes. it is just a conversion formula, it trims the time stamp field to time only instead of date and time.

>I think you typo'd since the above is 24 characters

Yes you are right, I'm not going too well here.....

>Do you just want the minutes??? valued at either 00 or 30?

Yes, that is all. The final result I want to achieve is to round the time to the nearest half hour and then concatenate it with a transaction code so the finished product needs to look like hh:mm:ssTrn e.g. 08:30101. I have achieved this now although it is over a few formulas. 1 to break up the time and round it, and 1 to concatenate it.

I tried the following formula and it worked up until the totext(temptime,&quot;hh:mm:ss&quot part. When it gets to there it says that it does not look like a part of the formula.

>@RoundingTime
>WhilePrintingRecords;
>//extracting just the minute portion of the time stamp
>StringVar tempTime :=
> mid({VW_EVT_AGENT_ACTIVITY.AA_TIME_STAMP}, 15,2);

>If tonumber(tempTime) >= 30 then
> tempTime := &quot;30&quot;
>else
> tempTime := &quot;00&quot;;

>totext(temptime,&quot;hh:mm:ss&quot + &quot; &quot; +
> {VW_CFG_TRANSACTION_CODE.TRANSACTION_CODE};


But it works now and I really appreciate your help guys.

YOU BOTH ROCK !!!

 

[Dandas]

OK.
Here is my new dilema.

When I use this formula to round up the hh: of {@StartTime} it drops of the &quot;0&quot; if the hh: is before 10am and the :mm: is &quot;00&quot;. So instead of displaying 09:00 it comes back 9:00 and I need 09:00.


if (TrimRight ({@StartTime} [4 to 5])) >= &quot;31&quot; then
totext(val(Left({@StartTime},0))+1,0)
else
Left({@StartTime},2)

I have had a little play with it and will keep playing with ideas but in the mean time if you come up with anything I would really love your help.

Thanks again.

 

[Naith]

I'm surprised you get 9 at all with &quot;Left({@StartTime},0)&quot;

Try and work it out with:

if (TrimRight ({@Time1} [4 to 5])) >= &quot;31&quot; then
totext(val(Left({@Time1},2))+1,&quot;00&quot
else
Left({@Time1},2)

Good luck Dandas,

Naith

 

[Ngolem]

Dandas -

I don't think I would ever &quot;round up&quot; a time.

Why? ... Because it causes soooo many problems.

When you &quot;round down&quot; minutes....you stay in the same hour that the time occurs. But when you &quot;round up&quot; you potentially start a chain of events that potentially could wind up with your value being in another year....(if the time stamp showed 11:31pm on Dec 31 of a year.

this does not happen AT ALL when rounding down. Jim Broadbent

 

[Dandas]

Are there any ways Jim that you would suggest reporting on something by half hour intervals?

This report is for a call centre and it is measuring what calls are coming in by half hour so that skilled staff can be rostered accordingly, and so that we can try to stop the call drivers. Each time an agent takes a call they punch a code into the phone which records a transaction code. Somehow I need to be able to roll this info up into half hour periods.

I would love to find out a different way but I just don't see one.

Thanks again.
Oh and if you come up with any other ideas for the hh: dropping the 0 I would love to hear them

 

[Dandas]

I found an alternative that has given me a new problem, but it should fix everything.

I found a calculated field that has the period the call was logged in and it is in hh:mm which is perfect. It is always either 08:00 or 08:30 etc, just the way I need it.

This field is derived from another field which breaks the day up into 48 periods (48 x 30 min periods = 1 day) so period 1 is 00:30 period 2 is 01:00.

My new drama is when I concatenate the period with the transaction code by using

totext({@Time period}) + totext({VW_PER_TRANSACTION_CODE.TC_TRANSACTION_CODE})

it concatenates the period with the period number not in hh:mm format. so it looks like 16101 instead of 08:00101.

I'm sure this will be an easy one for you. I'm slowly learning though (thank goodness)

 

[Dandas]

Alright !!!!!

I worked this one out my self, I managed to trace the field back before it was converted to a string and then I concatenated it.

Thanks for all your help.

I am sweet now !!!