CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Kiffer Van
Kiffer Van

Reputation: 242

Count data per day between two dates

Hi i'm trying to count the total late remark per day between two dates inputs by the user.

for example:

ID  NAME    DATE_TIME         REMARKS
1   Aa  2020-01-18 09:57:56     LATE
2   Aa  2020-01-18 10:57:56     LATE
3   Aa  2020-01-19 06:52:56    
4   Aa  2020-01-19 09:57:56     LATE
5   Aa  2020-01-19 09:57:56     LATE
6   Aa  2020-01-21 09:57:56     Late

Expected result.

NAME     DATE          count
Aa     2020-01-18         2
Aa     2020-01-19         2
Aa     2020-01-20         0
Aa     2020-01-21         1

The Data type of DATE_TIME is varhcar2

this is my attemp but i dont know how to achive it.

Select Count(REMARKS) countBT from TBLACCESSLOGS WHERE To_date(DATE_TIME,'YYYY-MM-DD') between To_date('2020-02-18','YYYY-MM-DD') and To_date('2020-02-20','YYYY-MM-DD')

and i get error date format picture ends before converting entire input string pointing on DATE_TIME as i execute.

Hope someone help me with this. Thank you in advance

Upvotes: 0

Views: 63

Answers (3)

vishnudattan
vishnudattan

Reputation: 476

Try this ..

    SQL> select * from late_remarks;

        ID NAME DATE_TIME             REMARKS
        -- --   -------------------   ----
        1  Aa   2020-01-18 09:57:56   LATE
        2  Aa   2020-01-18 10:57:56   LATE
        3  Aa   2020-01-19 06:52:56
        4  Aa   2020-01-19 09:57:56   LATE
        5  Aa   2020-01-19 09:57:56   LATE
        6  Aa   2020-01-21 09:57:56   LATE

    6 rows selected.



    SQL> with dates as (
      2      select to_date('17-01-2020', 'DD-MM-YYYY') + level "DATE"
      3      from dual
      4      connect by level <= (to_date('21-01-2020', 'DD-MM-YYYY') - to_date('17-01-2020', 'DD-MM-YYYY'))
      5      )
      6  select 'Aa' name, d."DATE", count(lr.remarks) count from dates d
      7      left outer join late_remarks lr
      8          on d."DATE" = trunc(to_timestamp (lr.date_time, 'YYYY-MM-DD HH24:MI:SS'))
      9      group by d."DATE"
     10      order by d."DATE";

    NAME DATE           COUNT
    --   --------- ----------
    Aa   18-JAN-20          2
    Aa   19-JAN-20          2
    Aa   20-JAN-20          0
    Aa   21-JAN-20          1

.. assuming name to be constant

Upvotes: 0

Belayer
Belayer

Reputation: 14934

Since you face the prospect that there may be missing dates within the range your looking for you need to generate an entry for each date in that range. You the join those dates with your table, counting the number of remarks column.

with date_parms as 
     (select to_date('&Start_Date','yyyy-mm-dd') start_date 
           , to_date('&End_Date','yyyy-mm-dd') end_date
        from dual
     ) 
    , date_list as
      (select start_date+lev-1 t_date 
         from date_parms
            , ( select level lev 
                  from dual 
                connect by level <= (select end_date - start_date + 1   
                                       from date_parms
                                    )
               ) 
       )  
select t_date "Date"
     , name
     , count(*) "Num Late"
  from date_list  dl
  left join lates l  on trunc(l.date_time) = dl.t_date and lower(l.remark) = 'late' 
 where 1=1 --lower(l.remark) = 'late'
 group by trunc(t_time), name;

Note. Once the initial parameters (start and end dates) are converted to from strings to dates no further date-string manipulation is required.

Upvotes: 3

Levin
Levin

Reputation: 2015

Completely edited; this is a multi-step process, the real important SQL logic is in "THE_GOODS". THe generation of days is in "DAYS" and I took that from here: https://www.zetetic.net/blog/2009/2/12/generating-a-sequential-date-series-in-oracle.html -- I don't understand it much more than ctl-c/ctl-v. "PERMS" makes the permutation of dates/names, then that is left-joined to THE_GOODS to get the counts. So for each combo of user and dates in range you get one row, and the count from THE_GOODS, or zero if there's no matching row.

to fiddle with it: http://sqlfiddle.com/#!4/42618/8

 WITH DAYS AS 
 (SELECT TO_CHAR(TRUNC(TO_DATE('01-JAN-2020') + ROWNUM - 1, 'DD'), 'YYYY-MM-DD') AS ADAY
 FROM (
   SELECT ROWNUM FROM (
 SELECT 1 FROM DUAL
 CONNECT BY LEVEL <= (TO_DATE('08-JAN-2020') - TO_DATE('01-JAN-2020'))
   )
 )
 ),
 THE_GOODS AS (
   select name, to_char(DATE_TIME, 'YYYY-MM-DD') AS ADAY, count(*) AS HOW_MANY
 from TBLACCESSLOGS
 where trunc(DATE_TIME, 'DD') between to_date('2020-01-01', 'YYYY-MM-DD')
 and to_date('2020-01-05', 'YYYY-MM-DD')
 and remarks = 'LATE'
 group by name, to_char(DATE_TIME, 'YYYY-MM-DD')
   )
 ,
 PERMS AS (
 SELECT DISTINCT DAYS.ADAY,  THE_GOODS.NAME
 FROM DAYS 
   CROSS JOIN
   THE_GOODS
  )
   SELECT p.NAME, p.ADAY, COALESCE(g.HOW_MANY, 0) AS HOWMANY
   FROM PERMS p
   LEFT JOIN THE_GOODS g
 on p.ADAY = g.ADAY
 and p.NAME = g.NAME
   ORDER BY p.ADAY, g.NAME

Upvotes: 1

Related Questions