CODEWITHSUNDEEP
sqloracle-database
Kiffer Van
Kiffer Van

Reputation: 242

Count Data each days

Hi i'm trying to write a query that will give me the result of Count of duration greater than 10 each employee per day.

sapmle data

EVENTID     USERID      DATE_TIME   READERID        READERNAME  LOGTYPE USERNAME    RESULT  DURATION

188110074       50078       2020-03-02 22:01:31 544381441       LOCKER      IN   SUBIA, MAY     00:12   12
188099752       50078       2020-03-02 21:48:52 544381436       LOCKER      OUT  SUBIA, MAY             
188098672       50078       2020-03-02 21:47:10 544381441       LOCKER      IN   SUBIA, MAY             
188098656       50078       2020-03-02 21:47:06 544381441       LOCKER      IN   SUBIA, MAY     15:45   945
187972279       50078       2020-03-02 06:01:54 544381436       LOCKER      OUT  SUBIA, MAY             
187949027       50078       2020-03-02 04:30:46 544381441       LOCKER      IN   SUBIA, MAY     00:16   16
187933475       50078       2020-03-01 22:44:47 544381431       LOCKER      IN   SUBIA, MAY     00:04   4
187933289       50078       2020-03-01 22:40:41 544381436       LOCKER      OUT  SUBIA, MAY             
187926256       50078       2020-03-01 22:01:31 544381431       LOCKER      IN   SUBIA, MAY     00:14   14
187914503       50078       2020-03-01 21:46:42 544381428       LOCKER      OUT  SUBIA, MAY 
180769437       1214201592  2020-01-20 08:13:06 544381436       LOCKER      OUT  ALLAN YAP          
180771730       1214201592  2020-01-20 08:36:38 544381431       LOCKER      IN   ALLAN YAP  00:23   23
180775099       1214201592  2020-01-20 09:07:04 544381436       LOCKER      OUT  ALLAN YAP          
180777340       1214201592  2020-01-20 09:30:53 544381431       LOCKER      IN   ALLAN YAP  00:23   23
180778948       1214201592  2020-01-20 09:48:58 544381436       LOCKER      OUT  ALLAN YAP          
180781714       1214201592  2020-01-20 10:04:52 544381431       LOCKER      IN   ALLAN YAP  00:15   15

Expected output

USERID      DATE_TIME   Count
50078       2020-03-02  3
50078       2020-03-01  1
1214201592  2020-01-20  3

Here's what i have tried

Select count, USERNAME,  DATE_TIME
from (
Select  A.USERNAME,A.DATE_TIME , Count(A.Duration) count
from TBLACCESSLOGS A    
where Duration > 10 
Group by Date_Time,USERNAME)

Note: Date_time is in varchar type but got wrong result , its looks like far from what i expected.

Hope someone help me out with this

Upvotes: 0

Views: 62

Answers (2)

Popeye
Popeye

Reputation: 35900

You are quite closer to your expected output. Just use TRUNC as following:

SELECT
    A.USERNAME,
    A.USERID, -- this
    TRUNC(TO_DATE(A.DATE_TIME,'YYYY-MM-DD HH24:MI:SS')) AS DATE_TIME,
    COUNT(A.DURATION) COUNT
FROM
    TBLACCESSLOGS A
WHERE
    DURATION > 10
GROUP BY
    A.USERNAME,
    A.USERID, -- this
    TRUNC(TO_DATE(A.DATE_TIME,'YYYY-MM-DD HH24:MI:SS'))

Cheers!!

Upvotes: 1

jishan siddique
jishan siddique

Reputation: 1895

Please check this it's work for me.

Note you need to check this.

DECLARE @Date VARCHAR(30) ='2020-03-02 22:01:31'; 
SELECT CAST(@DATE AS DATE) SELECT CONVERT(DATE,@DATE)

SELECT
    A.USERNAME,
    CAST(A.DATE_TIME AS DATE) AS DATE_TIME,
    COUNT(A.DURATION) TotalCount
FROM
    TBLACCESSLOGS A
WHERE
    DURATION > 10
GROUP BY
    USERNAME,
    CAST(A.DATE_TIME AS DATE)
  1. Another way
SELECT
    A.USERNAME,
    CONVERT(DATE,A.DATE_TIME) AS DATE_TIME,
    COUNT(A.DURATION) TotalCount
FROM
    TBLACCESSLOGS A
WHERE
    DURATION > 10
GROUP BY
    USERNAME,
    CONVERT(DATE,A.DATE_TIME)

Upvotes: 1

Related Questions