Another SQL question regarding timetables

Back with another question about SQL. This time I'd like to do the following:

Find how many tests have failed or passed before date x, with the specific restriction to only look for the latest executed version of that testcase

To clarify:

TestCase Date Result
1 01-11-2006 Fail
2 02-11-2006 Pass
3 05-11-2006 Pass
1 13-11-2006 Fail
1 15-11-2006 Pass
3 11-11-2006 Pass
1 29-11-2006 Fail
2 29-11-2006 Pass
3 29-11-2006 Fail

I'd like to see:

MaxDate NrOfPassed NrOfFailed
08-11-2006 2 1
17-11-2006 3 0
01-12-2006 1 2


For one line I'd know what to do (aircoded):


CREATE VIEW tmpTable
AS
SELECT
TestCase,
Max(Date) as MaxDate,
From
Test
WHERE
Date < 17-11-2006
GROUP BY
TestCase


and then

SELECT
tmpTable.MaxDate,
SUM(CASE WHEN test.Result = 'Pass' THEN 1 ELSE 0 END) As Passed,
SUM(CASE WHEN test.Result = 'Fail' THEN 1 ELSE 0 END) As Failed
INNER JOIN
tmpTable
ON test.TestCase = tmpTable.TestCase AND test.Date = tmpTable.MaxDate


But this is only for 1 row in my final table. Does anyone know how to do this all in a efficient way?

First, Date is a reserved word, so I'd highly recommend changing your 'Date' field to 'TestDate' or something along those lines.


I think this will give you what you need:

SELECT TestCase,
Max(TestDate) AS MaxTestDate,
Sum(CASE WHEN Result='Pass' THEN 1 ELSE 0 END) AS NumPassed,
Sum(CASE WHEN Result='Fail' THEN 1 ELSE 0 END) AS NumFailed

FROM Test

WHERE TestDate < '11/17/2006'

GROUP BY TestCase

OK, have been doing some research based on your suggestion, but no luck so far. Your suggestion does not give only the max date entry, but all entry's. This is because you use the Group BY clause.

When only one record per test is given, I would only get either 1 passed OR 1 fail. When executing your query I will get multiple passed en multiple fail.

I have tried a different approach to this problem but without any luck. I have triedto expand the CASE WHEN statement with a check to see if the date is the maximum date of that testcase:


SELECT TestCase,
Max(TestDate) AS MaxTestDate,
Sum(CASE WHEN Result='Pass' AND TestDate = MAX(TestDate) THEN 1 ELSE 0 END) AS NumPassed,
Sum(CASE WHEN Result='Fail'AND TestDate = MAX(TestDate) THEN 1 ELSE 0 END) AS NumFailed

FROM Test

WHERE TestDate < '11/17/2006'

GROUP BY TestCase


This does not seem to work: You cannot use max(date) or the likes in a CASE WHEN statement.

How do I limit the results to one testcase per testcase ID, basing the selection on the maximum date. Something like DISTINCT, but then based on the max date or something.

As a temporary solution, I can at least create a table containing all the maxdates for each testcase grouped by the weeknrs:


CREATE VIEW TmpTest
AS
SELECT TestCase,
Datename(YEAR,MAX(TestDate)) as Year,
Datename(WEEK,MAX(TestDate)) as Week,
MAX(TestDate)
From Test
GROUP BY Datename(YEAR,MAX(TestDate)),
Datename(WEEK,MAX(TestDate))


...and then query the results (I'd use a join to get the remainder of the data, but I'll leave it out here, it's just for better understanding that I note it here)



SELECT *
from
tmpTest
WHERE
Year < 2007 AND Week < 49


But then I could still have doubles (maybe I executed testcase 3 in weeknr's 10 and 11, how do I only select the testcase of week 11?). Maybe using distinct or something alike?

I hope my issue is clear enough to help me out, I've learned some new stuff along the way but I don't have my answer yet (but we're getting there).

TIA for your help :)

Peter, You were partially right on your query

This:


SELECT TestCase,
Max(TestDate) AS MaxTestDate,
FROM Test
WHERE TestDate < '11/17/2006'
GROUP BY TestCase


Combined with a join query to get the result from the original table will do the trick perfectly....For one moment in time (in this case you'll get the status of the tests at 11/17/2006). But now I want to loop this query to get the status of the testcases for each week. Does anyone wknow how?

TIA ! :)

I hate to BUMP, but does no one have an idea of how to do the above? Or is my question not clear enough?

If you'll post some example data, and the exact results you'd expect to get back from the query, I'll look at it.

It would also help if you tell us what database are we using here.

woops... forgot that detail in my enthusiasm ;)

I'm using MSSQL

And some sample data/results would still be handy. From the informaiton you have above, I still can't see what you want.

(If you can create a basic test script that creates a table, and inserts sample data you could attach it as a .txt file. Then we can play with it on our side...)

Bit late, but still...

Unfortunately, I cant create a dump of the data since I have no direct access to it. I did, however, create an example table in excel with my goals explained. I really can't put it any clearer then this. Hope you guys (and gals) understand :o

I sure hope you guys can help, if I can fix this, everything I need to do functionally can be done and I can start coding the program :)