Case Statement - Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Case Statement - Help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-17 : 09:20:21

I need to write a case statement using 5 Business Days including todays date(Business Days being Monday-Friday)

I have a table Customer_Processing using which I need to write a stored procedure and struggling with the select statement

Below is the Case Statement and need help for the case statement part

Select Requestid,ReportNo,AvailableDate = 
Case
When [AvailableDate] is in the next 5 businessdays including today then “Active”
When [AvailableDate] Is in the past then “Not Active – Past”
When [AvailableDate] is in the future(More than 5 business Days including today) then “Not Active – Future”
End
From Customer_Processing
Where (REQNO = @RQNO) and (ReportNo = @RPNO)

Thanks
Jim

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-17 : 09:36:48

Would something like this work for you? It would not take care of holidays that fall on weekdays. If you need to accommodate that, you would need a calendar table.

DECLARE @dt DATETIME;

SET @dt = '20120121';
 
SELECT
	CASE
		WHEN N < 0 THEN  'Not Active – Past'
		WHEN N >= 7 THEN 'Not Active – Future'
		ELSE 'Active'
	END
FROM
( SELECT DATEDIFF(DAY,GETDATE(),@dt) AS N ) S

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-17 : 10:07:22

At this time worried only about weekend holidays
How would i use the case statement you have provided with my scenario

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 10:17:59

You STILL need a calendar table

And Don't forget..every country has their own Holidays

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-17 : 10:24:03

[code]SELECT
Requestid,
ReportNo,
AvailableDate =
CASE
WHEN DATEDIFF(DAY,GETDATE(),[AvailableDate]) < 0 THEN 'Not Active Past'
WHEN DATEDIFF(DAY,GETDATE(),[AvailableDate]) >= 7 THEN 'Not Active Future'
ELSE 'Active'
END
FROM
Customer_Processing
WHERE
(REQNO = @RQNO)
AND (ReportNo = @RPNO);[/code]

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-17 : 10:45:08

Thank You.

quote:

Originally posted by sunitabeck

SELECT
   Requestid,
   ReportNo,
   AvailableDate = 
      CASE 
         WHEN DATEDIFF(DAY,GETDATE(),[AvailableDate]) < 0 THEN  'Not Active Past'
         WHEN DATEDIFF(DAY,GETDATE(),[AvailableDate]) >= 7 THEN 'Not Active Future'
         ELSE 'Active'
       END
FROM
   Customer_Processing
WHERE
   (REQNO = @RQNO)
   AND (ReportNo = @RPNO);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources