By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

Problem with SQL query newbie

P: 1
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
Mar 11 '09 #1
Share this Question
Share on Google+
1 Reply


P: 10
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
*/
Mar 13 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.