UPDATE ONE COLUMN BASED ON ANOTHER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE ONE COLUMN BASED ON ANOTHER

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-11 : 11:47:24

How would I in SQL UPDATE statement below update [RAG] column to 'C' character automatically only if the following section of UPDATE statement executes successfully and date is enetered.
[StatusCompletedDate]=CASE
WHEN @Status='Completed' THEN ISNULL([StatusCompletedDate],GETDATE()) END

Simply speaking trying to update RAG column value to 'C' automatically if Project is completed. Thank you.

UPDATE [KPITbl] SET [Lead] = @Lead, [WRM] = @WRM, [PTDB] = @PTDB, [PAR] = @PAR, [PM] = @PM,
[RequestingLOB] = @RequestingLOB, [LOB] = @LOB, [StartLocation] = @StartLocation,
[FinishLocation] = @FinishLocation, [Description] = @Description, [ProjectType] = @ProjectType,
[ServerName] = @ServerName, [ServerType] = @ServerType, [DCOorSTANDALONE]=@DCOorSTANDALONE,
[Responsible] = @Responsible, [Status] = @Status, [RAG] = @RAG, [StartDates] = @StartDates,
[EndDates] = @EndDates, [TreeOrDomainImpacted] = @TreeOrDomainImpacted,
[NumOfSites] = @NumOfSites, [NumOfUsers] = @NumOfUsers, [GBdatamoved] = @GBdatamoved,
[GBdatadeleted] = @GBdatadeleted, [NumOfSrvrsAdded] = @NumOfSrvrsAdded,
[NumOfSrvrsDecommed] = @NumOfSrvrsDecommed, [NumOfAppsDeployed] = @NumOfAppsDeployed,
[EUTEngineeringConsult] = @EUTEngineeringConsult, [Comments] = @Comments,
[TimeSpend] = @TimeSpend, [Complexity] = @Complexity, [ECM]=@ECM,
[LastUpdated]=(getdate() ),
[StatusCompletedDate]=CASE
WHEN @Status='Completed' THEN ISNULL([StatusCompletedDate],GETDATE()) END WHERE [TaskID] = @TaskID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 11:52:32

[code]UPDATE [KPITbl] SET [Lead] = @Lead, [WRM] = @WRM, [PTDB] = @PTDB, [PAR] = @PAR, [PM] = @PM,
[RequestingLOB] = @RequestingLOB, [LOB] = @LOB, [StartLocation] = @StartLocation,
[FinishLocation] = @FinishLocation, [Description] = @Description, [ProjectType] = @ProjectType,
[ServerName] = @ServerName, [ServerType] = @ServerType, [DCOorSTANDALONE]=@DCOorSTANDALONE,
[Responsible] = @Responsible, [Status] = @Status, [RAG] = CASE
WHEN @Status='Completed' THEN 'C' END, [StartDates] = @StartDates,
[EndDates] = @EndDates, [TreeOrDomainImpacted] = @TreeOrDomainImpacted,
[NumOfSites] = @NumOfSites, [NumOfUsers] = @NumOfUsers, [GBdatamoved] = @GBdatamoved,
[GBdatadeleted] = @GBdatadeleted, [NumOfSrvrsAdded] = @NumOfSrvrsAdded,
[NumOfSrvrsDecommed] = @NumOfSrvrsDecommed, [NumOfAppsDeployed] = @NumOfAppsDeployed,
[EUTEngineeringConsult] = @EUTEngineeringConsult, [Comments] = @Comments,
[TimeSpend] = @TimeSpend, [Complexity] = @Complexity, [ECM]=@ECM,
[LastUpdated]=(getdate() ),
[StatusCompletedDate]=CASE
WHEN @Status='Completed' THEN ISNULL([StatusCompletedDate],GETDATE()) END WHERE [TaskID] = @TaskID[/code]

what if its not completed? if there's such a condition to handle put an else also in case...

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-11 : 12:00:16

for "Cancelled" and "Completed" RAG woul need to be setup automatically to 'C'. For other statuses that I am pushing from my app like "Active", "Discovery", I don't want anything to be setup automatically. DropDown takes care of them. Thx again for looking into the code!

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-01-11 : 12:50:40

Thank you visakh16 -works fine! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources