I am having trouble setting up a query for my inspection test results for a given work piece.
Example Table [Inspection Data]
JOBSERIALPARAMMINMAXVALUEPFDATETIME
11Test101011F6/3/2007
11Test10105P6/4/2007
11Test2286P6/3/2007
11Test2281F6/4/2007
12Test10104P6/3/2007
12Test2285P6/4/2007
11Test3687P6/3/2007
Query table [Inspection Data] for:
JOB = 1
SERIAL = 1
MAX( DATETIME ) for each test
Expected Results:
JOBSERIALPARAMMINMAXVALUEPFDATETIME
11Test10105P6/4/2007
11Test2281F2/4/2007
11Test3687P6/3/2007
Thanks,
Sam
Here you go...
Code Snippet
Create Table #inspectiondata (
[JOB] int ,
[SERIAL] int ,
[PARAM] Varchar(100) ,
[MIN] int ,
[MAX] int ,
[VALUE] int ,
[PF] Varchar(100) ,
[DATETIME] Datetime
);
Insert Into #inspectiondata Values('1','1','Test1','0','10','11','F','6/3/2007');
Insert Into #inspectiondata Values('1','1','Test1','0','10','5','P','6/4/2007');
Insert Into #inspectiondata Values('1','1','Test2','2','8','6','P','6/3/2007');
Insert Into #inspectiondata Values('1','1','Test2','2','8','1','F','6/4/2007');
Insert Into #inspectiondata Values('1','2','Test1','0','10','4','P','6/3/2007');
Insert Into #inspectiondata Values('1','2','Test2','2','8','5','P','6/4/2007');
Insert Into #inspectiondata Values('1','1','Test3','6','8','7','P','6/3/2007');
--For SQL Server 2005
;With CTE
as
(
Select *, Row_Number() OVER(Partition By PARAM Order By [DATETIME] Desc) RowId From #inspectiondata
Where [JOB] = 1 And [SERIAL] = 1
)
Select
[JOB]
,[SERIAL]
,[PARAM]
,[MIN]
,[MAX]
,[VALUE]
,[PF]
,[DATETIME]
From
CTE
Where
RowId = 1
--For SQL Server 2000
Select
Data.[JOB]
,Data.[SERIAL]
,Data.[PARAM]
,Data.[MIN]
,Data.[MAX]
,Data.[VALUE]
,Data.[PF]
,Data.[DATETIME]
From
#inspectiondata Data
Join (
Select
Max([DateTime]) [DateTime]
,PARAM
From
#inspectiondata
Where
[JOB] = 1 And [SERIAL] = 1
Group By
PARAM
) as MaxData On MaxData.[DateTime] = Data.[DateTime] And MaxData.PARAM = Data.PARAM
Where
[JOB] = 1
And [SERIAL] = 1
|||
I was not familiar with CTEs until I read your posting and they seem very easy to read but I cannot get it to compile. The error I get if I name the CTE 'CTE' is 'CTE is not a recognized option'.
Are CTEs available with the Express version of SQL Server?
|||first google result would support thishttp://msdn2.microsoft.com/en-us/library/bb264566(SQL.90).aspx
Give the error of why it won't compile.|||
The reason why it was not working is because I had an alter procedure call at the beginning of the stored procedure.
Everything is working perfectly now.
Thank you for your help.
|||Also, The Alter Procedure call had to be moved to the first line in the sql code.
Thanks again,
Sam
