Separate results from an SQL query by date

Asked

Viewed 427 times

0

I am creating an SQL query that will be used in a system/report and came across the following problem. It has two results, one that displays the total time between the days searched and the other that will be displayed day by day. The difficulty in the case is in the second screen I have to create.

The Case Consultation is like this 

SELECT ocorrencias.[GITEC]
,SUM(DATEDIFF(SECOND, ocorrencias.[INICIO], ocorrencias.[FECHAMENTO 
OCORRÊNCIA])) as Total_Indispo 

FROM [REPORT].[dbo].[report_sirea] ocorrencias INNER JOIN [REPORT]. 
  [dbo].[planta_circuitos] circuitos
  ON ocorrencias.[CIRCUITO] = circuitos.[DESIGNACAO] WHERE
   ocorrencias.[INICIO] BETWEEN '2019-07-01' AND '2019-07-03 23:59:59'

   AND ocorrencias.[FECHAMENTO OCORRÊNCIA] IS NOT NULL
    AND ocorrencias.[GITEC] = 'GITEC/BH' 
    AND circuitos.[TIPO_PONTO] NOT IN ('LOTERICOS', 
   'BACKBONE','INTERLIGAÇÃO - BACKBONE') 
   GROUP BY ocorrencias.[GITEC]
   ORDER BY [ocorrencias].[GITEC]

In the case would also have to present the results dynamically separately, In case would need to show on 01, after the 02 and last the day 03.

It is possible to do this directly in SQL only using a Select or would need something else (Function, Procedure) or would need to do it directly in PHP language?

In this case, I am using SQL Database Server.

sql database sql-server

  • you can make a process that loads all the results in a temporary table and returns everything at once to the end with all the results you need. Another solution would be to assemble multiple selects, run all and pay the various results using mysqli_multi_query. If you need help with any of these options I can help with an answer

  • Can you provide table structure, example data for tables, and an expected result for these example data? Without it it’s hard to visualize what you want

1 answer

1

You can create a CTE with the days between the dates as follows:

WITH dias AS (
  SELECT CAST('2019-07-01' AS DATE) AS dia,
         1 AS sequencia
   UNION ALL
  SELECT CAST(DATEADD(DAY, 1, dia) AS DATE),
         sequencia + 1
    FROM dias
   WHERE dia < '2019-07-03 23:59:59'
)
SELECT d.dia
  FROM dias d
 ORDER BY d.sequencia

Then just apply on your query:

WITH dias AS (
  SELECT CAST('2019-07-01' AS DATE) AS dia,
         1 AS sequencia
   UNION ALL
  SELECT CAST(DATEADD(DAY, 1, dia) AS DATE),
         sequencia + 1
    FROM dias
   WHERE dia < '2019-07-03 23:59:59'
)
SELECT d.dia,
       ocorrencias.[GITEC],
       SUM(DATEDIFF(SECOND, ocorrencias.[INICIO], ocorrencias.[FECHAMENTO OCORRÊNCIA])) as Total_Indispo
  FROM [REPORT].[dbo].[report_sirea] ocorrencias
 INNER JOIN [REPORT]. [dbo].[planta_circuitos] circuitos
    ON ocorrencias.[CIRCUITO] = circuitos.[DESIGNACAO]
 INNER JOIN dias d
    ON CAST(ocorrencias.[INICIO] AS DATE) = dia
 WHERE ocorrencias.[FECHAMENTO OCORRÊNCIA] IS NOT NULL
   AND ocorrencias.[GITEC] = 'GITEC/BH'
   AND circuitos.[TIPO_PONTO] NOT IN ('LOTERICOS', 'BACKBONE','INTERLIGAÇÃO - BACKBONE')
 GROUP BY ocorrencias.[GITEC]
 ORDER BY d.dia, [ocorrencias].[GITEC];

Browser other questions tagged sql database sql-server

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