I have a table I am trying to work with as such:

Basically, We have agents in a call centre, and whenever they change state, a record of the state, and the timestamps that the change occured, as well as the agents name is recorded into a DB. In order to work with this data in PowerBI, I need to change how it is displayed.

What i need to do is get this data into a view like follows:

So using the data in the first table (we know the end times as when that agent changes to the next status, we can use that as the end time for the previous status), Im trying to get a new table or view, presenting the data with:

• Their Name
• Start and end time of the state
• the state
• The duration in seconds they were in that state

Im having a bit of trouble working out in my head how to do this for all of the agents and data. Can anyone provide me some guidance as to where I can start?

CodePudding user response：

What i need to do is get this data into a view like follows:

sure. The order may not be exact order. Change the order use order by clause as your wish.
setup test:

begin;
create temp table call(log_Timestamp timestamp,Agent text,  State text);
insert into call values
('2022/11/10 05:00:01',     'Steve'     ,'In Call')
,('2022/11/10 06:00:01',    'Steve'     ,'Waiting')
,('2022/11/10 05:00:01',    'Jack'  ,'In Call')
,('2022/11/10 05:30:01',    'Jack'  ,'Waiting')
,('2022/11/10 07:00:01',    'Steve'     ,'In Call');
commit;

query:

CREATE VIEW call_view AS
WITH cte AS (
    SELECT
        c1.log_Timestamp AS start,
        lead(c1.log_Timestamp) OVER (PARTITION BY Agent ORDER BY log_Timestamp),
        EXTRACT(EPOCH FROM (lead(c1.log_Timestamp) OVER (PARTITION BY Agent ORDER BY log_Timestamp) - c1.log_Timestamp)),
        Agent,
        state
    FROM CALL c1 ORDER BY Agent,
    log_Timestamp
)
SELECT
    *
FROM
    cte
WHERE
    lead IS NOT NULL;