Hi All,

I have a question about how to perform the following in llblgen - this is probably a simple quesiton but cannot see how to approach this.

I have a table that contains a start datetime and an end datetime. I would like to calculate the sum of the differences of these values. The end result would be a value of duration.

How can this be achieved?

Thanks!

Would you please write down the SQL query you want to produce.

select sum(datediff(MINUTE, start_timestamp, end_timestamp)) from cuda_run group by datediff(MINUTE, start_timestamp, end_timestamp)

Here is an approximate code (assuming you are using Adapter):

EntityField2 dateDiffField = new EntityField2("GlobalDiff",
    new DbFunctionCall("DATEDIFF(MINUTE, {0}, {1})",
        new object[] { OrderFields.OrderDate, OrderFields.ShippedDate}));

int globalDiff = 0;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    object result = adapter.GetScalar(dateDiffField, AggregateFunction.Sum);
    globalDiff = (result == null) ? 0 : (int) result;               
} 

daelmo wrote:

Here is an approximate code (assuming you are using Adapter):

EntityField2 dateDiffField = new EntityField2("GlobalDiff",
    new DbFunctionCall("DATEDIFF(MINUTE, {0}, {1})",
        new object[] { OrderFields.OrderDate, OrderFields.ShippedDate}));

int globalDiff = 0;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    object result = adapter.GetScalar(dateDiffField, AggregateFunction.Sum);
    globalDiff = (result == null) ? 0 : (int) result;               
} 

This looks good - what I was after, however, am actually using Self Service. How do you get the field index for the newly added field "dateDiffField"?

Here is the code in SelfServicing:

            var field = new EntityField("CalculatedField", new DbFunctionCall("DATEDIFF(MINUTE, {0},{1})", new Object[] { OrderFields.OrderDate, OrderFields.ShippedDate }));

            var fields = new ResultsetFields(1);
            fields.DefineField(field, 0, AggregateFunction.Sum);

            var groupBy = new GroupByCollection(field);

            var results = new DataTable();
            var dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, groupBy, null, 0, 0);

This returns a dataTable of multiple rows and a single column/field.