conditional SQL query based on the column value in table

Question

conditional SQL query based on the column value in table

anu-448545

SSC Eights!

Points: 843
More actions
March 18, 2008 at 3:47 pm

#183509

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

You need to use a case statement in you where clause. This queries assumes that you want the number of days to be the same. You can incorporate the logic to suit your needs.

SELECT *

FROM MyTable

WHERE CASE WHEN [Status] = 'APPROVED' THEN DATEDIFF(dd,signaturedate,approvaldate)

ELSE

DATEDIFF(dd,signaturedate,GETDATE())

END <= 30

My blog: http://jahaines.blogspot.com

anu-448545 SSC Eights! Points: 843 More actions · Answer 2

I already use a case statement in my stored proc.Can i nest two case statements.

Also, I am not passing status as a parameter.

When a particular row is found it shouls check if the column stataus has a value of 'Approved' and do the calculation accordingly.

I have added an attachemnt that explains what my query looks like.

anu-448545 SSC Eights! Points: 843 More actions · Answer 3

thanks I got that working

I just changed my case statement as :

CASE

WHEN (SELECT qq.LogEntryStatusID

FROM Transaction2821Journals zz INNER JOIN

Journals qq ON zz.LogEntryID=qq.LogEntryID INNER JOIN

LogEntryStatus ll ON qq.LogEntryStatusID = ll.LogEntryStatusID

WHEREzz.Transaction2821ID = j2821.Transaction2821ID

AND qq.JournalTypeID = 8

AND (zz.TransactionType IN ('Replacement','1035 Exchange'))

AND (zz.ProductType IN ('VA - INDIVIDUAL','GVA - PARTICIPANT (RR Recommended Investment Selection)'))

AND zz.DateClientSignature >= @DateClientSignatureFrom

AND zz.DateClientSignature <= @DateClientSignatureTo) = @status

AND @operation = '>' AND (DATEDIFF(day, DateClientSignature, DateForwarded) > @ApprovalTimeFrame) THEN 'True'

WHEN @operation = '-1' THEN 'True'

ELSE 'False'

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

if {approval date} is null before {status} is set to 'approved', just use isnull():

and datediff( day,

isnull({approval date},getdate()),

{signature date}) <= {@number of days}