Another SQL question regarding timetables

12-19-2006, 09:56 AM

jf0rce

Junior Contributor

Join Date: Nov 2002

Posts: 211

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):

Code:

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

and then

Code:

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?

__________________
There are 10 kinds of people: those who think binary, and those who don't

12-19-2006, 01:23 PM

Peter_Aquino Peter_Aquino is offline

Contributor

* Expert *

Join Date: Sep 2004

Location: San Diego, CA, USA

Posts: 732

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:

Code:

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

12-22-2006, 04:47 AM

jf0rce

Junior Contributor

Join Date: Nov 2002

Posts: 211

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:

Code:

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:

Code:

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)

Code:

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

__________________
There are 10 kinds of people: those who think binary, and those who don't

Last edited by jf0rce; 12-22-2006 at 04:48 AM. Reason: Typo

12-22-2006, 07:30 AM

jf0rce

Junior Contributor

Join Date: Nov 2002

Posts: 211

Peter, You were partially right on your query

This:

Code:

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 !

__________________
There are 10 kinds of people: those who think binary, and those who don't

01-02-2007, 04:39 AM

jf0rce

Junior Contributor

Join Date: Nov 2002

Posts: 211

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

__________________
There are 10 kinds of people: those who think binary, and those who don't

01-02-2007, 08:10 AM

Peter_Aquino Peter_Aquino is offline

Contributor

* Expert *

Join Date: Sep 2004

Location: San Diego, CA, USA

Posts: 732

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.

01-02-2007, 04:49 PM

NEOLLE

fully realized avatar

Super Moderator
* Expert *

Join Date: Jun 2004

Location: Davao Philippines

Posts: 2,295

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

__________________
A Day Called Today The Avatar State Amazing Davao! Shared Pool

01-03-2007, 03:11 AM

jf0rce

Junior Contributor

Join Date: Nov 2002

Posts: 211

woops... forgot that detail in my enthusiasm

I'm using MSSQL

__________________
There are 10 kinds of people: those who think binary, and those who don't

01-03-2007, 07:20 AM

wayneph

Web Junkie

Retired Moderator
* Expert *

Join Date: Apr 2004

Location: D/FW, Texas, USA

Posts: 8,393

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

__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned

#10

01-10-2007, 07:42 AM

jf0rce

Junior Contributor

Join Date: Nov 2002

Posts: 211

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

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

Attached Files

example data.zip (7.9 KB, 3 views)

__________________
There are 10 kinds of people: those who think binary, and those who don't