时，用大小写简化更新查询

Question

我有一个查询，其中有很多重复的条件。我想知道--能不能把这个条件变成变量或者类似的东西，所以不是每次都会重复整个句子吗？

重复的部分：

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

在查询中，如下所示：

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)

是否有可能有这样的东西代替：

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)

Answer 1

使用一个简单的CTE，它返回@tblALL_HOURS的所有列和ISNULL函数返回的值：

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

作为一列，然后使用CTE更新，并使用该列代替条件：

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