0

I have a task at hand, where i need to create a report with defect id, time the defect was in a particular status, In HP QC SPS.

The issue at hand is (for example) , if the status changed from 'new' to 'open in analysis', then to 'under investigation' and then changed it back to 'open in analysis'. the report should capture the time spend under each status. which means twice the status was 'open in analysis', so that time needs to be added and captured.

how can i achieve this.

can some one help me with a SQL query for the same?

GlennV
  • 3,471
  • 4
  • 26
  • 39
GDLK
  • 1

1 Answers1

0

I don't know what "HP QC SPS" means - should I? Does it understand Oracle SQL?

If it does: if your table has three columns, defect_id, ts (timestamp) and status, you need to compute the time in each status for each occurrence (even for the same defect_id) by using the lead() analytic function, partitioning by defect_id and ordering by ts; and then you need to group by defect_id and status and SUM(<these differences>).

If you want an illustration, I want one too. You provide input data, I show you how this works. While you are at it, please explain what happens with a status that is "current" (the last entry for a defect_id - unless the last status is resolved or closed).