straybullet
IS-IT--Management
Have the following as part of a larger query (Ive added line breaks to make it easier to read):
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
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