Oracle SQL Query with Group By starting and ending on different days

Asked

Viewed 61 times

0

I need to sum up grouped numerical records per day within a month, I have the timestamp of all records, but I can’t make a group by because my day starts with the 00:15 record and ends with the 00:00 record of the next day, so I can’t group with TO_CHAR(trunc(date), 'dd/MM/YYYY') since the first record of the day, in case 00:00, I do not consider for the current day and the last one is 00:00, which I need to consider, belongs to the next day, summarizing, an example of a day is 01/05/2020 00:15 until 02/05/2020 00:00 (I add all the records of that period and compose one day).

My current SELECT is like this:

SELECT
    period,
    liquid    
FROM
    (
        SELECT
            ( nvl(SUM(m.coluna1), 0) - nvl(SUM(m.coluna2), 0) ) liquid,            
            TO_CHAR(trunc(m.date), 'dd/MM/YYYY') period
        FROM
            tabela m
        WHERE
            m.id_registro IN (id_registro)
            AND m.date BETWEEN TO_DATE('01/05/2020 00:15:00', 'dd/mm/yyyy hh24:mi:ss') AND TO_DATE('01/06/2020 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
        GROUP BY
            TO_CHAR(trunc(m.date), 'dd/MM/YYYY')
    ) vrdm

How do I mount this select to consider a day always starting 00:15 and ending 00:00 the next day and group that by day to sum up the records?

I need you to stay like this:

  period   |  liquid
01/05/2020 |   3000   -> SOMA(todas diferenças entre coluna1 e coluna2 entre 01/05 00:15 e 02/05 00:00)
02/05/2020 |   4000   -> SOMA(todas diferenças entre coluna1 e coluna2 entre 02/05 00:15 e 03/05 00:00)
03/05/2020 |   3500   -> SOMA(todas diferenças entre coluna1 e coluna2 entre 03/05 00:15 e 04/05 00:00)

The table I have is like this:

       date       |   coluna1   |  coluna2
01/05/2020 00:00  |     50      |     20
01/05/2020 00:15  |     60      |     30
01/05/2020 00:30  |     70      |     40
       ...        |             |
01/05/2020 23:45  |     80      |     50
02/05/2020 00:00  |     100     |     40
02/05/2020 00:15  |     110     |     35
oracle select date

  • I don’t have the bench here to test, but taking 15 minutes of m.date with - NUMTODSINTERVAL(15, 'MINUTE') does not solve your problem?

  • @Leandroluque where would I wear it? You could give me an example?

  • At GROUP BY. Keep your instruction equal until group by and replace with GROUP BY TO_CHAR(trunc(m.date - NUMTODSINTERVAL(15, 'MINUTE')), 'dd/MM/YYYY') ) vrdm

  • @Leandroluque thank you for trying, but not solved yet.

  • Can share database Scripts to analyze other paths?

  • Basically, I have a table that records data every 15 minutes, value and time, and I need to group the sum of these values per day, only that the day does not start 00:00 and ends 23:59, and yes 00:15 and 00:00 (the next day). I just need to think of a way that the select will handle the 00:00 hours by the day before.

  • I did a test here with my suggestion and, from what I understood of your problem, it worked. I created a database with only two columns (id, date). I registered the following dates: (1, 12-JUN-20 12.00.00.000000 AM), (3, 13-JUN-20 12.00.15.000000 AM), (2, 12-JUN-20 11.59.00.000000 PM), (4, 14-JUN-20 12.00.00.000000 AM). I executed the following search and it grouped correctly: SELECT TO_CHAR(data-NUMTODSINTERVAL(15, 'MINUTE'), 'dd/MM/YYYY'), COUNT(*) FROM TESTE458093 GROUP BY TO_CHAR(data-NUMNUMTODSINTERVAL(15, 'MINUTE'), 'dd/MM/YYYY');

  • @Leandroluque take a look at the issue I asked the question, see if it was clear what I need.

Show 3 more comments

1 answer

1

You can calculate the date using CASE to check whether the date is between the period 00:15 ~ 00:00.

Doing something like that:

CASE 
  WHEN DT - TRUNC(DT) >= (1/24/60 * 15) THEN 
    TRUNC(DT) + (1/24/60 * 15) 
   ELSE 
     TRUNC(DT -1) + (1/24/60 * 15) 
END DT_NEW

Staying then:

If minutes are longer than 15 minutes, then the date for grouping will be the same day, if not the past day.

Example:

WITH dados as (
SELECT 10 vl, to_date('01/01/2010 00:15:00','dd/mm/yyyy hh24:mi:ss') dt FROM dual UNION ALL  
SELECT 20 vl, to_date('02/01/2010 00:14:59','dd/mm/yyyy hh24:mi:ss') dt FROM dual UNION ALL
SELECT 30 vl, to_date('02/01/2010 00:15:00','dd/mm/yyyy hh24:mi:ss') dt FROM dual UNION ALL  
SELECT 40 vl, to_date('03/01/2010 00:14:59','dd/mm/yyyy hh24:mi:ss') dt FROM dual 
),

AJUSTADO AS (
SELECT VL, 
       DT , 
       CASE 
          WHEN DT - TRUNC(DT) >= (1/24/60 * 15) THEN 
            TRUNC(DT) + (1/24/60 * 15) 
           ELSE 
             TRUNC(DT -1) + (1/24/60 * 15) 
       END DT_NEW
 FROM dados 
)

SELECT sum(vl) valor, dt_new 
 FROM AJUSTADO
 GROUP BY dt_new 
 ORder BY dt_new

See rotating on SQL Fiddle.

  • Thanks Bruno for the answer, I edited my question, I do not know if your solution solves what I need, you could take a look at the rest of the question to see if it suits me your solution.

  • Yes meets, just apply the same concept in your query. But this I will not do for you hahaha.

Browser other questions tagged oracle select date

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