changed tests that have been automated..

[JamesA99]

Hi, I am trying to put together a SQL report that will give me details on changed tests...what I would like is to have the report detail the test name, folder it is a part of (or test container), what has changed (possible to include step name?) and the other usual details of ChangedAt, ChangedBy, CreatedAt,CreatedBy.

I would like it to filter off of the attribute or property as well (eg test that have been automated), note we have been looking at using the 'show changes' button but it gives us all changes to tests but we want to look up just changes to automated tests (based on attribute or property).

this is what I got so far, very general and covers all tests...need detail on automated tests, 

SELECT Name as 'Test Case', Description, ChangedBy, ChangedAt, CreatedAt, NodeID_pk, ParentNodeID_fk
FROM TM_TestPlanNodes tpn
WHERE datediff(ww, tpn.ChangedAt, getDate()) <= 1

any suggestion to use another approach is appreciated and any help would be appreciated,

thanks,

James

[Hubert Inreiter]

Hi James,

If you want to query all tests which have been automated recently you can use the table "TM_PlanHistory" there for some (not all) types of changes of tests history entries are written. NodeType = 121 is for tests automated from manual and NodeType = 123 is for tests converted from type automated to manual.

Generally, I'd suggest to use the LQM_Tests table for retrieving information on tests which covers test properties in a flat form and reduces complexity of SQL (less joins necessary). Additionally the Test Container is covered with this table too.

Please see the example below which queries all recent tests which have been automated. Use for example columns starting with a_junit_* to get the JUnit properties or a_nunit_* for NUnit properties:

SELECT tests.Name "Test Case", tests.ChangedBy, tests.ChangedAt, tests.CreatedAt, TestID_pk, ParentID_fk,

tpnTc.Name "Test Container", tests.Type  -- tests.a_junit_* tests.a_nunit_* ...

FROM LQM_Tests tests

INNER JOIN TM_TestPlanNodes tpnTc ON tests.ContainerID_fk = tpnTc.NodeID_pk

INNER JOIN TM_PlanHistory hist ON tests.TestID_pk = hist.PlanNodeID_fk

WHERE hist.NodeType = 121 -- CREATED_FROM_MANUAL

AND datediff(ww, hist.ChangedAt, getDate()) <= 1

ORDER BY hist.ChangedAt DESC

Regards,

Hubert

[JamesA99]

Thanks Hubert, I will try out the code...thanks for helping out!