To find a Date Difference in Minutes in Data Collection

[former_member229176]

Hello Experts.

In onre of the requirements, I need to find out Date difference between two datetimes entered as DC.

DC Parameter name: START_TIME , Type: Text , Mask: ##:## ##/##/#### , Value: 10:12 05/31/2016

DC Parameter name: END_TIME , Type: Text , Mask: ##:## ##/##/#### , Value: 11:11 05/31/2016

DC Parameter name: TOTAL_TIME , Type: Formula

I have swritten script as: (I have hardcoded ENDTIME as just wantes to see whetehr query works or not)

queryStr = "select datediff(mi , (substring(\'" + START_TIME + "\' , 13 , 4) + \'-\' + substring(\'" + START_TIME + "\' , 7 , 2) + \'-\' + substring(\'" + START_TIME + "\' , 10 , 2) +\'T\' + substring(\'" + START_TIME + "\', 1 , 5) + \':00\' ) , '2016-05-31T11:11:00') AS MINS";

result = executeQuery(queryStr);

TotalTime = result.get("MINS");

exit(TotalTime);

But when I perfrom Data Collect, it gives me an error sayong aht START_TIME is not defined.

What am I doing wrong here? Or is it that formula/scripts will take only numeric fields even if I am passing it as parameter?

Is there any other method to achieve the same?

Regards,

Soham

[stuarta1 · Accepted solution]

Soham

Unfortunately, parameters only work for numeric data, not text.

Stuart

[former_member229176 · Accepted solution]

That is not good

Do you know any other method wherein I can calculate Date difference as shown in the question?

Regards,

Soham

[stuarta1 · Accepted solution]

Soham

Without custom development, you could store the parameters first in one Data Collection, then use a formula in another data collection to calculate the difference from those parameters just stored in the database. Not ideal I know.

[former_member229176 · Accepted solution]

Thnaks Stuat.

But I guess as I'm a newbie to ME, I dotn quite get the approach. Can you please explain how can I achieve that?

Suppose I'm storing START_TIME and END_TIME (Both text fields) in DC Group: DC1

In DC Group DC2, I have TOTAL_TIME (Formula), WHat should go in my formula?

Regards,

Soham

[stuarta1 · Accepted solution]

You need to query the database to get the start and end times for the DC1 for the current SFC, convert them from strings and subtract. A single query could do this most efficiently. This isn't ideal by any means - the user can cancel a data collection, so you may have to handle missing data too.

[former_member229176 · Accepted solution]

Hi Stuart,

For testing purpose, I'm passing following in Formula script:

queryStr = "select TOP 1 ACTUAL from PARAMETRIC_MEASURE where PARA_CONTEXT_GBO =\'";

queryStr = queryStr + SFC_BO+"\' AND MEASURE_NAME = \'START_TIME \' order by PARAMETRIC_MEASURE.TEST_DATE_TIME desc ";

result = executeQuery(queryStr);

actual= result.get("ACTUAL");

exit(actual);

But it is giving me following error:

A mathematical error has occurred; check inputs into formula for valid values (Message 15249)

Can you see what's wrong with the query?

Regards,

Soham

[sergiy_katerinich · Accepted solution]

I would try to print out the value of "actual" before exit, or add some logic to check if "actual" ever returns the expected data.

[stuarta1 · Accepted solution]

Soham

exit() only works with a numeric value and your measure START_TIME is a string, so that won't work.

Stuart

[former_member229176 · Accepted solution]

Thaks Stuart.

Yes, exit() only works with Numeric

I was able to find a datetime difference in Minutes between two datetimes START_TIME and END_TIME.

DC Group: TEST1  , DC Parameter: START_TIME , END_TIME

DC Group: TEST2 , DC Parameter: TOTAL_TIME (Type: Formula)

Script looks like this:

queryStr = "SELECT datediff(mi,(substring(S.ACTUAL , 13 , 4) + \'-\' + substring(S.ACTUAL , 7 , 2) + \'-\' + substring(S.ACTUAL , 10 , 2)+\'T\' + substring(S.ACTUAL , 1 , 5) + \':00\' ) , ( substring(E.ACTUAL , 13 , 4) + \'-\' + substring(E.ACTUAL , 7 , 2) + \'-\' + substring(E.ACTUAL , 10 , 2)+\'T\' + substring(E.ACTUAL , 1 , 5) + \':00\' )) as MINS from (SELECT TOP 1 ACTUAL from PARAMETRIC_MEASURE where PARA_CONTEXT_GBO = \'";

queryStr = queryStr + SFC_BO + "\' AND MEASURE_NAME = \'START_TIME\')  S , (SELECT TOP 1 ACTUAL from PARAMETRIC_MEASURE where PARA_CONTEXT_GBO = \'";

queryStr = queryStr + SFC_BO + "\' AND MEASURE_NAME = \'END_TIME\')  E";

result = executeQuery(queryStr);

mins = result.get("MINS");

exit(mins);

Once I open Data Ollect TEST2 in POD and save it, I am able to see difference calculated and stored in TOTAL_TIME. I can look at it in Data Collection Results Report.

Thaks a lot for the help. Your inputs made it possible

Regards,

Soham

[stuarta1 · Accepted solution]

Glad you got it working!

Don't forget to allow for no start_time/end_time existing, and if you have more than one of each, to sort them in a predictable manner before getting TOP 1.