Grouping by day and date period

2

I have a query from the period from 26 to 30-06 which brings the following result:

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

However, I want it to come this way, that is, I want it to come to the query every day regardless of whether or not it is registered.

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 as follows:

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)
    
asked by anonymous 18.08.2017 / 15:41

3 answers

-1

Renan, this suggestion that first groups the lines that meet the requirements for only after completing the absences.

The issue period is informed early in the code, which avoids the need to modify in the middle of the code to change the issuance period. This makes it easier to use 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 SolDataColumn column is declared as datetime , you can change:

- convert(date, SolDataFechamento, 103) para cast(SolDataFechamento as date)

This makes the WHERE clause sargable .

    
18.08.2017 / 17:00
0

I did this, a query that gets all the tabs first. then comes the 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
    
18.08.2017 / 16:22
0

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

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

Note: I'm considering that your SolDataFechamento column is being written as varchar or nvarchar in the format dd/MM/yyyy .

Here you check the script fault used in the response.

    
18.08.2017 / 16:03