How can I eliminate the duplicate subqueries?

How can I eliminate the duplicate subqueries?

SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU )

from the following query below:

Declare @OPV int
Declare @Today_JD int
Declare @DayOfWeek int
SET  @Today_JD =  dbo.date2jde (convert(varchar(10),getdate(),111)) --today in Julian   
SET  @DayOfWeek = DATEPART(dw, dbo.jde2date(@Today_JD))    -- Day of week (1 to 7)

UPDATE TESTDTA.F4211  SET SDVR03 = 1
FROM TESTDTA.F4211

WHERE sdkcoo = 30001 and sdsrp1 = 'ITO' and sdsobk > 0 and  sdvend > '' and 
      sddoco = 2606544 and sdlnid = 27000 and 

    CASE 
        WHEN ((SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU ) >= @Today_JD) AND (@DayOfWeek = 2) and (SDVEND= 1010 OR SDVEND=4010)) THEN 1
            WHEN ((SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU ) >= @Today_JD) AND (@DayOfWeek = 3) and (SDVE开发者_如何学运维ND= 110051 OR SDVEND=110052 OR SDVEND = 2010)) THEN 1            
            WHEN ((SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU ) >= @Today_JD) AND (@DayOfWeek = 4) and (SDVEND= 50001))   THEN 1
            WHEN ((SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU ) >= @Today_JD) AND (@DayOfWeek = 5) and (SDVEND= 110034))  THEN 1    
        ELSE 0      
    END  = 1

Simple really, remove it from the CASE clauses and put directly into the WHERE clause. If it is not true, ALL branches of the CASE statement goes to FALSE -> it ends up with CASE ELSE -> 0 which won't match the = 1 at the end. So just take it outside of the case.

Declare @OPV int
Declare @Today_JD int
Declare @DayOfWeek int
SET  @Today_JD =  dbo.date2jde (convert(varchar(10),getdate(),111)) --today in Julian   
SET  @DayOfWeek = DATEPART(dw, dbo.jde2date(@Today_JD))    -- Day of week (1 to 7)

UPDATE TESTDTA.F4211  SET SDVR03 = 1
FROM TESTDTA.F4211
WHERE sdkcoo = 30001 and sdsrp1 = 'ITO' and sdsobk > 0 and  sdvend > '' and 
      sddoco = 2606544 and sdlnid = 27000 and 
      (SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU ) >= @Today_JD)
AND
    CASE 
        WHEN (@DayOfWeek = 2) and SDVEND IN (1010,4010) THEN 1
        WHEN (@DayOfWeek = 3) and SDVEND IN (110051,110052,2010) THEN 1            
        WHEN (@DayOfWeek = 4) and SDVEND= 50001)   THEN 1
        WHEN (@DayOfWeek = 5) and SDVEND= 110034)  THEN 1    
        ELSE 0      
    END  = 1

I would go further and drop the case statement completely

Declare @OPV int
Declare @Today_JD int
Declare @DayOfWeek int
SET  @Today_JD =  dbo.date2jde (convert(varchar(10),getdate(),111)) --today in Julian   
SET  @DayOfWeek = DATEPART(dw, dbo.jde2date(@Today_JD))    -- Day of week (1 to 7)

UPDATE TESTDTA.F4211  SET SDVR03 = 1
FROM TESTDTA.F4211
WHERE sdkcoo = 30001 and sdsrp1 = 'ITO' and sdsobk > 0 and  sdvend > '' and 
      sddoco = 2606544 and sdlnid = 27000 and 
      ((SDPDDJ - (SELECT IBOPV FROM TESTDTA.F4102 WHERE IBLITM = SDLITM and IBMCU = SDMCU ) >= @Today_JD)
AND (
    ((@DayOfWeek = 2) and SDVEND IN (1010,4010)) OR
    ((@DayOfWeek = 3) and SDVEND IN (110051,110052,2010)) OR
    ((@DayOfWeek = 4) and (SDVEND= 50001)) OR
    ((@DayOfWeek = 5) and (SDVEND= 110034))
    )

You could use SQL Servers update ... from syntax, like:

UPDATE  t1
SET     SDVR03 = 1
FROM    TESTDTA.F4211 t1
JOIN    TESTDTA.F4102 t2
ON      IBLITM = SDLITM and IBMCU = SDMCU
WHERE   ...