latest data for different dates

I have two tables
Well status
Date,wellid,gas
1/5,A,10
1/5,B,20
2/5,A,11
2/5,B,19
1/5,A,10
2/5,B,21
Well test
Date,wellid,gas
20/4,A,10
2/5,A,10
19/4,B,20
the output I want is
Date,Wellid,gas,LastweltestDate,GasfromL
astwelltest
1/5,A,10, 20/4,10
1/5,B,20, 19/4,20
2/5,A,11, 2/5,10
2/5,B,19, 19/4,20
1/5,A,10, 2/5,11
2/5,B,21, 19/4,20
Basically latest well test dates and corresponding gas for that each
day. There is a gap between two welltests. So if a well test happens on
1st and then on 5th, then till 5th the latestwell test date is 1st.
I hope I am clear.
thanks
PradeepPradeep (agarwalp@.eeism.com) writes:
> Well status
> Date,wellid,gas
> 1/5,A,10
> 1/5,B,20
> 2/5,A,11
> 2/5,B,19
> 1/5,A,10
> 2/5,B,21
>
> Well test
> Date,wellid,gas
> 20/4,A,10
> 2/5,A,10
> 19/4,B,20
> the output I want is
> Date,Wellid,gas,LastweltestDate,GasfromL
astwelltest
> 1/5,A,10, 20/4,10
> 1/5,B,20, 19/4,20
> 2/5,A,11, 2/5,10
> 2/5,B,19, 19/4,20
> 1/5,A,10, 2/5,11
> 2/5,B,21, 19/4,20
SELECT s.Date, s.wellid, s.gas, t.date, t.gas
FROM wellstatus s
JOIN welltest t ON s.wellid = t.wellid
AND t.date = (SELECT MAX(t2.date)
FROM welltest t2
WHERE t2.wellid = s.wellid
AND t2.date <= s.date)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Try:
SELECT dt, wellid, gas,
( SELECT t2.gas FROM test t2
WHERE t2.wellid = D.wellid
AND t2.dt = D.last_test )
FROM ( SELECT s1.dt, s1.wellid, s1.gas, MAX( t1.dt )
FROM status s1
INNER JOIN test t1
ON t1.wellid = s1.wellid
AND t1.dt <= s1.dt
GROUP BY s1.dt, s1.wellid, s1.gas
) D ( dt, wellid, gas, last_test ) ;
In the future, please post datatypes, keys, constraints etc. so that others
can better understand the nature of the data.
Anith|||Thanks,
I'll try and let you know.
Thanks a Lotsql