Need a kick in the right direction w/this query -- sql server programming

sql server programming : Need a kick in the right direction w/this query

Lucas Graf
6/25/2006 8:26:24 PM

Here is a link to the DDL since it would take up a lot of room here.

http://damageinc.org/DDL.html

After you use that to generate the table and some sample data, here is the
problem I need help with.

You will see the following data, if you use this query...

SELECT DISTINCT Apps.AppName, TestCases.TestCase , Tests.Attributes,
Reports.Result, Reports.ReportDate
FROM
Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID

AppName TestCase Attribute
Result ReportDate
Test App 1 Run for 30 minutes Attribute 1 Fail
2006-06-25 19:58:31.800
Test App 1 Run for 30 minutes Attribute 1 Pass
2006-06-25 19:58:29.800
Test App 1 Run for 30 minutes Attribute 1 Pass
2006-06-25 19:58:30.800
Test App 1 Run for 45 minutes Attribute 2 Fail
2006-06-25 19:58:33.800
Test App 1 Run for 45 minutes Attribute 2 Fail
2006-06-25 19:58:34.800
Test App 1 Run for 45 minutes Attribute 2 Pass
2006-06-25 19:58:32.800
Test App 1 Run for 60 minutes Attribute 3 Fail
2006-06-25 19:58:36.863
Test App 1 Run for 60 minutes Attribute 3 Pass
2006-06-25 19:58:35.863
Test App 1 Run for 60 minutes Attribute 3 Pass
2006-06-25 19:58:37.863

What I need to get is the count most recent result per TestCase regardless
of attribute.
So I was trying to do it visually by using this query..

SELECT tc.TestCase, tc.mostRecent, tc.Result
FROM Reports rp
RIGHT JOIN
(
SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
mostRecent
FROM Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID

WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )

GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent =
rp.ReportDate ) ORDER BY TestCAse

Which will give you this data...

TestCase mostRecent Result
Run for 30 minutes 2006-06-25 19:58:31.800 Fail
Run for 30 minutes 2006-06-25 19:58:30.800 Pass
Run for 45 minutes 2006-06-25 19:58:34.800 Fail
Run for 45 minutes 2006-06-25 19:58:32.800 Pass
Run for 60 minutes 2006-06-25 19:58:36.863 Fail
Run for 60 minutes 2006-06-25 19:58:37.863 Pass

Which as you can tell is giving me both the most recent pass & fail result,
where I want the most recent result regardless of Pass/Fail.

Which in turn, makes my count query wrong as well...

SELECT COUNT(DISTINCT tc.TestCase) As Count,
COUNT(DISTINCT CASE rp.Result WHEN 'Pass' THEN rp.ReportID ELSE NULL END) as
Pass,
COUNT(DISTINCT CASE rp.Result WHEN 'Fail' THEN rp.ReportID ELSE NULL END) as
Fail
FROM Reports rp
RIGHT JOIN
(
SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
mostRecent
FROM Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID

WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )

GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent = rp.ReportDate )

Which will give me

Count Pass Fail
3 3 3

Instead of the result I am looking for of..

Count Pass Fail
3 1 2

Uri Dimant
6/26/2006 12:00:00 AM

Lucas
Thanks fro posting DDL

See if this helps you

SELECT TestCase,COUNT(CASE WHEN Result='Fail' THEN ReportDate END) AS
'Fail',
COUNT(CASE WHEN Result='Pass' THEN ReportDate END) AS 'Pass'
FROM
(
SELECT Apps.AppName, TestCases.TestCase , Tests.Attributes,
Reports.Result, Reports.ReportDate
FROM
Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID
) AS Der GROUP BY TestCase

[quoted text, click to view]

Tom Cooper
6/26/2006 12:35:50 AM

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.TestID = tc.TestCaseID
Inner Join Tests t On tc.TestCaseID = t.TestID
Inner Join Apps a On tc.AppID = a.AppID
Where t.Attributes In ('Attribute 1', 'Attribute 2', 'Attribute 3')
Group By a.AppName, tc.TestCase)

The above query assumes that there are no duplicates in Reports.ReportDate.

I changed your Left Outer Joins to Inner Joins since if for a Report row,
you don't have a TestCases row and a Tests row, then the Attributes column
will nave NULL in it, so the where condition would be false. So your Left
Outer Join gives the same result as an Inner Join, but Inner Joins are ofter
faster.

BTW, your DDL is not too long to post to this group (IMHO) and you will have
better luck if you just include it the post rather than a web site since
some people are reluctant to go to an unknown site.

Tom

[quoted text, click to view]

Lucas Graf
6/26/2006 12:26:33 PM

Hi guys, thanks for the help so far.

I am really close now. One remaining problem, say there is no report s for
say testcase 3 (delete any reports w/the TestID of 3 in it).

Running the query will give me

Count Pass Fail
2 0 2

What i need is to show the total test cases even if there is no result(s)
for some, and the count of the ones where there are results for like so..

Count Pass Fail
3 0 2

Lucas Graf
6/26/2006 1:04:49 PM

Thanks Tom "the master" Cooper!

I appreciate everyone's help as well, every time I post a question I get
another nugget of i nformation to store away.

[quoted text, click to view]

Tom Cooper
6/26/2006 3:59:53 PM

Select Count(*)
+ (Select Count(*) From
TestCases tc
Where Not Exists (Select 1 From Reports r Where r.TestID =
tc.TestCaseID)) 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.TestID = tc.TestCaseID
Inner Join Tests t On tc.TestCaseID = t.TestID
Inner Join Apps a On tc.AppID = a.AppID
Where t.Attributes In ('Attribute 1', 'Attribute 2', 'Attribute 3')
Group By a.AppName, tc.TestCase)

Tom

[quoted text, click to view]

Lucas Graf
6/26/2006 5:23:46 PM

Tom, maybe I could pick your brain one more time :)

For whatever reason on my actual tables I still can't get what I need :(

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...

====================================================================================

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn