Making a view

My brain is locking up again. I would like to make a view with the
Tech_ID and the most recent, non-null value of
TechWOProduct_WorksheetNumber. Thanks for any help -John
CREATE TABLE [tblTech] (
[Tech_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
)
CREATE TABLE [tblWO] (
[WO_ID] [int] IDENTITY (1, 1) NOT NULL ,
[WO_Date] [datetime] NULL
)
CREATE TABLE [tblTechWO] (
[TechWO_ID] [int] IDENTITY (1, 1) NOT NULL ,
[WO_ID] [int] NOT NULL ,
[Tech_ID] [int] NOT NULL
)
CREATE TABLE [tblTechWOProduct] (
[TechWOProduct_ID] [int] IDENTITY (1, 1) NOT NULL ,
[TechWO_ID] [int] NOT NULL ,
[TechWOProduct_Date] [datetime] NOT NULL ,
[TechWOProduct_WorksheetNumber] [int] NULL
)Hi, John
Besides DDL, it would have been useful if you would have provided
sample data and expected results. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Your DDL is incomplete: it has no primary keys, no foreign keys and no
unique keys. If the DDL would have been complete we would know if it's
possible to have two rows in the tblTechWOProduct table with the same
TechWO_ID and TechWOProduct_Date. Because the DDL is incomplete, i must
ask you: is the above situation possible?
Anyway, assuming that the answer to the previous question is "no" (you
should create a unique constraint to ensure this), try the following
(not tested):
CREATE VIEW FirstView
AS
SELECT t.Tech_ID, p.TechWOProduct_Date, p.TechWOProduct_WorksheetNumber
FROM tblTechWO t INNER JOIN tblTechWOProduct p ON
t.TechWO_ID=p.TechWO_ID
WHERE TechWOProduct_WorksheetNumber IS NOT NULL
GO
CREATE VIEW YourView
AS
SELECT v.* FROM FirstView v
INNER JOIN (
SELECT Tech_ID, MAX(TechWOProduct_Date) AS MaxDate
FROM FirstView GROUP BY Tech_ID
) x ON v.Tech_ID=x.Tech_ID AND v.TechWOProduct_Date=x.MaxDate
Razvan