I have an audit table that tracks the steps in a process and I need to track the time for each step. It used to be stored in MS SQL Server but is now stored in PostgreSQL. I have the query from SQL server and have not been successful at converting it. Here is the MS SQL working example: http://www.sqlfiddle.com/#!18/1b423/1
Here are the rules:
The steps are not required to be sequential, so step 1 can happen
after step 5.The records for an order are not stored sequentially by step or order, but rather are intermixed with other orders based upon the Time Entered.
The sample data being ordered by Order Number then New is NOT normal and cannot be depended upon.
Each step can be repeated for any given order, if repeated for an order, then sum the times by step.
The starting step record is always null in the Old column
Starting step is calculated as the time difference between
when it is in the New column and when it is the value in the Old column for a given order.For the steps that the order never came out of, the time is computed up to the present moment
A step can be repeated many times and am only looking for the total time spent in each step.
I cannot get the date difference to sum or handle the null old status value for the first step. I get various forms of this error when running the following sql.
ERROR: function isnull(timestamp without time zone, timestamp with time zone) does not exist LINE 4: sum(a1.timeentered - isnull(a2.timeentered,now())) as "tota... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT
a1.ordernumber,
a1."new" AS "Step",
sum(a1.timeentered - isnull(a2.timeentered,now())) as "total time"
FROM
audittrail AS a1
LEFT JOIN
audittrail AS a2
ON
a1."new" = a2."old" AND
a1.ordernumber = a2.ordernumber
GROUP BY
a1.ordernumber,
a1."new"
ORDER BY
a1.ordernumber ASC
Here is the sample data as well as a link to a sample online: http://www.sqlfiddle.com/#!17/e6fd5a
Old New Time Entered Order Number
NULL Step 1 4/30/12 10:43 1C2014A
Step 1 Step 2 5/2/12 10:17 1C2014A
Step 2 Step 3 5/2/12 10:28 1C2014A
Step 3 Step 4 5/2/12 11:14 1C2014A
Step 4 Step 5 5/2/12 11:19 1C2014A
Step 5 Step 9 5/3/12 11:23 1C2014A
NULL Step 1 5/18/12 15:49 1C2014B
Step 1 Step 2 5/21/12 9:21 1C2014B
Step 2 Step 3 5/21/12 9:34 1C2014B
Step 3 Step 4 5/21/12 10:08 1C2014B
Step 4 Step 5 5/21/12 10:09 1C2014B
Step 5 Step 6 5/21/12 16:27 1C2014B
Step 6 Step 9 5/21/12 18:07 1C2014B
NULL Step 1 6/12/12 10:28 1C2014C
Step 1 Step 2 6/13/12 8:36 1C2014C
Step 2 Step 3 6/13/12 9:05 1C2014C
Step 3 Step 4 6/13/12 10:28 1C2014C
Step 4 Step 6 6/13/12 10:50 1C2014C
Step 6 Step 8 6/13/12 12:14 1C2014C
Step 8 Step 4 6/13/12 15:13 1C2014C
Step 4 Step 5 6/13/12 15:23 1C2014C
Step 5 Step 8 6/13/12 15:30 1C2014C
Step 8 Step 9 6/18/12 14:04 1C2014C
This is the expected result:
| OrderNumber | Step | Total Time in Step (seconds) |
|-------------|--------|------------------------------|
| 1C2014A | Step 1 | 171240 |
| 1C2014A | Step 2 | 660 |
| 1C2014A | Step 3 | 2760 |
| 1C2014A | Step 4 | 300 |
| 1C2014A | Step 5 | 86640 |
| 1C2014A | Step 9 | 324902599 |
| 1C2014B | Step 1 | 235920 |
| 1C2014B | Step 2 | 780 |
| 1C2014B | Step 3 | 2040 |
| 1C2014B | Step 4 | 60 |
| 1C2014B | Step 5 | 22680 |
| 1C2014B | Step 6 | 6000 |
| 1C2014B | Step 9 | 323323159 |
| 1C2014C | Step 1 | 79680 |
| 1C2014C | Step 2 | 1740 |
| 1C2014C | Step 3 | 4980 |
| 1C2014C | Step 4 | 3840 |
| 1C2014C | Step 5 | 420 |
| 1C2014C | Step 6 | 5040 |
| 1C2014C | Step 8 | 875160 |
| 1C2014C | Step 9 | 320918539 |
CodePudding user response：
This turned out to be harder than I thought. This is the full query I used. It doesn't have the subsitution for ISNULL function, but it gets most of the way there. I used the extract function from Date/Time Functions. Specifically, to get everything in seconds, I used extract(epoch from ...
SELECT
a1.ordernumber,
a1."new" AS "Step",
sum(extract(epoch from a2.timeentered) -
extract(epoch from a1.timeentered)) as total_time
FROM
audittrail AS a1
LEFT JOIN
audittrail AS a2
ON
a1.new = a2.old AND
a1.ordernumber = a2.ordernumber
GROUP BY
a1.ordernumber,
a1.new
ORDER BY a1.ordernumber ASC
which gives
| ordernumber | Step | total_time |
|---|---|---|
| 1C2014A | Step 1 | 171240 |
| 1C2014A | Step 2 | 660 |
| 1C2014A | Step 3 | 2760 |
| 1C2014A | Step 4 | 300 |
| 1C2014A | Step 5 | 86640 |
| 1C2014A | Step 9 | NULL |
| 1C2014B | Step 1 | 235920 |
| 1C2014B | Step 2 | 780 |
| 1C2014B | Step 3 | 2040 |
| 1C2014B | Step 4 | 60 |
| 1C2014B | Step 5 | 22680 |
| 1C2014B | Step 6 | 6000 |
| 1C2014B | Step 9 | NULL |
| 1C2014C | Step 1 | 79680 |
| 1C2014C | Step 2 | 1740 |
| 1C2014C | Step 3 | 4980 |
| 1C2014C | Step 4 | 3840 |
| 1C2014C | Step 5 | 420 |
| 1C2014C | Step 6 | 5040 |
| 1C2014C | Step 8 | 875160 |
| 1C2014C | Step 9 | NULL |
To me this calculation looks wrong. For me, it makes more sense (for example) for the entry for Step 3/Order 1C2014A, the total_time should be 11 minutes or 660 seconds. To achieve this, swap old and new in the join and swap a1 and a2 in the sum(part(epoch....) to become
SELECT
a1.ordernumber,
a1.new AS Step,
sum(extract(epoch from a1.timeentered) -
extract(epoch from a2.timeentered)) as total_time
FROM
audittrail AS a1
LEFT JOIN
audittrail AS a2
ON
a1.old = a2.new AND
a1.ordernumber = a2.ordernumber
GROUP BY
a1.ordernumber,
a1.new
ORDER BY a1.ordernumber ASC
CodePudding user response：
Just replace isnull and datediff with equivalent PostgreSQL expressions in the second query line.
select a1.OrderNumber as "OrderNumber", a1.New as "Step",
extract('epoch' from
sum(coalesce(a2.TimeEntered, now()) - a1.TimeEntered))::integer
as "Total Time in Step (seconds)"
from AuditTrail a1
left join AuditTrail a2
on a1.New = a2.Old
and a1.OrderNumber = a2.OrderNumber
group by a1.OrderNumber, a1.New
order by a1.OrderNumber;