New and old values
-
Hi all,
I hoping someone in the community can help me out on this. I have a table that is structured as such:
id, project_code, launch_date, rsn_code, entry_date, update_date
So I have:
1, 24, 12/03/03, xc, 10/11/03, <<null>>
2, 24, 11/28/03, gr, 10/13/03, <<null>>
I want to create a view that will show the new launch date and the previous one. Like this:
24, 11/28/03, 12/03/03
Any help provided would be greatly appreciated.
-
Here is a quick (maybe inefficient) possibility...
Select TheLatest.project_code, Previous.launch_date , TheLatest.launch_date
From (Select project_code, Max(launch_date) as launch_date
from [Table] A Group by project_code) TheLatest
join (Select project_code, Max(launch_date) as Previous
from [Table] B
Where launch_date < (
Select Max(launch_date)
from [Table] C
Where C.project_code = B.project_code
)
Group by project_code) Previous
Once you understand the BITs, all the pieces come together
-
Thomas,
Thanks for your prompt response. I took your query and filled it in with my tables, but it is erroring out.
Select TheLatest.project_code, Previous.launch_date_before , TheLatest.launch_date
From (Select project_code, Max(launch_date) as launch_date
from project_launch_hist A
Group by project_code) TheLatest
join (Select b.project_code, Max(launch_date) as launch_date_before
from project_launch_hist B
Where b.launch_date < (Select Max(c.launch_date)
from project_launch_hist C
Where C.project_code = B.project_code)
Group by b.project_code) PreviousError: Line 10: Incorrect syntax near 'Previous'.
-
Sorry about that, I did not take the time to make the sample table(s)....
anyway...
Select TheLatest.project_code, Previous.launch_date as Previous, TheLatest.launch_date as Latest
From (Select project_code, Max(launch_date) as launch_date
from [Test] A Group by project_code) TheLatest
join (Select project_code, Max(launch_date) as launch_date
from [Test] B
Where launch_date < (
Select Max(launch_date)
from [Test] C
Where C.project_code = B.project_code
)
Group by project_code) Previous
On TheLatest.project_code = Previous.project_code
Once you understand the BITs, all the pieces come together -
of course. I should have caught that. Thanks for your help.
-
SELECT
Q.ProjectCode
, Q.LastLaunch
, (Select Top 1 Q2.LaunchDte
From Launches Q2
Where Q2.LaunchDte < Q.LastLaunch
AND
Q2.ProjectCode = Q.ProjectCode
Order by LaunchDte Desc) As PrevLaunch
From(
Select
ProjectCode
, Max(LaunchDte) as LastLaunch
From
Launches
Group by
ProjectCode )Q
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply