How can I control several columns at the same time?-CodePudding

I have the following table and I want to control the selection with the DATE_FORM variable.

Why does my T-SQL code not work? Does someone have an idea?

-- @DATE_FORM = 1 select a particular INFO-DATE INTERVALL
-- @DATE_FORM = 2 select a particular ORDER-DATE INTERVALL
-- @DATE_FORM = 3 select a particular SHIPPING INTERVALL

DECLARE @DATE_FORM int = 3,
        @FROM smalldatetime  = '2022-10-05 00:00:00',
        @UNTIL smalldatetime = '2022-10-06 00:00:00';

SELECT *
FROM [AdventureWorksDW2019].[dbo].[Table_2]
WHERE 1 = 1
  AND (CASE WHEN @DATE_FORM = 1 THEN INFO_DATE 
            WHEN @DATE_FORM = 2 THEN ORDER_DATE END) BETWEEN @FROM AND @UNTIL
  AND ((CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_START END) BETWEEN @FROM AND @UNTIL
        AND (CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_END END) BETWEEN @FROM AND @UNTIL)

CodePudding user response：

It doesn't work because your cases doesn't allow your conditions to ever be met. For instance, let's say @Date_Form = 3, what will happen to this condition?:

(CASE WHEN @DATE_FORM = 1 THEN INFO_DATE 
      WHEN @DATE_FORM = 2 THEN ORDER_DATE 
   END) between @FROM and @UNTIL

Since @DATE_FORM = 3 is not handled in the Case it would bascially result in:

null between @From AND @UNTIL

which will result in false.. I would (for simplicity) remove the cases completely:

SELECT *
FROM [AdventureWorksDW2019].[dbo].[Table_2]
WHERE (@DATE_FORM = 1 
      AND INFO_DATE BETWEEN @FROM AND @UNTIL)
OR    (@DATE_FORM = 2 
      AND ORDER_DATE BETWEEN @FROM AND @UNTIL)
OR    (@DATE_FORM = 3 
       AND SHIPPING_DATE_START BETWEEN @FROM AND @UNTIL 
       AND SHIPPING_DATE_END BETWEEN @FROM AND @UNTIL)

CodePudding user response：

The first condition:

AND (CASE WHEN @DATE_FORM = 1 THEN INFO_DATE WHEN @DATE_FORM = 2   THEN ORDER_DATE END) between @FROM and @UNTIL`

When @DATE_FORM is 3 that will just be AND NULL between @FROM and @UNTIL which will always be FALSE.

Since that is always FALSE when @Date_Form = 3 then you likely want to switch your AND to an OR:

SELECT *
FROM [AdventureWorksDW2019].[dbo].[Table_2]
WHERE 
   (CASE WHEN @DATE_FORM = 1   THEN INFO_DATE 
            WHEN @DATE_FORM = 2   THEN ORDER_DATE END) between @FROM and @UNTIL
  OR ( 
        (CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_START END) between @FROM and @UNTIL
        AND (CASE WHEN @DATE_FORM = 3 THEN SHIPPING_DATE_END END) between @FROM and @UNTIL
     )