Adds WITH query within another query - SQL Server

Navigation
1

People, like adding the query below as a subquery.

Query 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 with the above query will be generated. 

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 come out this 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
    
asked by anonymous 18.08.2017 / 20:01

2 answers

-1

Renan, this suggestion dealing with cases of openings and cancellations, individually. A CTE for each case type. 

-- 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 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 maintain the code.

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

If the SolData and SolDataColumn 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 WHERE clause sargable .

    
18.08.2017 / 20:12
1

Just change the condition of JOIN to be part of 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 give script used to test the answer.

See working in SQL Fiddle

    

18.08.2017 / 20:18
Modal within a query Error in passing Java parameter