Need to change and include the undernoted
IF...ELSE...END Literal expression in a
CTE- Common table expression.
IF @CINCIDENT <>'ALL'
BEGIN
IF @CINCIDENT = 'OPENED'
SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT >= GETDATE()'
ELSE IF @CINCIDENT = 'CLOSED'
SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT < GETDATE()
AND A.RESOLUTION_STAT_FRONT NOT IN ("BAD","UNHAPPY","UNSATISFACTORY")'
ELSE IF @CINCIDENT = 'UNRESOLVED'
SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT < GETDATE()
AND A.RESOLUTION_STAT_FRONT IN ("BAD","UNHAPPY","UNSATISFACTORY")'
END
CTE -Common Table Expression
;with cte as
(
SELECT a.Brn_name AS BRNNAME,A.PTICK_CODE AS TICKET,
A.LNAME AS LNAME ,A.FNAME AS FNAME ,
A.EMPNO AS EMPNO ,A.COMP_TYPE AS COMP_TYPE,
b.Attribs AS ATTRIB1 , c.Attribs AS ATTRIB2,
d.Attribs AS ATTRIB3 , e.Attribs AS ATTRIB4
FROM Response A
JOIN @REQUEST B ON a.RESPONSE1 = B.Response
JOIN @REQUEST C ON a.RESPONSE2 = C.Response
JOIN @REQUEST D ON a.RESPONSE3 = D.Response
JOIN @REQUEST E ON a.RESPONSE4 = E.Response
WHERE A.COMP_DATE BETWEEN @CFROM AND @CTO
.................(CASE THEN END) STATEMENT..........
)
How do I switch from the IF..ELSE..END
literal expression
to tie into the
CTE expression to complete the statement
Thanks
What I have tried:
On going system developmental challenge.
Checked the Internet to no avail