SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Replacing a value in a SELECT statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jberberette
Starting Member

1 Posts

Posted - 02/03/2010 :  16:32:30  Show Profile  Reply with Quote
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
8679 Posts

Posted - 02/03/2010 :  17:12:39  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/04/2010 :  03:30:47  Show Profile  Reply with Quote
you need to update in table? then just do

UPDATE table
SET PrevMissionId=MissionId
WHERE PrevMissionId=0
Go to Top of Page

tony_sql
Starting Member

1 Posts

Posted - 02/04/2010 :  11:08:44  Show Profile  Visit tony_sql's Homepage  Reply with Quote
Some help here

http://www.yourcodefactory.com/forum/topics.aspx?ForumID=22
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 02/05/2010 :  04:12:37  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 02/05/2010 :  04:13:15  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/05/2010 :  04:27:20  Show Profile  Reply with Quote
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)
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 02/05/2010 :  04:39:45  Show Profile  Visit webfred's Homepage  Reply with Quote
Done - thank you


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/05/2010 :  08:41:55  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 02/05/2010 :  08:50:23  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/05/2010 :  08:52:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000