Cognos/Flexnet manager SQL - calculate value per time period

Question

I have been tasked with analyzing license utilization via data stored in a database controlled by Flexnet manager (flexlm). What I need to graph is the number of concurrent licenses in use for a specific period of time (high water mark). I am having some trouble doing this as I have very little experience of SQL or BI tools.

I have taken 2 tables, license_events and license_features (these have been filtered for specific users and features). I have then done a join to create a License_feature table. Sample data looks as follows:

CLIENT_PROJECT FEATURE_NAME LIC_COUNT START_TIME    EVENT_TIME DURATION 
BGTV           eclipse      1         1,422,272,438 1422278666 6,228 
BGTV           eclipse      1         1,422,443,815 1422443845 30 
BGTV           eclipse      1         1,422,615,676 1422615681 5 
BGTV           eclipse      1         1,422,631,395 1422631399 4 
BGTV           eclipse      4         1,422,631,431 1422631434 3 
BGTV           eclipse      1         1,422,631,465 1422631474 9 
BGTV           eclipse      1         1,422,631,472 1422631474 2 
BGTV           eclipse      2         1,422,632,128 1422632147 19 
BGTV           eclipse      1         1,422,632,166 1422632179 13 
BGTV           eclipse      6         1,422,632,197 1422632211 14 

What I need now is to graph something like this:

For each time (second) sum(LIC_COUNT) where start_time <= time && end_time >= time

Ideally this should give me the number of concurrent licenses checked out at a specific second. Even better would be if I could get this information for a different time period such as hours or days.

How could I go about doing this?

Answer 1

Use the GROUP BY keywords to group the SUM() together on a specific column. For example, grouping the SUM() of LIC_COUNT by each START_TIME;

SELECT START_TIME, SUM(LIC_COUNT) AS TOTAL_LIC_COUNT
FROM YOUR_TABLE
GROUP BY START_TIME

Now, to SUM() all LIC_COUNT at each increment between START_TIME and END_TIME you'll need to explicitly specify those unique values somewhere else. For example, if you created a table called UniqueTimes that contained all possible values between your earliest START_DATE and last END_DATE. Then you could do something like the following;

SELECT UniqueTime, SUM(LIC_COUNT) AS TotalLicCount
FROM YOUR_TABLE
LEFT JOIN UniqueTimes ON (UniqueTime >= START_TIME AND UniqueTime <= END_TIME)
GROUP BY UniqueTime

This should group your rows as each unique time, and show the total of all summed LIC_COUNT at each specific time.

I hope this helps.