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',GETDATE(),1)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('A',GETDATE()+1,2)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('B',GETDATE(),1)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('C',GETDATE(),1)
GO
INSERT INTO inspections(C1, C2 , C3 )
VALUES('D',GETDATE(),1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('A',GETDATE()+1,1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('B',GETDATE()+1,1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('C',GETDATE()+1,1)
GO
INSERT INTO delivery(C1, C2 , C3 )
VALUES('D',GETDATE()+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
*/