Need Kick in right direction w/query take 2

Bumping this up to the top, scroll down a bit to see the original thread.
--
I have updated the DDL with the actual tables and a sample of table data of
what I am actually using.
http://damageinc.org/DDL.html (The DDL is too large to post, the message
gets kicked back to me)
AFter you enter that DDL
If you run the query..
SELECT Apps.AppName, Tests.TestCase, TestCases.Type
FROM
TestCases
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
LEFT OUTER JOIN Tests ON TestCases.TestID = Tests.ID
WHERE
(
(
(Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
'87')
OR
(Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
'88')
)
AND (TestCases.Card = 'G71_D' OR TestCases.Card = 'G70_D' OR TestCases.Card
= 'G72_D' OR TestCases.Card = 'G73_D')
AND TestCases.OS = 'Windows Vista'
)
GROUP BY AppName, TestCase,TestCases.Type
You will see
AppName TestCase
Type
3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso D3D Benchmarks
3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso Games
3D Mark 2003 Benchmark: Default 2x AA 4x Aniso D3D
Benchmarks
3D Mark 2003 Benchmark: Default 2x AA 4x Aniso G ames
.
.
.
.
There are 97 tests there.
What I need is that 97 for the count, then the count of the most recent
pass/fail result(s) (if there is a result) for each of those tests.
Using
Select Count(*) As 'Count',
Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Where r.ReportDate In
(Select Max(r.ReportDate)
From Reports r
Inner Join TestCases tc On r.TestCaseID = tc.ID
Inner Join Tests t On tc.TestID = t.ID
Inner Join Apps a On tc.AppID = a.ID
WHERE
(
(
(tc.Proj1 = '87' OR tc.Proj2 = '87' OR tc.Proj3 = '87' OR tc.Proj4 = '87'
OR tc.Proj5 = '87')
OR
(tc.Proj1 = '88' OR tc.Proj2 = '88' OR tc.Proj3 = '88' OR tc.Proj4 = '88'
OR tc.Proj5 = '88')
)
AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR tc.Card
= 'G73_D')
AND tc.OS = 'Windows Vista'
)
Group By a.AppName, t.TestCase)
Gives me
Count Pass Fail
25 2 23
Which is obviously wrong, since the Count should be 97.
I have to be overthinking something here...I think this works:
SELECT Count(*) As 'Count',
Sum (Case When r.Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum (Case When r.Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Inner Join TestCases tc On r.TestCaseID = tc.ID
LEFT OUTER JOIN Apps a ON tc.AppID = a.ID
LEFT OUTER JOIN Tests t ON tc.TestID = t.ID
Right Join (
SELECT a.AppName, t.TestCase, tc.Type, Max(Coalesce(r.ReportDate,
'1900-01-01'))As ReportDate
FROM TestCases tc
LEFT OUTER JOIN Apps a ON tc.AppID = a.ID
LEFT OUTER JOIN Tests t ON tc.TestID = t.ID
LEFT OUTER JOIN Reports r ON r.TestCaseID = tc.ID
WHERE
(
(
(Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
'87')
OR
(Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
'88')
)
AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR tc.Card
= 'G73_D')
AND tc.OS = 'Windows Vista'
)
GROUP BY AppName, TestCase,tc.Type) x On r.ReportDate = x.ReportDate
Tom
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:%23zfcKfZmGHA.3880@.TK2MSFTNGP02.phx.gbl...
> Bumping this up to the top, scroll down a bit to see the original thread.
> --
> I have updated the DDL with the actual tables and a sample of table data
> of
> what I am actually using.
> http://damageinc.org/DDL.html (The DDL is too large to post, the message
> gets kicked back to me)
> AFter you enter that DDL
> If you run the query..
> SELECT Apps.AppName, Tests.TestCase, TestCases.Type
> FROM
> TestCases
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
> LEFT OUTER JOIN Tests ON TestCases.TestID = Tests.ID
> WHERE
> (
> (
> (Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
> '87')
> OR
> (Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
> '88')
> )
> AND (TestCases.Card = 'G71_D' OR TestCases.Card = 'G70_D' OR
> TestCases.Card
> = 'G72_D' OR TestCases.Card = 'G73_D')
> AND TestCases.OS = 'Windows Vista'
>
> )
> GROUP BY AppName, TestCase,TestCases.Type
>
> You will see
>
> AppName TestCase
> Type
> 3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso D3D Benchmarks
> 3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso Games
> 3D Mark 2003 Benchmark: Default 2x AA 4x Aniso D3D
> Benchmarks
> 3D Mark 2003 Benchmark: Default 2x AA 4x Aniso G ames
> .
> .
> .
> .
>
> There are 97 tests there.
> What I need is that 97 for the count, then the count of the most recent
> pass/fail result(s) (if there is a result) for each of those tests.
> Using
> Select Count(*) As 'Count',
> Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
> Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
> From Reports r
> Where r.ReportDate In
> (Select Max(r.ReportDate)
> From Reports r
> Inner Join TestCases tc On r.TestCaseID = tc.ID
> Inner Join Tests t On tc.TestID = t.ID
> Inner Join Apps a On tc.AppID = a.ID
> WHERE
> (
> (
> (tc.Proj1 = '87' OR tc.Proj2 = '87' OR tc.Proj3 = '87' OR tc.Proj4 =
> '87'
> OR tc.Proj5 = '87')
> OR
> (tc.Proj1 = '88' OR tc.Proj2 = '88' OR tc.Proj3 = '88' OR tc.Proj4 =
> '88'
> OR tc.Proj5 = '88')
> )
> AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR
> tc.Card
> = 'G73_D')
> AND tc.OS = 'Windows Vista'
> )
> Group By a.AppName, t.TestCase)
> Gives me
> Count Pass Fail
> 25 2 23
>
> Which is obviously wrong, since the Count should be 97.
> I have to be overthinking something here...
>
>