ITmonkeys
asked on
Retrieve last two versions of same id in T-SQL
Am sure there's an easy way around this:
Important thing to note :- Although I'm building the SQL in an dedicated analyzer the report itself is going to be run in EXCEL through Microsoft Query. I only have READ-ONLY access to the database, thanks to my super helpful IT department,
I've got a table that contains ALL my controls including previous versions of that control. The relevant columns are:
ControlNumber - unique identifier for that control in the system
ControlVersion
ControlState - Can be Retired, Edit or Released. The controlversion increments itself when it's in Edit State.
ReleaseDate - The unique date & time it was released.
There can be many instances of the same ControlNumber in the system, each with it's own version number, State, and unique ReleaseDate. !!However!! Version number is NOT Unique to the ControlNumber.
For example :
ControlNumber Controlversion ControlState ReleaseDate
000012 Released 1 2006-12-22 11:22:02.387
000012 Retired 2 2007-01-03 09:28:48.257
000025 Released 2 2007-01-03 10:48:06.697
000025 Retired 3 2007-01-03 10:48:15.710
000027 Released 1 2006-12-06 13:27:35.117
000027 Released 2 2007-02-23 16:45:05.083
000027 Released 3
000027 Retired 3 2007-09-27 10:02:33.513
000028 Released 1 2007-02-23 16:45:05.083
000028 Released 2
000028 Retired 2 2007-09-27 10:02:33.513
000029 Released 1 2007-02-23 16:45:05.083
000029 Released 2 2007-09-27 09:12:43.513
000029 Edit 3 2007-09-27 10:02:33.513
Where the state is EDIT, I want to bring back a report that tells me the current version (in edit state) and the last version, so:
000029 Released 2 2007-09-27 09:12:43.513
000029 Edit 3 2007-09-27 10:02:33.513
Because controlversion is not unique when I run a report on the most current control I use the following:
Select a.ControlNumber, a.ControlVersion, a.ReleaseDate, a.ControlState
from controls a,
(select max(releasedate) as release , ControlNumber from controls group by ControlNumber) b
where a.releasedate = b.release and a.ControlNumber = b.ControlNumber
However, I can't figure out how to modify it to give me the last two releasedates. I've tried subqueries and nested queries. 'Having' doesn't seem to work and I can't use a max in the Where Clause.
HEEEELP!!!!
Important thing to note :- Although I'm building the SQL in an dedicated analyzer the report itself is going to be run in EXCEL through Microsoft Query. I only have READ-ONLY access to the database, thanks to my super helpful IT department,
I've got a table that contains ALL my controls including previous versions of that control. The relevant columns are:
ControlNumber - unique identifier for that control in the system
ControlVersion
ControlState - Can be Retired, Edit or Released. The controlversion increments itself when it's in Edit State.
ReleaseDate - The unique date & time it was released.
There can be many instances of the same ControlNumber in the system, each with it's own version number, State, and unique ReleaseDate. !!However!! Version number is NOT Unique to the ControlNumber.
For example :
ControlNumber Controlversion ControlState ReleaseDate
000012 Released 1 2006-12-22 11:22:02.387
000012 Retired 2 2007-01-03 09:28:48.257
000025 Released 2 2007-01-03 10:48:06.697
000025 Retired 3 2007-01-03 10:48:15.710
000027 Released 1 2006-12-06 13:27:35.117
000027 Released 2 2007-02-23 16:45:05.083
000027 Released 3
000027 Retired 3 2007-09-27 10:02:33.513
000028 Released 1 2007-02-23 16:45:05.083
000028 Released 2
000028 Retired 2 2007-09-27 10:02:33.513
000029 Released 1 2007-02-23 16:45:05.083
000029 Released 2 2007-09-27 09:12:43.513
000029 Edit 3 2007-09-27 10:02:33.513
Where the state is EDIT, I want to bring back a report that tells me the current version (in edit state) and the last version, so:
000029 Released 2 2007-09-27 09:12:43.513
000029 Edit 3 2007-09-27 10:02:33.513
Because controlversion is not unique when I run a report on the most current control I use the following:
Select a.ControlNumber, a.ControlVersion, a.ReleaseDate, a.ControlState
from controls a,
(select max(releasedate) as release , ControlNumber from controls group by ControlNumber) b
where a.releasedate = b.release and a.ControlNumber = b.ControlNumber
However, I can't figure out how to modify it to give me the last two releasedates. I've tried subqueries and nested queries. 'Having' doesn't seem to work and I can't use a max in the Where Clause.
HEEEELP!!!!
actually, it should be
Select a.ControlNumber, a.ControlVersion, a.ReleaseDate, a.ControlState
from controls a,
(select top 2 releasedate as release , ControlNumber from controls group by releasedate, ControlNumber order by releasedate) b
where a.releasedate = b.release and a.ControlNumber = b.ControlNumber
hope this helps ...
Select a.ControlNumber, a.ControlVersion, a.ReleaseDate, a.ControlState
from controls a,
(select top 2 releasedate as release , ControlNumber from controls group by releasedate, ControlNumber order by releasedate) b
where a.releasedate = b.release and a.ControlNumber = b.ControlNumber
hope this helps ...
ASKER
Hi,
Sorry I worded that wrong, what I really want is a script that does the following :
While State = 'Edit'
For each Controlnumber
If count (controlnumber) => 2 Then
Select ControlNumber, max(ControlVersion), ReleaseDate, State
Select ControlNumber, max(ControlVersion)-1, ReleaseDate, State
Else
Select ControlNumber, ControlVersion, ReleaseDate, State
Thanks
Sorry I worded that wrong, what I really want is a script that does the following :
While State = 'Edit'
For each Controlnumber
If count (controlnumber) => 2 Then
Select ControlNumber, max(ControlVersion), ReleaseDate, State
Select ControlNumber, max(ControlVersion)-1, ReleaseDate, State
Else
Select ControlNumber, ControlVersion, ReleaseDate, State
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bit of modification but it works well, thanks a lot!!
Glad I could be of any help and thanks for the grade !
Select a.ControlNumber, a.ControlVersion, a.ReleaseDate, a.ControlState
from controls a,
(select top 2 releasedate as release , ControlNumber from controls group by ControlNumber order by releasedate) b
where a.releasedate = b.release and a.ControlNumber = b.ControlNumber
hope it helps ...