Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

A way to tell newly created cases vs edited ones


#1

Good morning.

My boss has asked to see if we can get SQL written to determine cases newly created during a time period and ones edited.

When working on the SQL - it looks like in the Cases table - when a new case is added the user_id and updated_by are the same values. When it is updated then the updated_by gets updated.

So it seems we can use - for the new cases - where user_id and updated_by are the same and for those updated - the 2 fields would have different values.

Problem is - if I add in a new case and 3 days later edit it - technically I have updated it and it is not new. The 2 fields will remain the same… If I use the case_changes table - if the user_id and updated_by are the same and there are no records in the case_changes table - then the case is new. But as soon as I edit my case (even if 3 seconds later) - a record goes into the case_changes table and th etest case will be seen as an update.

I just want to make sure there is no other way to determine a new case as my boss will need to explain the reason why it looks we create very few cases.

Thanks,

Brian


#2

Why not compare created_on and updated_on in the cases table? If they are the same, the case hasn’t been updated.


#3

those dates will have the same issue. If a new cases is added without steps and then steps are added - the adding of steps is technically an update and the updated_on will be different than the created_on.

Only way I can think to get around this is to add a check box for a New case. It would be one more thing to update and we would have to put rules around when it is unchecked…


#4

Hello Brian,

Thanks for your posting. TestRail 4.0 comes with a new dedicated report specifically added for this purpose:

Test cases that were added AND updated in the selected time frame will appear twice (in the Created and Updated sections). Would this work for you?

Regards,
Tobias


#5

Not sure right now - we needed to get 4.0 installed and play with the report and how it reports the cases. Thanks for the update.


#6

Hello Brian,

Thanks for your reply. Please let us know in case any questions come up about the new report, we are happy to help.

Regards,
Tobias


#7

I finally ran the report - it looks good (I would love to know what is used to determine the new vs updated). I do notice that the report can only be run against the test suites that the individual user created. Will there eventually be a way to run against a set of test suites, no matter who created them?

Thanks,

Brian


#8

We would like to know if any testcases were deleted. What I found out was it does not show up in the activity report. Is this a bug? We simply deleted a testcase and ran the activity summary and it says no changes found.

Domenic


#9

Hello Brian,

Thanks for your posting. TestRail uses the Updated On/By and Created By/On fields for the report (which you can also display as part of the test case tables). You can run the report against all test suites or just a subset and you can configure this on the Test Suites tab when you create the report.

I hope this helps!

Regards,
Tobias


#10

Hello Domenic,

Thanks for your posting. Deleted test cases are currently not covered by the Activity summary and the report only includes new or updated test cases. It’s planned to add support for this as well but this is currently not covered. Deleted test suites/runs/plans/milestones are already displayed in the project history on the project overview page.

Regards,
Tobias


#11

[quote=tgurock]Hello Brian,

Thanks for your posting. TestRail uses the Updated On/By and Created By/On fields for the report (which you can also display as part of the test case tables). You can run the report against all test suites or just a subset and you can configure this on the Test Suites tab when you create the report.

I hope this helps!

Regards,
Tobias[/quote]

I guess what I really need is a report that goes at the Project level. For projects a, b, c ,d, g - what are the new vs updated cases.

I will have to look at those fields in 4.0 as they must be treated a bit differently in 4.0 than the previous 3.0.x versions.

Thanks,

Brian


#12

Hello Brian,

You would need to create a separate report per project. You can also configure a scheduled/recurring report (e.g. that generates a report once per day or week) and this would make sense for this type of report. You can also let TestRail send you a notification email once a new report is available.

The fields have the exact same meaning in TestRail 3 and 4 and are also managed in the same way. The report simply ignores the Updated By/On value if the creation date is the same as the updated date to find out new vs. updated test cases.

I hope this helps!

Regards,
Tobias


#13

I will play with the dates in my SQL (we have a non-hosted install) - unfortunately one of the requested reports is data on the QA team as a whole. We are a small group and some manages want to see what the entire QA group is doing via creating new vs updating cases.

Since we are starting fresh (we really did not have test cases before my boss was brought on board) they want to make sure we are adding and updating cases. The number of new cases should be high for a bit then the updated cases should increase over time and new cases decrease.


#14

Hello Brian,

Thanks for the additional details, sounds good. Please let us know in case any questions come up, we are happy to help.

Regards,
Tobias