SQL - Query Logic

Question

SQL - Query Logic

Navigation

#1 by (1 votes)

0

I am breaking my head with a situation I want to bring a return of a query considering only useful days.

This table has a list of users:

Thistableispopulatedbyusers:

Iwantthequerytoreturnthedaysnotfilledbytheusers,disregardingSaturdaysandSundays,sotheresultofthequerywouldlooklikethis:Querybetween05/09and10/09

As a solution I thought of when creating the user already enter the dates in the fill table, and to have the result I want to simply query the records that the value column is zeroed.

I would like to know if there is a better way to do something like this, since I'm starting with programming.

sql-server

asked by anonymous 22.09.2018 / 09:25

1 answer

PHP code to query MYSQL leaking memory My policy does not work in Angular

score 1 · Accepted Answer

I want the query to return the days unfilled by users, disregarding Saturdays and Sundays,

Jorge, the suggestion is that you store in the Preenchimento table only the events that occurred and that at the time the query is executed, the workday table for the period is set.

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, common table expressions (CTE) were used to implement modular programming, as described in the article "Modular Programming with Table Expressions (CTE)", published in the section " Articles " from Porto SQL .

CTE Datas generates the days in the 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 CTE, DxU , filters the dates to keep only from Monday to Friday in the CTE and also realizes the Cartesian product with the table of existing users.

-- 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;

Please note that if you want working days you will also need a holiday table (local, state and national).

I did not test the code; may contain error (s).