Problem with SQL query newbie

[tirby]

i got one for you

i have two tables in a data base.
the first one is called inspections it consits of

col1 Well_name vchar
col2 Date shortdate
col3 level numeric
grouped by well name and date is desc
second table is called delivery it consits of

col1 well_name vchar
col2 date shortdate
col3 amount numeric

what i need is to complie this data into the following table or view
col1 well_name group by
col2 date of first inspection
col3 level of first inspection
col4 date of second inspection
col5 level of second inspection
col6 sum of deliveies from the second table beteen the date in col1 and col2

this one as given me nightmares

[csenasa]

Hi,

I was confused reading last line of your question!!!!! (col6 sum of deliveies from the second table beteen the date in col1 and col2
) because col1 contains well_name , So please redefine your question.

But in meanwhile see if you can get something out of the follwing query :)

IF EXISTS(SELECT Name FROM SysObjects WHERE Name = 'inspections ')
BEGIN
DROP TABLE inspections
END
GO
CREATE TABLE inspections
(
C1 VARCHAR(25),
C2 DATETIME,
C3 INT

)
GO
IF EXISTS(SELECT Name FROM SysObjects WHERE Name = 'delivery ')
BEGIN
DROP TABLE delivery
END
GO
CREATE TABLE delivery
(
C1 VARCHAR(25),
C2 DATETIME,
C3 INT

)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('A',GETD ATE(),1)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('A',GETD ATE()+1,2)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('B',GETD ATE(),1)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('C',GETD ATE(),1)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('D',GETD ATE(),1)
GO

INSERT INTO delivery(C1, C2 , C3 )
VALUES('A',GETD ATE()+1,1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('B',GETD ATE()+1,1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('C',GETD ATE()+1,1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('D',GETD ATE()+1,1)
GO

SELECT ins.C1 , ins.C2 , ins.C3 ,del.C2 , del.C3 , SUM(ins.C3) as SumOfdel
FROM delivery del ,
(SELECT ins.C1 , MIN(ins.C2) as MinDate , MAX(ins.C2) as MaxDate --, ins.C3 , del.C2 , del.C3
FROM inspections ins
GROUP BY ins.C1) inspect , inspections ins
WHERE del.C1 = inspect.C1 and ins.C1 = inspect.C1
and del.C2 BETWEEN inspect.MinDate AND inspect.MaxDate
GROUP BY ins.C1 , ins.C2 , ins.C3 ,del.C2 , del.C3
/*
col1 well_name group by
col2 date of first inspection
col3 level of first inspection
col4 date of second inspection
col5 level of second inspection
col6 sum of deliveies from the second table beteen the date in col1 and col2
*/