CODEWITHSUNDEEP
sql-servert-sql
FrenkyB
FrenkyB

Reputation: 7197

Simplyfing update query with case when

I have a query where there is a lot of repetitions of one condition. I am wondering - is it possible to put this condition into variable or something like that, so not the whole sentence is repeated each time?

The part which is repeating:

ISNULL((CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR
     WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END),0)

In query it looks like this:

UPDATE @tblALL_HOURS SET REG_WORK_COST = (CASE WHEN MINUTE_FROM >= 0 AND MINUTE_FROM <= @6h AND MINUTE_TO >= @6h AND MINUTE_TO <= @18h
AND IS_ABSENT = 0
THEN (DATEDIFF(MINUTE, DATEADD(MINUTE, @6h, CAST(CAST(DATE_TO AS DATE) AS DATETIME)), date_to)/60.00) * (BRUTO_HOUR + ISNULL((CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END),0))

WHEN MINUTE_FROM >= 0 AND MINUTE_FROM <= @6h AND MINUTE_TO >= @18h AND MINUTE_TO < @24h
AND IS_ABSENT = 0
THEN 12.00 * (BRUTO_HOUR + ISNULL((CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END),0))

WHEN MINUTE_FROM >= @6h AND MINUTE_FROM <= @18h AND MINUTE_TO >= @6h AND MINUTE_TO <= @18h
AND IS_ABSENT = 0
THEN (DATEDIFF(MINUTE, DATE_FROM, DATE_TO) / 60.00) * (BRUTO_HOUR + ISNULL((CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END),0))

WHEN MINUTE_FROM >= @6h AND MINUTE_FROM <= @18h AND MINUTE_TO >= @18h AND MINUTE_TO < @24h
AND IS_ABSENT = 0
THEN (DATEDIFF(MINUTE, DATE_FROM, DATEADD(MINUTE, @18h, CAST(CAST(DATE_FROM AS DATE) AS DATETIME))) / 60.00) * (BRUTO_HOUR + ISNULL((CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END),0))

END)

Is it possible to have something like this instead:

WHEN MINUTE_FROM >= @6h AND MINUTE_FROM <= @18h AND MINUTE_TO >= @18h AND MINUTE_TO < @24h AND IS_ABSENT = 0 THEN (DATEDIFF(MINUTE, DATE_FROM, DATEADD(MINUTE, @18h, CAST(CAST(DATE_FROM AS DATE) AS DATETIME))) / 60.00) * (BRUTO_HOUR + @myCondition)

Upvotes: 0

Views: 45

Answers (1)

forpas
forpas

Reputation: 164099

Use a simple CTE that returns all the columns of @tblALL_HOURS and the value returned by ISNULL function:

ISNULL(CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END,0)

as a column and then update With the CTE and use that column in place of the condition:

WITH cte AS (
  SELECT *, 
    ISNULL(CASE WHEN IS_HOLIDAY = 1 THEN 2.5 * NETO_HOUR WHEN DAY_IN_WEEK = 'Sunday' THEN NETO_HOUR END,0) AS myCondition 
  FROM @tblALL_HOURS 
)
UPDATE cte 
SET REG_WORK_COST = CASE 
    WHEN MINUTE_FROM >= 0 AND MINUTE_FROM <= @6h AND MINUTE_TO >= @6h AND MINUTE_TO <= @18h AND IS_ABSENT = 0
      THEN (DATEDIFF(MINUTE, DATEADD(MINUTE, @6h, CAST(CAST(DATE_TO AS DATE) AS DATETIME)), date_to)/60.00) * (BRUTO_HOUR + myCondition)
    WHEN MINUTE_FROM >= 0 AND MINUTE_FROM <= @6h AND MINUTE_TO >= @18h AND MINUTE_TO < @24h AND IS_ABSENT = 0
      THEN 12.00 * (BRUTO_HOUR + myCondition)
    WHEN MINUTE_FROM >= @6h AND MINUTE_FROM <= @18h AND MINUTE_TO >= @6h AND MINUTE_TO <= @18h AND IS_ABSENT = 0
      THEN (DATEDIFF(MINUTE, DATE_FROM, DATE_TO) / 60.00) * (BRUTO_HOUR + myCondition)
    WHEN MINUTE_FROM >= @6h AND MINUTE_FROM <= @18h AND MINUTE_TO >= @18h AND MINUTE_TO < @24h AND IS_ABSENT = 0
      THEN (DATEDIFF(MINUTE, DATE_FROM, DATEADD(MINUTE, @18h, CAST(CAST(DATE_FROM AS DATE) AS DATETIME))) / 60.00) * (BRUTO_HOUR + myCondition)
END

Upvotes: 2

Related Questions