Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Only 1st part of case select working?

Status
Not open for further replies.

straybullet

IS-IT--Management
Jun 5, 2003
593
US
Have the following as part of a larger query (Ive added line breaks to make it easier to read):
Code:
       (CASE WHEN DATEDIFF(WEEK, 

(SELECT AMDEVENTS1.CompletionTime FROM AMDEVENTS AMDEVENTS1 WHERE AMDEVENTS.EvRefNum = AMDEVENTS1.EvRefNum 

AND AMDEVENTS1.EvtType = 'PreConstr' AND AMDEVENTS1.TaskDesc = 'All permits in hand'), GETDATE()) >= 0 THEN 


(Select ResNum from AMDEVENTS AMDEVENTS1 WHERE AMDEVENTS1.EvtType = 'Constr' 

AND AMDEVENTS.EvRefNum = AMDEVENTS1.EvRefNum AND RTRIM(AMDEVENTS1.TaskDesc) = 

RTRIM((CASE WHEN CHARINDEX(' - Completed',AMDEVTDATA.udb_status) > 0 THEN LEFT(AMDEVTDATA.udb_status, CHARINDEX(' - 

Completed',AMDEVTDATA.udb_status)) ELSE AMDEVTDATA.udb_status END))) 


ELSE 


(Select ResNum from AMDEVENTS AMDEVENTS1 WHERE AMDEVENTS1.EvtType = 'PreConstr' 

AND AMDEVENTS.EvRefNum = AMDEVENTS1.EvRefNum AND RTRIM(AMDEVENTS1.TaskDesc) = 

RTRIM((CASE WHEN CHARINDEX(' - Completed',AMDEVTDATA.udb_status) > 0 THEN LEFT(AMDEVTDATA.udb_status, CHARINDEX(' - 

Completed',AMDEVTDATA.udb_status)) ELSE AMDEVTDATA.udb_status END))) END) AS [Sort2]

The problem is that only those with an EvtType of 'Constr' are retrieving the ResNum (a unique identifier). Is there a different way of defining [Sort2] so that it pulls the information properly for both EvtTypes?

Let them hate - so long as they fear... Lucius Accius
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top