Forum Stats

  • 3,784,143 Users
  • 2,254,897 Discussions
  • 7,880,709 Comments

Discussions

Hi experts please help for the below question

4064279
4064279 Member Posts: 27
edited Sep 4, 2019 11:14AM in SQL & PL/SQL

can u explain the query to display for each customer for each month????

.a.total login minutes,

b.total bytes downloaded,

c.avg login duration per day,

d.no of days which the user did not login at all

CREATE TABLE T1

    (CUST_ID VARCHAR2(30),LOGIN_ID VARCHAR2(30),CUST_NM VARCHAR2(30),CUST_ADDR VARCHAR2(30));

INSERT INTO T1 VALUES

CUST0000000001,,USER1,User_LogiN ID_1,143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34)

INSERT INTO T1 VALUES

(CUST0000000002,USER2,User_LogiN ID_2,143/2 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34)

INSERT INTO T1 VALUES

(CUST0000000003,USER3,User_LogiN ID_3,143/3 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

INSERT INTO T1 VALUES

(CUST0000000004,USER4,User_LogiN ID_4,143/4 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

COMMIT;

CREATE TABLE T2

(CONNECTION_ID VARCHAR2(30),

LOGIN_ID VARCHAR2(30),

LOGIN_DTTM VARCHAR2(30),LOGOUT_DTTM VARCHAR2(30),BYTES_CNT VARCHAR2(30),

DAILY_BILLING_STATUS VARCHAR2(30),MONTHLY_BILLING_STATUS VARCHAR2(30),

LOGIN_SESSION_BILLING_AMT VARCHAR2(30));

INSERT INTO T2 VALUES('CON0000000001','USER1','01-SEP-08','01-SEP-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000002','USER1','02-SEP-08','01-SEP-08',500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000003','USER1','1-SEP-08,'10-SEP-08',1500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000004','USER1','3-SEP-08,'30-SEP-08',10,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000005','USER1','3-SEP-08,'01-OCT-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000006','USER1','0-OCT-08,'05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000007','USER1','1-SEP-08,'17-SEP-08',1000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000008','USER1','24-SEP-08','24-SEP-08',1500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000009','USER2','01-SEP-08','01-SEP-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000010','USER2','01-SEP-08','01-SEP-08',500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000011','USER2','10-SEP-08','10-SEP-08',1500,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000012','USER2','30-SEP-08','30-SEP-08',10,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000013','USER2','30-SEP-08','01-OCT-08',100,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000014','USER2','03-OCT-08','05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000015','USER2','16-SEP-08','17-SEP-08',1000,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000016','USER2','24-SEP-08','24-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000017','USER3','01-SEP-08','01-SEP-08',100,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000018','USER3','01-SEP-08','01-SEP-08',500,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000019','USER3','10-SEP-08','10-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000020','USER3','30-SEP-08','30-SEP-08',10,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000021','USER3','30-SEP-08','01-OCT-08',100,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000022','USER3','03-OCT-08','05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000023','USER3','16-SEP-08','17-SEP-08',1000,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000024','USER3','24-SEP-08','24-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000025','USER4','01-SEP-08','01-SEP-08',100,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000026','USER4','01-SEP-08','01-SEP-08',500,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000027','USER4','10-SEP-08','10-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000028','USER4','30-SEP-08','30-SEP-08',10,'N',N,NULL   );

INSERT INTO T2 VALUES('CON0000000029','USER4','30-SEP-08','01-OCT-08',100,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000030','USER4','03-OCT-08','05-OCT-08',12000,'N',N,NULL);

INSERT INTO T2 VALUES('CON0000000031','USER4','16-SEP-08','17-SEP-08',1000,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000032','USER4','24-SEP-08','24-SEP-08',1500,'N',N,NULL );

INSERT INTO T2 VALUES('CON0000000033','USER1','24-SEP-07','24-SEP-07',122,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000034','USER1','25-SEP-07','26-SEP-07',544,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000035','USER2','25-SEP-07','26-SEP-07',355,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000036','USER3','24-SEP-07','24-SEP-07',100,'N',N,NULL  );

INSERT INTO T2 VALUES('CON0000000037','USER3','25-SEP-07','26-SEP-07',10,'N',N,NULL   );

INSERT INTO T2 VALUES('CON0000000038','USER3','25-DEC-07','25-DEC-07',1000,'N',N,NULL );

Tagged:
Billy VerreynneBEDEjaramill

Answers

  • KayK
    KayK Member Posts: 1,687 Bronze Crown
    edited Sep 4, 2019 7:23AM

    Hi 40,

    it seems that your inserts for table T1 will not work, please correct.

    You're looking for 'total login minutes' and 'avg login duration per day' but your date columns in T2 only contents days, months and years, no hours, no minutes.
    How do you calculate these minutes ?

    It will be helpfull to see what do you expect as output along with the rules to achieve this.

    regards

    Kay

  • 4064279
    4064279 Member Posts: 27
    edited Sep 4, 2019 7:30AM

    using to_char() function it should be calcualted both login_in time and logout time

    CREATE TABLE T1

        (CUST_ID VARCHAR2(30),LOGIN_ID VARCHAR2(30),CUST_NM VARCHAR2(30),CUST_ADDR VARCHAR2(30));

    INSERT INTO T1 VALUES('CUST0000000001',USER1,'User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

    INSERT INTO T1 VALUES('CUST0000000002',USER2,'User_LogiN_ID_2','143/2 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

    INSERT INTO T1 VALUES('CUST0000000003',USER3,'User_LogiN_ID_3','143/3 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

    INSERT INTO T1 VALUES('CUST0000000004',USER4,'User_LogiN_ID_4','143/4 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34);

    COMMIT;

  • 4064279
    4064279 Member Posts: 27
    edited Sep 4, 2019 7:31AM

    CREATE TABLE T1

        (CUST_ID VARCHAR2(30),LOGIN_ID VARCHAR2(30),CUST_NM VARCHAR2(30),CUST_ADDR VARCHAR2(30));

    INSERT INTO T1 VALUES('CUST0000000001',USER1,'User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    INSERT INTO T1 VALUES('CUST0000000002',USER2,'User_LogiN_ID_2','143/2 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    INSERT INTO T1 VALUES('CUST0000000003',USER3,'User_LogiN_ID_3','143/3 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    INSERT INTO T1 VALUES('CUST0000000004',USER4,'User_LogiN_ID_4','143/4 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

  • KayK
    KayK Member Posts: 1,687 Bronze Crown
    edited Sep 4, 2019 7:41AM

    Where do you find login_in time and logout time ?

    The inserts didn't work for me:

    < scott:[email protected] > INSERT INTO T1 VALUES('CUST0000000001',USER1,'User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');INSERT INTO T1 VALUES('CUST0000000001',USER1,'User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34')                                      *ERROR at line 1:ORA-00984: column not allowed here< scott:[email protected] > INSERT INTO T1 VALUES('CUST0000000001','USER1','User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');INSERT INTO T1 VALUES('CUST0000000001','USER1','User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34')                                                                *ERROR at line 1:ORA-12899: value too large for column "SCOTT"."T1"."CUST_ADDR" (actual: 56, maximum: 30)
    4064279
  • 4064279
    4064279 Member Posts: 27
    edited Sep 4, 2019 7:57AM

    INSERT INTO T1 VALUES('CUST0000000001','USER1,'User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    INSERT INTO T1 VALUES('CUST0000000002','USER2,'User_LogiN_ID_2','143/2 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    INSERT INTO T1 VALUES('CUST0000000003','USER3,'User_LogiN_ID_3','143/3 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    INSERT INTO T1 VALUES('CUST0000000004','USER4,'User_LogiN_ID_4','143/4 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond
    edited Sep 4, 2019 8:24AM

    Hi,

    Would you like to get answers that work?  Then make sure the CREATE TABLE and INSERT statements you post work, too.

    4064279 wrote:INSERT INTO T1 VALUES('CUST0000000001','USER1,'User_LogiN_ID_1','143/1 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');INSERT INTO T1 VALUES('CUST0000000002','USER2,'User_LogiN_ID_2','143/2 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');INSERT INTO T1 VALUES('CUST0000000003','USER3,'User_LogiN_ID_3','143/3 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');INSERT INTO T1 VALUES('CUST0000000004','USER4,'User_LogiN_ID_4','143/4 Uthamar GaNdhi Salai,NuNgambakkam, CheNNai NULL 34');

    Those INSERT statements still don't work.  Test (and, if necessary, fix) your statements before you post them.

    The INSERT statements for t2 in your original message have problems, too.

    Using string columns for date/time information is just asking for complicated, inefficient, error-prone code.  Use DATE columns for t2.login_dttm and logout_dttm.

    Don't forget to post the exact results you want from the given sample data.

  • Paulzip
    Paulzip Member Posts: 8,545 Blue Diamond
    edited Sep 4, 2019 8:23AM

    Let's rewind a little.  Why are all your fields strings?  Use the correct datatype, dates as dates, numbers as number etc.  Your current approach makes zero sense and makes any solution - especially one relying on aggregation - more difficult.

    CREATE TABLE T2 (

    CONNECTION_ID VARCHAR2(30),

    LOGIN_ID VARCHAR2(30),

    LOGIN_DTTM DATE,

    LOGOUT_DTTM DATE,

    BYTES_CNT INTEGER,

    DAILY_BILLING_STATUS VARCHAR2(30),

    MONTHLY_BILLING_STATUS VARCHAR2(30),

    LOGIN_SESSION_BILLING_AMT NUMBER

    );

    Billy VerreynneBEDEjaramill
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 4, 2019 10:03AM

    CHANGE the subject title of your thread!  (See forum guidelines #2) -->

    2) Thread Subject lineGive your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP".  This forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue.  By marking your question as urgent you are actually less likely to get a good response, as members will choose to ignore such questions or respond unfavorably.
  • 4064279
    4064279 Member Posts: 27
    edited Sep 4, 2019 10:33AM
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 4, 2019 11:14AM
    4064279 wrote:oh

    Yes..OH.  Read the guidelines, and also learn about "date" datatypes from the tutorial provided on the forum (right-hand side of the forum).