Here is a watered down DDL of my tables.

CREATE TABLE Apps
(
AppID smallint,
AppName varchar(32),
PRIMARY KEY (AppID)
)


CREATE TABLE TestCases
(
TestCaseID smallint,
AppID smallint,
TestCase varchar(32),
PRIMARY KEY (TestCaseID)
)

CREATE TABLE Reports
(
ReportID smallint,
TestCaseID smallint,
Result bit,
ReportDate datetime
)



INSERT INTO Apps (AppID,AppName)
VALUES (1,'Test App 1')

INSERT INTO TestCases (TestCaseID,AppID,TestCase)
VALUES (1,1,'Run for 30 minutes')
INSERT INTO TestCases (TestCaseID,AppID,TestCase)
VALUES (2,1,'Run for 45 minutes')
INSERT INTO TestCases (TestCaseID,AppID,TestCase)
VALUES (3,1,'Run for 60 minutes')

INSERT INTO Reports(ReportID,TestCaseID,Result,ReportDate)
VALUES (1,2,1,GETDATE())
WAITFOR DELAY '000:00:01'
INSERT INTO Reports(ReportID,TestCaseID,Result,ReportDate)
VALUES (2,3,1,GETDATE())
WAITFOR DELAY '000:00:01'
INSERT INTO Reports(ReportID,TestCaseID,Result,ReportDate)
VALUES (3,3,0,GETDATE())
WAITFOR DELAY '000:00:01'

At this point if you run the following query

SELECT  Apps.AppName,Testcases.TestCase,Reports.Result,Reports.ReportDate
FROM TestCases
LEFT OUTER JOIN Reports ON Reports.TestCaseID = TestCases.TestCaseID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID

You will have
AppName        TestCase                    Result     Date
Test App 1     Run for 30 minutes     NULL     NULL
Test App 1     Run for 45 minutes     1     2006-02-17 14:03:03.813
Test App 1     Run for 60 minutes     1     2006-02-17 14:03:04.813
Test App 1     Run for 60 minutes     0     2006-02-17 14:03:05.813

2 of the 3 test cases have been tested, and 1 has been tested twice.
What I am looking to get at this point is a count of all test cases , plus
the latest pass/fail result.
E.G

Count            Pass       Fail
  3                      1             1

I have the following query

SELECT
COUNT(DISTINCT rp.TestCaseID) As Count,
COUNT(DISTINCT CASE rp.Result WHEN '1' THEN rp.ReportID ELSE NULL END) as
Pass,
COUNT(DISTINCT CASE rp.Result WHEN '0' THEN rp.ReportID ELSE NULL END) as
Fail

FROM
Reports rp JOIN
(
  SELECT
   Reports.TestCaseID,
   MAX( Reports.ReportDate) AS mostRecent
   FROM Reports
   GROUP BY TestCaseID
)
tc
ON (
  rp.TestCaseID = tc.TestCaseID
  And rp.ReportDate = tc.mostRecent
)

But that gives me

Count       Pass   Fail
  2                1         1

What do I need to modify to get my needed result of ...
Count       Pass   Fail
  3                1        1

?

Any help is much appreciated!

Let's design the schema around a Test, instead of splitting out the
attirbutes into their own tables. Is a result an entity by itself or
doe sit only make sense with a Test?  Etc.  This is basic data
modeling.

CREATE TABLE Tests
(test_nbr INTEGER NOT NULL PRIMARY KEY, -- assume internal code?
test_description CHAR(20) NOT NULL,
appl_name CHAR(20) NOT NULL,
test_date DATETIME, -- null is not scheduled
test_results CHAR(1) DEFAULT 'N' NOT NULL
    CHECK (test_result IN ('N', 'S', 'F', 'P'),
CHECK (CASE WHEN test_date IS NULL AND test_result = 'N'
             THEN 'T'
             WHEN test_date IS NOT NULL AND test_result <> 'N'
             THEN 'T'
             ELSE 'F' END = 'T')
);

where test_result codes mean

'N' = not scheduled, no date
'S' = scheduled -- has to have a date
'F' = tested and failed
'P' = tested and passed

Good SQL programmers never use the low level, proprietary, rigid BIT
data type.  We know we have to design codes and that it is hard work.

the latest pass/fail result. <<

Those are two different levels of aggregation.

CREATE VIEW CurrentTests (test_description, appl_name, test_date,
test_results)
AS
SELECT test_description, appl_name, test_date, test_results
  FROM Tests AS T1
WHERE test_date
       = (SELECT MAX(T2.test_date
            FROM Tests AS T2
           WHERE T1.test_description = T2.test_description
             AND T1.appl_name = T2.appl_name)
   AND test_results IN ('P', 'F');

and then a seocnd query for count

CREATE VIEW CurrentTests (test_description, appl_name, N, S, P, F,
test_count)
AS
SELECT test_description, appl_name,
       SUM (CASE WHEN test_results = 'N' THEN ! ELSE 0 END),
       SUM (CASE WHEN test_results = 'S' THEN ! ELSE 0 END),
       SUM (CASE WHEN test_results = 'P' THEN ! ELSE 0 END),
       SUM (CASE WHEN test_results = 'F' THEN ! ELSE 0 END),
       COUNT(*) FROM Tests AS T1
GROUP BY test_description, appl_name;

Y9u can run these VIEWs together, but I would not do it

Like I said the DDL i posted is very very extremley watered down.
While your below solution may be great if I was at the point of starting
over, I am not.

We have apps, which have test cases where each test case has a set of
"flags" that make up the final test. E.g. Video Card, OS, and a few other
items.  So each test case say "Run for 30 minutes" can have x number of OS
and x number of Video card combinations like

Run For 30 minutes     Win Xp       VidCardA
Run For 30 minutes     Win Xp       VidCardB
Run For 30 minutes     Win2k      VidCardA
Run For 30 minutes     Win2k      VidCardB
Run For 30 minutes     WinME     VidCardA
Run For 30 minutes     WinME     VidCardB

These are what make up the actual tests.

And yes i need the report as a seperaate entity to log driver version, etc.
against each of those various test cases.
So again, while your solution may be helpful under your designed schema, it
does me no good under what I have.




Show quoteHide quote

On a side note, i just read a bunch of your older posts on IDENTITY and
found them very interesting.

Show quoteHide quote

In your subquery, you are only looking at the TestCaseIDs that have
been recorded to the Reports table; these would include only the tests
that have been run (as I understand it).  You need to include the list
of possibl TestCases in your subquery, like so:


SELECT
COUNT(DISTINCT tc.TestCaseID) As Count,
COUNT(DISTINCT CASE rp.Result WHEN '1' THEN rp.ReportID ELSE NULL END)
as
Pass,
COUNT(DISTINCT CASE rp.Result WHEN '0' THEN rp.ReportID ELSE NULL END)
as
Fail

FROM
Reports rp  RIGHT JOIN
(
  SELECT
   TestCases.TestCaseID,
   MAX( Reports.ReportDate) AS mostRecent
   FROM TestCases LEFT JOIN Reports ON TestCases.TestCaseID =
Reports.TestCaseID
   GROUP BY TestCases.TestCaseID
)
tc
ON (
  rp.TestCaseID = tc.TestCaseID
  And rp.ReportDate = tc.mostRecent
)



HTH,
Stu

OBJECTPROPERTY problem
T-SQL: How to loop through the resultset from another procedure?
optimize queries with unexpected results
"Where date" problem
Help with Stored Prcoedure