SQL - Logica de Consulta

Question

I’m cracking my head with a situation I want to bring back a consultation considering only useful days.

This table has the list of users:

This table is filled by users:

I want the query return me the days not filled by users, disregarding Saturdays and Sundays, so the result of the consultation would be like this:Consultation between 05/09 and 10/09

As a solution I thought when creating the user already enter the dates in the fill table, and to have the result I just want to query the record that the value column is reset.

I wonder if there’s a better way to do something like this, because I’m starting with programming.

Answer 1

I want the query return me the days not filled by users, disregarding Saturdays and Sundays,

Jorge, the suggestion is that you store in the table Preenchimento only events that occurred and that at the time of execution of the query is that the table of working days of the period is mounted.

For example:

-- código #1
declare @DataInicial date, @DataFinal date;
set dateformat dmy;
-- informe as datas no formato dd/mm/aaaa
set @DataInicial= cast('5/9/2018' as date);
set @DataFinal= cast('20/9/2018' as date);

--
set datefirst 1;  -- semana inicia na segunda-feira
with 
Datas as (
SELECT @DataInicial as Dia
union all 
SELECT dateadd(day, +1, Dia)
  from Datas 
  where Dia < @DataFinal
),
DxU as (
SELECT U.Vendedor, D.Dia
  from Datas as D
       cross join Usuário as U
  where datepart(dw, D.Dia) <= 5
        -- and Dia não é feriado
)
SELECT DU.Vendedor, DU.Dia
  from DxU as DU
  where not exists (SELECT * 
                      from Preenchimento as P
                      where P.Dia = DU.Dia 
                            and P.Vendedor = DU.Vendedor);

To facilitate the understanding and maintenance of the code were used CTE (common table Expressions) to implement modular programming, as stated in the article "Modular programming with table expressions (CTE)”.

THE CTE Datas generates days in period. You can follow this by isolating the first part of code #1:

-- código #1 parte 1
declare @DataInicial date, @DataFinal date;
set dateformat dmy;
-- informe as datas no formato dd/mm/aaaa
set @DataInicial= cast('5/9/2018' as date);
set @DataFinal= cast('20/9/2018' as date);

--
with 
Datas as (
SELECT @DataInicial as Dia
union all 
SELECT dateadd(day, +1, Dia)
  from Datas 
  where Dia < @DataFinal
)
SELECT Dia
  from Datas; 

The second TEC, DxU, filters the dates to keep only from Monday to Friday in CTE and also performs the Cartesian product with the existing user table.

-- código #1 parte 2
declare @DataInicial date, @DataFinal date;
set dateformat dmy;
-- informe as datas no formato dd/mm/aaaa
set @DataInicial= cast('5/9/2018' as date);
set @DataFinal= cast('20/9/2018' as date);

--
set datefirst 1;  -- semana inicia na segunda-feira
with 
Datas as (
SELECT @DataInicial as Dia
union all 
SELECT dateadd(day, +1, Dia)
  from Datas 
  where Dia < @DataFinal
),
DxU as (
SELECT U.Vendedor, D.Dia
  from Datas as D
       cross join Usuário as U
  where datepart(dw, D.Dia) <= 5
        -- and Dia não é feriado
)
SELECT Vendedor, Dia
  from DxU;

Note that if you want working days you will also need a table of holidays (local, state and national). You can find solutions to implement it in the article "Business day operations on SQL Server”.

I have not tested the code; it may contain an error(s).