Skip to content
Advertisement

How to select data from day by day with special time?

I want to select data day by day. Condition: data will be cut the day from 10:00 pm to tomorrow 10 pm. For example:

| REGTIME    | T8  | T9 | T10 |
| 2008-12-03 | 879 | 785| 542 |
| 2008-12-04 | 556 | 454| 321 |
| 2008-12-05 | 678 | 122| 212 |
| 2008-12-06 | 887 | 544| 214 |

(2008-12-03 data will be show from 2008-12-02 10:00pm to 2008-12-03 10:00 pm)…

That query shows me all date, but it shows day with all same value.

SELECT REGTIME2,
       A.T8,T9,T10
FROM 
     ( SELECT                         
              SUM(CASE WHEN sdudent_type = 'AAAA1' THEN 1 ELSE 0 END) AS T8,
              SUM(CASE WHEN sdudent_type = 'AAAA2' THEN 1 ELSE 0 END) AS T9,
              SUM(CASE WHEN sdudent_type = 'AAAA3' THEN 1 ELSE 0 END) AS T10                   
       FROM class_men C     
       WHERE REGTIME >= TO_DATE(:REGTIME_from,'YYYYMMDDHH24MISS')
       AND REGTIME < TO_DATE(:REGTIME_to,'YYYYMMDDHH24MISS')          
) A,
(    
  select distinct
         to_char(REGTIME,'YYYY-MM-DD') AS REGTIME2
  from   class_men
  group by  REGTIME
  order by to_char(REGTIME,'YYYY-MM-DD') desc              
) B

Advertisement

Answer

You are getting the results duplicated for all days as you are using a CROSS JOIN (written in the legacy syntax using a comma) to join the two SELECT clauses and there is no correlation between those two statements.

You appear to want something like:

SELECT TRUNC( RegTime ) AS RegTime,                         
       COUNT(CASE WHEN sdudent_type = 'AAAA1' THEN 1 END) AS T8,
       COUNT(CASE WHEN sdudent_type = 'AAAA2' THEN 1 END) AS T9,
       COUNT(CASE WHEN sdudent_type = 'AAAA3' THEN 1 END) AS T10                   
FROM   class_men C
WHERE  REGTIME >= TO_DATE(:REGTIME_from,'YYYYMMDDHH24MISS')
AND    REGTIME < TO_DATE(:REGTIME_to,'YYYYMMDDHH24MISS')
GROUP BY TRUNC( RegTime );

or using TO_CHAR( RegTime, 'YYYY-MM-DD' ) instead of TRUNC( RegTime ).

Which, for the sample data:

CREATE TABLE class_men ( regTime, sdudent_type ) AS
SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1'
  FROM DUAL CONNECT BY LEVEL <= 250 UNION ALL
SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA2'
  FROM DUAL CONNECT BY LEVEL <= 42 UNION ALL
SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3'
  FROM DUAL CONNECT BY LEVEL <= 13 UNION ALL
SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1'
  FROM DUAL CONNECT BY LEVEL <= 99 UNION ALL
SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA2'
  FROM DUAL CONNECT BY LEVEL <= 17 UNION ALL
SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3'
  FROM DUAL CONNECT BY LEVEL <= 24 UNION ALL
SELECT DATE '2021-01-03' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1'
  FROM DUAL CONNECT BY LEVEL <= 23 UNION ALL
SELECT DATE '2021-01-03' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3'
  FROM DUAL CONNECT BY LEVEL <= 50;

Would output (for the range 20210101000000 to 20210104000000):

REGTIME             |  T8 | T9 | T10
:------------------ | --: | -: | --:
2021-01-01 00:00:00 | 250 | 42 |  13
2021-01-03 00:00:00 |  23 |  0 |  50
2021-01-02 00:00:00 |  99 | 17 |  24

(Note: the date format depends on the NLS_DATE_FORMAT session parameter [unless you use TO_CHAR].)

db<>fiddle here

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement