Extracting date-series data from EA (not date-time)

[ChB]

I'd like to have some time-series graphs such as "requirement count over time" and "Use Case count over time".

I think the built-in time-series dashboard chart can't be run retrospectively: I think it only generates a data point when it is run manually.  I want a graph that starts from a date in the past.

I'm using EA 12.1 with a local .eap file (jet).

I'm thinking I'll write a simple SQL Query to extract the data and then chart that in Excel.

An SQL Query like the following doesn't work because the CreatedDate field is a date & time.
select t_object.CreatedDate,
       count(*)
  from t_object
 where t_object.Stereotype like '*requirement*'
 group by t_object.CreatedDate

How do I get my query to return (and group by) the date part only (not a date-time)?

I assume I'd manually run the SQL Query in EA and then copy the data to Excel.

[Geert Bellekens]

You should look into the date functions of MS-Access SQL syntax to group the data by date (or week, or month)

Or otherwise you could also get all the data out and leave the aggregation to Excel.

Geert

[Helmut Ortmann]

Hi,

this forum isn't the best to discuss SQL features. May be there is a possibility. Google could help you. I could imagine defining time slots and group by them. If it's feasible with your EA Database,Access???, I don't know.

If I couldn't find an SQL solution I would cyclically run the SQL and store the results, let say every hour/day or whatever in a database.

Regards,

Helmut

[ChB]

Partially solved:

A bit more Google-foo and I've worked out how to get just the date part of the date-time field.  Thanks Geert for the hint that refined my Googling:

select count(*) as Count,
       format(t_object.CreatedDate,"short date") as Creation_Date
  from t_object
 where t_object.Stereotype like '*requirement*'
 group by format(t_object.CreatedDate,"short date")
 order by format(t_object.CreatedDate,"short date")

I then copy those results to Excel for charting.

I shame I can't use EA's built in time-series charting gadget.