Grouping by day and by date period

Asked

Viewed 640 times

2

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

  • 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.

  • @Renanbessa how it stores this information?

  • @Renanbessa: the column SolDataFechamento is declared as date or datetime? If declared as datetime, it contains only date or date and time?

3 answers

0

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.

  • Sorack. This query is coming in the same way. cancelled date 27-06-2017 4 29-06-2017 5

  • @Renanbessa the solution served you? Do not forget to accept the answer that helped you so that you can help more people with the same question.

0

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
  • That’s right.. I hadn’t seen your answer.. and yours is with his full Where.. Congratulations!

-1


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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.