April 24, 2012 at 9:27 am
This is probably one of those things that is simple, but it is about to drive me crazy.
I need to return a list of test sites that failed a test the last time they were tested.
The database could contain years of test data, so an old test failure that had resulted in a repair should not be among the list.
i.e. A site should be included only if the last time it was tested it failed.
I have tried using MAX(Date), a subquery, and various combinations. I have created result sets that contain just about anything possible....except the one result set I need.
A query to find those sites that have a PfReal of less than 0.7 should contain the results:
Name Date
SiteTwo 2004-07-12
SiteSix 2006-06-12
I have attached a script (as a txt file) to create a small database with two partial tables
--CREATE DATABASE ExampleTest--Run this command first to create the DB
-- before creatingtables and data --(need to ask if this is a SMS quirk
--or if entire script can be run outside of the --IDE)
USE ExampleTest
CREATE TABLE Locations
(
LocID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(20) UNIQUE,
ActiveStatus INT
);
CREATE TABLE Tests
(
TestDate DATE,
Phase CHAR(1),
TestType VARCHAR(3),
PfReal real,
THDv real,
THDi real,
LocID INT
)
INSERT INTO Locations
VALUES('SiteOne', 0);
INSERT INTO Locations
VALUES('SiteTwo', 0);
INSERT INTO Locations
VALUES('SiteThree', 0);
INSERT INTO Locations
VALUES('SiteFour', 0);
INSERT INTO Locations
VALUES('SiteFive', 0);
INSERT INTO Locations
VALUES('SiteSix', 0);
INSERT INTO Locations
VALUES('SiteSeven', 0);
INSERT INTO Locations
VALUES('SiteEight', 0);
INSERT INTO Locations
VALUES('SiteNine', 0);
INSERT INTO Locations
VALUES('SiteTen', 0);
INSERT INTO Tests
VALUES('6/5/02','A',012,0.897652,1.677156,6.405772,1);
INSERT INTO Tests
VALUES('6/5/02','B',012,1.736501,9.298439,0.853296,1);
INSERT INTO Tests
Values('6/5/02','C',012,1.871134,7.616451,0.919981,1);
INSERT INTO Tests
VALUES('6/7/02','A',012,0.786763,1.766256,6.514663,2);
INSERT INTO Tests
VALUES('6/7/02','B',012,1.825413,9.395159,0.855296,2);
INSERT INTO Tests
Values('6/7/02','C',012,1.863232,7.526421,0.921941,2);
INSERT INTO Tests
VALUES('6/7/02','A',017,1.986868,6.459862,0.887152,5);
INSERT INTO Tests
VALUES('6/7/02','B',017,1.954125,7.327158,0.893756,5);
INSERT INTO Tests
VALUES('6/12/03','A',012,0.897652,1.677156,6.405772,1);
INSERT INTO Tests
VALUES('6/12/03','B',012,1.736501,9.298439,0.853296,1);
INSERT INTO Tests
Values('6/12/03','C',012,2.163542,7.616451,0.919981,1);
INSERT INTO Tests
VALUES('6/12/03','1',011,0.897652,1.657126,6.524761,6);
INSERT INTO Tests
VALUES('6/12/03','A',012,0.876932,1.655626,6.457172,3);
INSERT INTO Tests
VALUES('6/12/03','B',012,1.736501,9.298439,0.853296,3);
INSERT INTO Tests
VALUES('8/13/03','A',013,1.367673,1.753256,5.661435,2);
INSERT INTO Tests
VALUES('8/13/03','B',013,1.528143,9.359169,0.825276,2);
INSERT INTO Tests
Values('8/13/03','C',013,1.762332,7.562421,0.912941,2);
INSERT INTO Tests
Values('8/15/03','A',017,0.999329,2.77908,2.766069,4);
INSERT INTO Tests
Values('8/15/03','C',017,0.996485,2.809778,2.7955,4);
INSERT INTO Tests
VALUES('8/15/03','A',012,1.286243,1.691756,6.382763,7);
INSERT INTO Tests
VALUES('8/15/03','B',012,1.562813,9.253319,0.724696,7);
INSERT INTO Tests
Values('8/15/03','C',012,1.684232,6.526651,0.831541,7);
INSERT INTO Tests
VALUES('7/12/04','A',012,0.66873,1.752146,6.431563,2);
INSERT INTO Tests
VALUES('7/12/04','B',012,1.843163,9.387519,0.778296,2);
INSERT INTO Tests
Values('7/12/04','C',012,1.795432,7.535511,0.875341,2);
INSERT INTO Tests
Values('7/12/04','A',017,0.688429,2.79615,2.755869,8);
INSERT INTO Tests
Values('7/12/04','C',017,0.988525,2.812878,2.7860,8);
INSERT INTO Tests
VALUES('6/18/05','A',012,0.886512,1.77656,6.514772,1);
INSERT INTO Tests
VALUES('6/18/05','B',012,1.785601,8.110339,0.852352,1);
INSERT INTO Tests
Values('6/18/05','C',012,1.882534,7.614291,0.938581,1);
INSERT INTO Tests
VALUES('7/6/06','A',017,1.889668,6.845962,0.718752,5);
INSERT INTO Tests
VALUES('7/6/06','B',017,1.845215,7.375128,0.379586,5);
INSERT INTO Tests
Values('7/18/06','A',017,0.978529,2.79615,2.755869,8);
INSERT INTO Tests
Values('7/18/06','C',017,0.988525,2.812878,2.7860,8);
INSERT INTO Tests
VALUES('7/19/06','A',012,0.952462,2.267046,6.504772,1);
INSERT INTO Tests
VALUES('7/19/06','B',012,1.642401,8.983439,0.861596,1);
INSERT INTO Tests
Values('7/19/06','C',012,1.781134,6.881451,0.919981,1);
INSERT INTO Tests
VALUES('6/12/06','1',011,0.675652,1.657126,6.524761,6);
INSERT INTO Tests
VALUES('6/7/07','A',013,0.766763,1.775356,6.514663,9);
INSERT INTO Tests
VALUES('6/7/07','B',013,1.825413,8.395159,0.855296,9);
INSERT INTO Tests
Values('6/7/07','C',013,1.854232,8.526421,0.922941,9);
INSERT INTO Tests
VALUES('8/11/07','A',012,1.483543,1.691756,6.988448,7);
INSERT INTO Tests
VALUES('8/11/07','B',012,1.562813,9.253129,0.724556,7);
INSERT INTO Tests
Values('8/11/07','C',012,1.865232,6.432451,0.831541,7);
INSERT INTO Tests
VALUES('6/7/02','A',017,0.786763,1.766256,6.514663,10);
INSERT INTO Tests
Values('6/7/02','C',017,1.863232,7.526421,0.921941,10);
April 24, 2012 at 9:36 am
select tests.* from tests
inner join
(
select locid,max(testdate) as md from tests
group by locid
) as lf
on lf.locid=tests.locid and lf.md=tests.testdate
where pfreal<0.7
MVDBA
April 24, 2012 at 9:40 am
even better, i formatted it as you wanted
select l.name,test.testdate from tests
inner join
(
select locid,max(testdate) as md from tests
group by locid
) as lf
on lf.locid=tests.locid and lf.md=tests.testdate
inner join location l on l.locid=tests.locid
where pfreal<0.7
MVDBA
April 24, 2012 at 9:40 am
Wow first post and you posted everything to make this easy for us!!! Excellent!!!
Something like this should work
select * from
(
select l.Name, l.ActiveStatus, t.*, ROW_NUMBER() OVER (partition by l.LocID order by TestDate Desc) as RowNum
from Locations l
join Tests t on l.LocID = t.LocID
) x
where x.RowNum = 1 and x.PfReal < 0.7
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 9:49 am
sean's solution proboably performs better than mine
MVDBA
April 24, 2012 at 9:55 am
One more way, that uses the same logic as Sean, but uses a CTE instead of derived table.
with MyTests as(
select L.Name, TestDate, PfReal, row_number() over (partition by L.Name order by TestDate desc) as RowNum
from Tests as T inner join Locations as L on T.LocID = L.LocID)
Select Name, TestDate
from MyTests
where RowNum = 1 and PfReal < 0.7
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 24, 2012 at 9:57 am
WOW!
That was fast, thanks! Good to know I included all the info needed, have to confess I wasn't sure about the larity of my request workding.
I am now going to study these. As you might guess I am just starting the learning curve. Using Murach's SQL SQL Server 2008 for Developers, any other suggestions for texts?
thanks
April 24, 2012 at 10:02 am
Have to add a thanks. You did very good posting your first question. There are others out there that could learn from you.
April 24, 2012 at 10:03 am
Adi Cohn-120898 (4/24/2012)
One more way, that uses the same logic as Sean, but uses a CTE instead of derived table.
with MyTests as(
select L.Name, TestDate, PfReal, row_number() over (partition by L.Name order by TestDate desc) as RowNum
from Tests as T inner join Locations as L on T.LocID = L.LocID)
Select Name, TestDate
from MyTests
where RowNum = 1 and PfReal < 0.7
Adi
The version I posted and the cte version you posted have the exact same execution plan. This type of thing has come up before and I think we pretty much determined that either style is going to perform the same.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 10:07 am
rogle 43122 (4/24/2012)
WOW!That was fast, thanks! Good to know I included all the info needed, have to confess I wasn't sure about the larity of my request workding.
I am now going to study these. As you might guess I am just starting the learning curve. Using Murach's SQL SQL Server 2008 for Developers, any other suggestions for texts?
thanks
The speed and accuracy of the responses is 100% a result of you thinking about what somebody else might need to answer your question and the fact that you posted it.
This site is a great extension of any learning you could do on your own. Hang out here. Read questions posted by other that are of particular interest. As you get more comfortable, try answering them yourself, even if you don't post your answers. Eventually you will be answering questions on your own. The important thing to remember is that the topic of SQL is entirely too big for anybody to be a master of the whole subject. We are all learning daily. It is like drinking from a fire hose at first but it gets pretty refined over time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply