Date choices

Question

Good afternoon, you guys,

I’m looking to make a iif in sql that if the field FLAG is equal to "And" it brings the date of the column DTDIGIT and store in variable @dataInicio, and when the FLAG for "S" it brings the date of the column EMISSAO and store in variable @dataFIM.

And then I declare these variables, because that _ query_ will become a function.

Can someone help me?

Answer 1

If there is a single row with the FLAG column containing "S" and another row containing the value "E", both meeting the filter defined in the WHERE clause, the code below returns what is requested.

-- código #1
SELECT @dataInicio= case when FLAG = 'E' then DTDIGIT else @dataInicio end,
       @dataFim= case when FLAG = 'S' then EMISSAO else @dataFim end
  from tabela
  where ...;

Example:

-- código #2
CREATE TABLE Config (FLAG char(1), DTDIGIT date, EMISSAO date);

INSERT into Config values
  ('E', '20120303', NULL), ('S', NULL, '20120403');

declare @dataInicio date, @dataFim date;

SELECT @dataInicio= case when FLAG = 'E' then DTDIGIT else @dataInicio end,
       @dataFim= case when FLAG = 'S' then EMISSAO else @dataFim end
  from Config;

SELECT @dataInicio, @dataFim; 

However, if there is more than one row with the same value for the FLAG column, the completion of variables is undetermined.