Renan, here is the suggestion that first groups the lines that meet the requirements for only after supplementing the absences.
The issuing period is informed at the very beginning of the code, which avoids the need to modify in the middle of the code to change the issuing period. This facilitates the use of the code.
-- código #2
--> informe datas inicial e final (formato dd/mm/aaaa)
declare @dataInicial date, @dataFinal date;
set @dataInicial= convert(date, '26/6/2017', 103);
set @dataFinal= convert(date, '30/6/2017', 103);
with
-- contabiliza cancelamentos
cteCancelados as (
SELECT convert(date, SolDataFechamento, 103) as SolDataFechamento,
count(SolID) as Cancelados
from Solicitacao
where ProID = 4
and SolEstagioID = 110
and convert(date, SolDataFechamento, 103) between @dataInicial and @dataFinal
group by convert(date, SolDataFechamento, 103)
),
-- gera período
cteDatas as (
SELECT @dataInicial as dataPeríodo
union all
SELECT dateadd(day, +1, dataPeríodo)
from cteDatas
where dataPeríodo < @dataFinal
)
SELECT convert(char(10), D.dataPeríodo, 103) as DATA_Cancelado,
coalesce(S.Cancelados, 0) as Cancelados
from cteDatas as D
left join contaSolID as S on S.SolDataFechamento = D.dataPeríodo;
If the Weld Finishing column is declared as datetime, you can change:
- convert(date, SolDataFechamento, 103) para cast(SolDataFechamento as date)
This makes the clause WHERE sargable.
is giving conversion error? I think what you have to do is Where S.Welding Machining BETWEEN '02/24/2017' AND '08/18/2017'.. that is, compare the dates in the MM/dd/yyyy seat format
– Lucas
no friend, it has nothing to do with conversion no. I want you to come to the consultation every day whether or not you have registration.
– Renan Bessa
@Renanbessa how it stores this information?
– Marconi
@Renanbessa: the column
SolDataFechamento
is declared as date or datetime? If declared as datetime, it contains only date or date and time?– José Diz