| Author |
 Topic  |
|
|
jberberette
Starting Member
1 Posts |
 Posted - 02/03/2010 : 16:32:30
|
Hi Everyone,
I've only been using SQL for 2 days now and have been tasked to create a report for our agency. I can get the requested data with no issues other that the fact that I cannot sort it properly.
In a nutshell we use a proprietary piece of software to track missions entered by our watch standers. The system basically the system creates a value (MissionID) when the mission is created...then if a change is made to the mission the system creates a new record and changes the [MissionID] value to the next available increment number.....then the original mission number is tracked via the [PrevMissionID] field.
example: MissionID=1000
an update to the mission is changed and now the MissionID=1001 and the PrevMissionID=1000 and so on.....
Now here's the weird part....when the mission is marked "Completed" they change the [MissionID] field back to the original MissionID and change the PrevMissionID to "0". I'm not sure why they decided to do that but it really screws up data sorts.
example: original mission entered....MissionID=1000 ---- a change is made so now.....MissionID=1001 & PrevMissionID=1000 ---- once the mission is marked complete.....MissionID=1000 & PrevMissionID=0
What I need to do in my query is basically an "If Then" type statement that basically checks to see if the [PrevMissionID]=0 if so changes the value to be equal with the [MissionID] value.
Basically if [PrevMissionID]=0 then replace [PrevMissionID] with the [missionID]. So If the PrevMissionID=0 and the MissionID=1000 then I want the PrevMissionID now equals 1000.
I hope this isn't to confusing and I truly appreciate your time.
Jack |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/03/2010 : 17:12:39
|
select
MissionId,
case
when PrevMissionId=0 then MissionId
else PrevMissionId
end as PrevMissionId,
other columns...
from table
where ...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/04/2010 : 03:30:47
|
you need to update in table? then just do
UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0
|
|
|
|
tony_sql
Starting Member
1 Posts |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/05/2010 : 04:12:37
|
quote:
Originally posted by visakh16
you need to update in table? then just do
UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0
Nope, OP did not want to update.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/05/2010 : 04:13:15
|
quote:
Originally posted by tony_sql
Some help here
<edit: unwanted link removed>
I can see nothing there to solve OP's problem.
No, you're never too old to Yak'n'Roll if you're too young to die. |
Edited by - webfred on 02/05/2010 04:39:05 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/05/2010 : 04:27:20
|
| WebFred you may want to edit your quote to remove the link to not further advertise them (and in case the MODs delete the original post) |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/05/2010 : 04:39:45
|
Done - thank you 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/05/2010 : 08:41:55
|
quote:
Originally posted by webfred
quote:
Originally posted by visakh16
you need to update in table? then just do
UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0
Nope, OP did not want to update.
No, you're never too old to Yak'n'Roll if you're too young to die.
Basically if [PrevMissionID]=0 then replace [PrevMissionID] with the [missionID]. So If the PrevMissionID=0 and the MissionID=1000 then I want the PrevMissionID now equals 1000.
sounded like op needed to update value of [PrevMissionID] with the [missionID] when PrevMissionID=0 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 02/05/2010 : 08:50:23
|
quote:
...have been tasked to create a report for our agency...
...when the mission is marked "Completed" they change the [MissionID] field back to the original MissionID and change the PrevMissionID to "0". I'm not sure why they decided to do that but it really screws up data sorts...
Maybe I am wrong but that sounds to me that the OP should not do any update if he wants the application to run properly in the future...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/05/2010 : 08:52:20
|
quote:
Originally posted by webfred
quote:
...have been tasked to create a report for our agency...
...when the mission is marked "Completed" they change the [MissionID] field back to the original MissionID and change the PrevMissionID to "0". I'm not sure why they decided to do that but it really screws up data sorts...
Maybe I am wrong but that sounds to me that the OP should not do any update if he wants the application to run properly in the future...
No, you're never too old to Yak'n'Roll if you're too young to die.
No idea
Let us keep guessing until OP comes back with explanation |
|
|
| |
Topic |
|
Replacing a value in a SELECT statement
