Grouping by day and by date period

Question

I have a query of the period 26 a 30-06 that brings the following result:

Data_Cancelado     Cancelados
27-06-2017             4
29-06-2017             5

However, I want it to come this way, ie,.

Data_Cancelado     Cancelados
26-06-2017             0
27-06-2017             4
28-06-2017             0
29-06-2017             5
30-06-2017             0

The query is the following:

select
CONVERT(DATE,S.SolDataFechamento,103) [DATA_Cancelado],
COUNT (S.SolID) [Cancelados]
from Solicitacao S
where S.ProID = 4 and S.SolEstagioID = 110  and S.SolDataFechamento BETWEEN '26-06-2017' AND '30-06-2017' 
group by CONVERT(DATE,S.SolDataFechamento,103)

Answer 1

You can use the clause WITH to isolate the days and use them in a LEFT JOIN with his query current:

WITH dias AS(
  SELECT CAST('2017-06-26' AS DATE) AS dia
  UNION ALL
  SELECT DATEADD(DAY, 1, d.dia)
    FROM dias d
   WHERE d.dia < '2017-06-30'
)
SELECT CONVERT(VARCHAR, d.dia, 103) AS data_cancelado,
       COUNT(s.solid) AS cancelados
  FROM dias d
       LEFT JOIN solicitacao s ON CONVERT(DATE, s.soldatafechamento, 103) = d.dia
                              AND s.proid = 4
                              AND s.solestagioid = 110
 GROUP BY d.dia
OPTION (MAXRECURSION 0);

Observing: I’m considering that your column SolDataFechamento is being recorded as varchar or nvarchar in format dd/MM/yyyy.

Here you check the script complete used in the response.

Answer 2

I did so, a query that receives all tabs first. then comes select with a sum case

DECLARE @StartDateTime DATE
DECLARE @EndDateTime DATE

SET @StartDateTime = '01/05/2017'
SET @EndDateTime = '01/06/2017';

WITH DateRange(DateData) AS 
(
    SELECT @StartDateTime as Date
    UNION ALL
    SELECT DATEADD(d,1,DateData)
    FROM DateRange 
    WHERE DateData < @EndDateTime
)
SELECT DateData,
    sum(case when CONVERT(DATE,S.SolDataFechamento,103) = CONVERT(DATE,DateData,103) then COUNT(s.solid) else 0 end) as qtd
FROM DateRange, Solicitacao S
group by DateData
OPTION (MAXRECURSION 0)
GO

Answer 3

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.