Hello All,
I usually develop the middle-tier and front-end of our ASP.NET - C# - nHibernate - SQL Server project but I am venturing more and more into the back-end. I am not new to development but I am relatively new to all things database and so I would like some advice regarding a relatively complex query.
I have the following query, and it works in SQL Server 2005 Management Studio.The parts of interest are the first INNER JOIN clause and the Select statement. Our database has a TestSetResults table with a primary key of TestSetResultID, a TestCases table with a primary key of TestCaseID and a TestCaseResults table with keys of TestSetResultID and TestCaseID. In this way a Test Set Result record is associated with many Test Case Result records. The purpose of the query is to get back a maximum of 50 TestSetResult records along with the number of tests completed (a count of the associated TestCaseResult records) and the number of test failures (a count of all the associated TestCaseResult records with a status of "failure") for each test run result. (A colleague provided me with this query, and I'm not entirely sure how it works).
Code:
SELECT TOP (50) {TSR.*}, {t1.*}, {t2.*}, {t3.*}, {t4.*}, {t5.*}, TestCounts.TestsCompleted, TestCounts.NumFailures
FROM TestSetResults AS TSR
INNER JOIN (
SELECT
TCR.TestSetResultID,
COUNT (TCR.TestCaseID) AS TestsCompleted,
SUM(CASE WHEN TCR.TestCaseStatus == 'failure' THEN 1 ELSE 0 END) AS NumFailures
FROM TestCaseResults TCR
GROUP BY TCR.TestSetResultID)
AS TestCounts ON TestCounts.TestSetResultID =TSR. TestSetResultID
INNER JOIN table1 AS t1 ON TSR.t1ID = t1.ID
INNER JOIN table2 AS t2 ON TSR.t2ID = t2.ID
INNER JOIN table3 AS t3 ON t2.t3ID = t3.ID
INNER JOIN table4 AS t4 ON TSR.t4ID = t4.ID
INNER JOIN table5 AS t5 ON t4.t5ID = t5.ID
WHERE pID IN ('...')
ORDER BY TSR.StartDateTime DESC
The trouble I am having is that I can't get the values of TestCounts.TestsCompleted and TestCounts.NumFailures from the query result. I suspect that there is a far better way of doing this by using the SUM and COUNT aggregate functions in the Select clause.
The code that I am using to perform this query looks something like this...
Code:
if (this.session.IsOpen)
{
using (ITransaction tx = this.session.BeginTransaction())
{
ISQLQuery nHibernateQuery = this.session.CreateSQLQuery(this.Query.Sql.Statement);
AddEntitiesToQuery(ref nHibernateQuery);
this.query.Result = nHibernateQuery.List();
tx.Commit();
}
}
I would be very grateful is somebody could explain how I should get
the TestCounts values from the query result.
Thanks in advance,
Mikey
Login
FAQ
Search