results based on column value - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » results based on column value

results based on column value

Options

Previous Topic · Next Topic

mguser

Posted: Tuesday, March 18, 2008 4:53:17 PM

Rank: Newbie

Joined: 3/18/2008
Posts: 2
Points: -144

I have a stored procedure which selects results based on some date calculations.

In my table I have a status column and two date fields (Approval Date and Signature Date)
If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.

If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.

How can i achieve this?

Scott Whigham

Posted: Thursday, March 20, 2008 1:25:40 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi there - I deleted your cross post so that we could talk about this in just one location.

You need to use the DATEDIFF() function with CASE. Something like

Code:

DECLARE @NumberOfDays INT
SET @NumberOfDays = 30

SELECT *
FROM MyTable
WHERE @NumberOfDays <=
  CASE
      WHEN Status='Approved' THEN DATEDIFF(dd, ApprovalDate, SignatureDate)
      ELSE DATEDIFF(dd, GETDATE(), SignatureDate)
  ENDp

That's off the top of my head but it should be close-enough-to-right so that you could find the right answer.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.