How to write SQL Select statement for this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to write SQL Select statement for this?

Author

Topic

anandchinnappan
Starting Member

7 Posts

Posted - 2010-03-15 : 14:22:07

Dear Experts,

My view has the following three columns.

1, Start Date
2. End Date
3. Balance.

I need an SQL statement which has to check if EndDate is less than the Current date and balance is above zero. If so, it assumes the last date of the job is already over, outstanding materials not yet been returned. Hence it has to return 'Pending'. If EndDate is greater than current date, it means final date for completion of the process is yet to come. So the task is in progress, hence it has to return 'Progress'. If the balance is Zero, all materials connected with the task have been received either as a FG or as Raw Materials. So it has to return, 'Completed'. I need a singe SQL Statement for this.

Please help me.

Thanks in advance.

Regards
Anand

Sachin.Nand

2937 Posts

Posted - 2010-03-15 : 14:31:15

You can use case statements.
example
select case when enddate<currentdate and balance >0 then 'Pending' else ur next case condition & so on.

PBUH

Ehan
Starting Member

19 Posts

Posted - 2010-03-15 : 14:34:27

something like this
SELECT
StartDate,
EndDate,
Balance,
Status = CASE WHEN EndDate < GETDATE() AND Balance > 0 THEN 'Pending'
WHEN EndDate > GETDATE() THEN 'Progress'
WHEN Balance = 0 THEN 'Completed' END
FROM tab

anandchinnappan
Starting Member

7 Posts

Posted - 2010-03-15 : 14:58:46

thanks experts.
I got the solution through ur answer.

Regards
Anand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources