Add WITH query inside another query - SQL Server

Question

Guys, how to add the query below as a subconsulta.

Consultation with WITH:

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

Another Query, where this is to put the query from above, because another column will be generated with the query above.

select
CONVERT(DATE,S.SolData,103) [DATA],
COUNT (S.SolID) [Abertos]

from Solicitacao S
where S.ProID = 4 and S.SolData between '26-06-2017' and '30-06-2017' and S.SolTipID = 35

GROUP BY CONVERT(DATE,S.SolData,103)

It’s to go out that way

Data           Abertos        Cancelados
26-06-2017       0                2
27-06-2017       2                1
28-06-2017       5                0
29-06-2017       4                0
30-06-2017       0                4

Answer 1

Just change the condition of JOIN to be part of a CASE:

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,
       COUNT(CASE WHEN s.soltipid = 35 THEN 1 ELSE NULL END) AS abertos,
       COUNT(CASE WHEN s.solestagioid = 110 THEN 1 ELSE NULL END) AS cancelados
  FROM dias d
       LEFT JOIN solicitacao s ON CONVERT(DATE, s.soldatafechamento, 103) = d.dia
                              AND s.proid = 4
GROUP BY d.dia
OPTION (MAXRECURSION 0);

Here you check the script used to test the response.

See working on SQL Fiddle

Answer 2

Renan, here is the suggestion treating the cases of openings and cancellations, individually. A CTE for each type of case.

-- 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)
),
-- contabiliza aberturas
cteAbertos as (
SELECT convert(date, SolData, 103) as SolData,
       count(SolID) as Abertos
  from Solicitacao
  where ProID = 4 
        and SolTipID = 35
        and convert(date, SolData, 103) between @dataInicial and @dataFinal 
  group by convert(date, SolData, 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],
       coalesce(A.Abertos, 0) as Abertos,
       coalesce(C.Cancelados, 0) as Cancelados
  from cteDatas as D
       left join cteCancelados as C on C.SolDataFechamento = D.dataPeríodo
       left join cteAbertos as A on A.SolData = D.dataPeríodo;

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 code maintenance.

The WHERE clause, present in each TEC, filters the required lines, thus reducing the volume of lines to be processed by GROUP BY (operator Stream Aggregate).

---

If the Weld and Weld columns are declared as datetime, you can change:

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

This makes the clause WHERE sargable.