'where' 中的 SQL 开关/大小写

Question

我现在有这个查询

DECLARE @user uniqueidentifier
, @day_to_find int
, @date DATETIME = getdate()
SELECT
@user = CAST(Value AS uniqueidentifier)
FROM BookSoreConfiguration.dbo.Config(NOLOCK)
WHERE Key = 'SuperUser.Id'
SELECT
@day_to_find = CAST(Value AS int)
FROM BookSoreConfiguration.dbo.Config(NOLOCK)
WHERE Key = 'Returns.MaxDay'
--INSERT INTO...... //Part not copied for cleaning
SELECT DISTINCT
@user,
@date,
bd.shopping_code,
bd.cod_gender,
bd.book_number,
'03',
'01',
'11',
@user,
@user,
GETDATE(),
GETDATE(),
ean_code,
15,
bd.cod_variation,
FROM bdo.BOOK_DATA bd WITH (NOLOCK)
-- various inner joins...
WHERE 
btw.num_catalog IS NOT NULL
AND(
(
AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= @day_to_find
OR (cod_catalog = '04'
AND bd.cod_category NOT IN ('children', 'used', 'discounted'))
AND bd.cod_state= '00'
AND bd.cod_variation = '00'
AND NOT EXISTS (SELECT TOP 1
*
FROM BOOK_RETURNS br (NOLOCK)
WHERE br.code = bd.shopping_code
AND br.cod_category = bd.cod_category
AND br.book_number = bd.book_number
AND br.cod_request_returns = 1
AND br.num_progr_bd = bd.prog
AND br.cod_elaboration = '103')
GO

我需要修改这部分:

AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= @day_to_find

如果bd.shopping_code为"B41"， "B74"， "BT34"and必须变成

AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= 30

在所有其他情况下，and将保持

AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= @day_to_find

是否可以通过开关箱来管理这两种情况，或者有其他可能的方法?

编辑2022/05/13如果我使用UNION会怎样?

DECLARE @user uniqueidentifier
, @day_to_find int
, @date DATETIME = getdate()
SELECT
@user = CAST(Value AS uniqueidentifier)
FROM BookSoreConfiguration.dbo.Config(NOLOCK)
WHERE Key = 'SuperUser.Id'
SELECT
@day_to_find = CAST(Value AS int)
FROM BookSoreConfiguration.dbo.Config(NOLOCK)
WHERE Key = 'Returns.MaxDay'
--INSERT INTO...... //Part not copied for cleaning
SELECT DISTINCT
@user,
@date,
bd.shopping_code,
bd.cod_gender,
bd.book_number,
'03',
'01',
'11',
@user,
@user,
GETDATE(),
GETDATE(),
ean_code,
15,
bd.cod_variation,
FROM bdo.BOOK_DATA bd WITH (NOLOCK)
-- various inner joins...
WHERE
bd.shopping_code NOT IN ('B41', 'B74', 'BT34')
AND btw.num_catalog IS NOT NULL
AND(
(
AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= @day_to_find
OR (cod_catalog = '04'
AND bd.cod_category NOT IN ('children', 'used', 'discounted'))
AND bd.cod_state= '00'
AND bd.cod_variation = '00'
AND NOT EXISTS (SELECT TOP 1
*
FROM BOOK_RETURNS br (NOLOCK)
WHERE br.code = bd.shopping_code
AND br.cod_category = bd.cod_category
AND br.book_number = bd.book_number
AND br.cod_request_returns = 1
AND br.num_progr_bd = bd.prog
AND br.cod_elaboration = '103')

UNION
SELECT DISTINCT
@user,
@date,
bd.shopping_code,
bd.cod_gender,
bd.book_number,
'03',
'01',
'11',
@user,
@user,
GETDATE(),
GETDATE(),
ean_code,
15,
bd.cod_variation,
FROM bdo.BOOK_DATA bd WITH (NOLOCK)
-- various inner joins...
WHERE
bd.shopping_code IN ('B41', 'B74', 'BT34')
AND btw.num_catalog IS NOT NULL
AND(
(
AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= 30
OR (cod_catalog = '04'
AND bd.cod_category NOT IN ('children', 'used', 'discounted'))
AND bd.cod_state= '00'
AND bd.cod_variation = '00'
AND NOT EXISTS (SELECT TOP 1
*
FROM BOOK_RETURNS br (NOLOCK)
WHERE br.code = bd.shopping_code
AND br.cod_category = bd.cod_category
AND br.book_number = bd.book_number
AND br.cod_request_returns = 1
AND br.num_progr_bd = bd.prog
AND br.cod_elaboration = '103')
GO

Answer 1

由于左侧部分保持不变，只有值可以不同，您可以尝试以下方法(CASE '返回'右侧部分的值):

AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= 
CASE
WHEN bd.shopping_code IN ('B41', 'B74', 'BT34') THEN 30
ELSE @day_to_find
END

Answer 2

WHERE
(
bd.shopping_code IN ('B41', 'B74', 'BT34') AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= 30
OR
DATEDIFF(DAY, btw.sell_date, GETDATE()) >= @day_to_find
)
AND ...