Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    14

    Unanswered: Report using three tables on group basis

    Hi,

    I wish to know a clue for the follwing report among three tables either using join or sub-query or PL/SQL Script as per the below desired output.

    Top 10 games by uniques / by volume

    It should produce something like this:

    Game Uniques Volumes Game Start Game End Mins on Air
    Top 5 movies beginning with "D" 2734 7924 9/24/06 9:59 PM 9/24/06 10:41 PM 42
    Top 5 One Hit Wonders 2355 6471 9/24/06 9:07 PM 9/24/06 9:48 PM 41
    Things you find in The Kitchen 1336 3600 9/24/06 10:41 PM 9/24/06 10:59 PM 18
    Twisted Title Men in Black 770 1435 9/24/06 9:53 PM 9/24/06 9:59 PM 6
    Anagram Lance Armstrong 884 1350 9/24/06 9:48 PM 9/24/06 9:53 PM 5
    A.Bucks Jack and Jill... 593 824 9/24/06 8:59 PM 9/24/06 9:04 PM 4
    Missing link ANY101 649 815 9/24/06 9:04 PM 9/24/06 9:07 PM 3

    Parameters should be startDate and endDate.

    This query can be obtained from using the following tables: Calls, Games, Events, Event_Types

    Calls have a timestamp.

    Every game has event, such as start game or end game (see Event_Types), with its timestamp

    Volumes: Number of calls received for each game between start game date and end date
    Uniques: Unique Number of calls received for each game between start game date and end date
    (distinct cli)
    Mins on air: differences between start call and end call

    Relationship:
    The ID column from games table and game_id from events table is common.

    Assume if the event type id is 2 then it starts game and if 3 then game ends. Other type is irrelevant for this query.

    The id from event_type is mentioned in another table event_types as master with description. But it is not required to establish relationship with this table. As this code ( 2 or 3) is alredy availbel with event_type_id in the events table.

    Please assume the CLI number as dummy data.

    I have provided the structure and query to generate tables and populate testing data to sort out this issue at the earliest.

    I tried to perform this query but I wish to compare the result with the script given by experts as Im not a core developer.

    1) desc calls

    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    CLI NOT NULL VARCHAR2(255)
    CALL_DATE NOT NULL TIMESTAMP(6)

    insert into values('&CLI','&call_date')

    select substr(CLI,1,10),substr(call_date,1,22) from calls

    SUBSTR(CLI SUBSTR(CALL_DATE,1,22)
    ---------- ----------------------
    0662740929 22-SEP-06 05.22.44.123
    0662740973 22-SEP-06 05.22.47.123
    0662740956 22-SEP-06 05.22.46.123
    0662740980 22-SEP-06 05.22.47.123
    0662740936 09-MAY-06 05.22.44.123
    0762740954 22-SEP-06 05.22.45.123
    0762740936 09-MAY-06 05.22.47.123
    0762740921 22-SEP-06 05.22.44.123
    0113456789 22-SEP-06 05.47.04.082
    0987654321 22-SEP-06 06.16.29.727
    0 22-SEP-06 06.17.28.141

    SUBSTR(CLI SUBSTR(CALL_DATE,1,22)
    ---------- ----------------------
    0123456789 09-MAY-06 06.27.51.224
    0112740929 22-SEP-06 06.28.43.398
    0123456789 09-MAY-06 06.30.10.830
    0044791475 24-SEP-06 04.38.08.564
    0044791475 24-SEP-06 04.40.05.777
    0123456789 24-SEP-06 05.32.22.267
    0147258369 24-SEP-06 05.34.25.652
    0852147963 24-SEP-06 05.52.56.992
    0123456789 25-SEP-06 01.34.17.157
    0683379112 25-SEP-06 01.35.19.461
    0 25-SEP-06 03.09.12.347

    SUBSTR(CLI SUBSTR(CALL_DATE,1,22)
    ---------- ----------------------
    0141411683 25-SEP-06 03.21.07.402
    0141411683 25-SEP-06 03.21.38.519
    0618769562 02-JUN-06 03.22.12.807
    0123456789 02-JUN-06 03.24.11.387
    0 25-SEP-06 03.25.13.152
    0141412179 25-SEP-06 03.25.38.424
    0123456789 02-JUN-06 03.26.57.687
    0607069617 02-JUN-06 03.27.02.720
    0014141168 26-SEP-06 03.30.55.290
    0618769562 25-SEP-06 03.31.21.141
    0141411683 25-SEP-06 03.31.45.952

    SUBSTR(CLI SUBSTR(CALL_DATE,1,22)
    ---------- ----------------------
    0607069617 25-SEP-06 03.32.14.542
    0618769562 25-SEP-06 03.32.30.433
    0 25-SEP-06 03.32.43.292
    0141412179 25-SEP-06 03.33.07.166
    0 25-SEP-06 03.33.56.086
    0 25-SEP-06 03.34.03.918
    0123456789 26-SEP-06 03.34.21.193
    0 25-SEP-06 03.34.25.484
    0 25-SEP-06 03.34.39.126
    0 25-SEP-06 03.34.40.354
    0 25-SEP-06 03.34.51.231


    2)
    SQL> desc events
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    EVENT_TYPE_ID NOT NULL NUMBER(19)
    EVENT_DATE NOT NULL TIMESTAMP(6)
    GAME_ID NUMBER(19)

    insert into events values ('&EVENT_TYPE_ID','&EVENT_DATE',&GAME_ID')

    SQL> select substr(event_type_id,1,10),substr(event_date,1,20) ,substr(game_id,1,10) from events where game_id in (1918,1919,1920,1939,1958,1979,1999,2018,2040,2041 ,2061)

    SUBSTR(EVE SUBSTR(EVENT_DATE,1, SUBSTR(GAM
    ---------- -------------------- ----------
    3 26-APR-06 06.11.50.8 1939
    4 26-APR-06 06.12.05.6 1939
    5 26-APR-06 06.16.13.5 1939
    3 09-MAY-06 06.18.59.7 1920
    4 09-MAY-06 06.22.43.7 1920
    3 12-MAY-06 04.24.46.2 1920
    4 12-MAY-06 04.46.22.5 1920
    3 12-MAY-06 04.29.07.4 1920
    4 12-MAY-06 04.39.31.1 1920
    3 12-MAY-06 04.29.35.3 1920
    4 12-MAY-06 04.30.02.8 1920

    SUBSTR(EVE SUBSTR(EVENT_DATE,1, SUBSTR(GAM
    ---------- -------------------- ----------
    3 26-SEP-06 12.19.27.6 1958
    4 26-SEP-06 12.29.37.9 1958
    5 01-JUN-06 12.26.37.2 1958
    3 02-JUN-06 11.53.49.0 1979
    6 02-JUN-06 11.54.00.5 1979
    4 02-JUN-06 11.54.55.5 1979
    3 02-JUN-06 11.55.03.7 1979
    4 02-JUN-06 11.57.40.7 1979
    3 02-JUN-06 11.57.43.5 1979
    4 02-JUN-06 11.59.47.2 1979
    3 14-SEP-06 02.24.13.8 1999

    SUBSTR(EVE SUBSTR(EVENT_DATE,1, SUBSTR(GAM
    ---------- -------------------- ----------
    4 14-SEP-06 02.55.18.7 1999
    3 14-SEP-06 06.44.40.1 1999
    4 14-SEP-06 06.52.57.9 1999

    3 22-SEP-06 04.05.09.5 2018
    4 22-SEP-06 05.24.14.7 2018
    5 22-SEP-06 05.24.25.0 2018
    4 24-SEP-06 03.17.54.8 2018
    3 24-SEP-06 03.19.00.1 2018

    3) INSERT INTO games VALUES ('&ID'.'&NAME')

    SQL> desc games
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ID NOT NULL NUMBER(19)
    NAME NOT NULL VARCHAR2(255)


    select substr(id,1,10),substr(name,1,25) from games;

    SUBSTR(ID, SUBSTR(NAME,1,25)
    ---------- -------------------------
    1918 Copy of QN27030628
    1919 Copy of Copy of QN0104061
    1920 Copy of Copy of Copy of Q
    1939 Alex Game 8
    1958 QN27030628 Lee
    1979 Copy of QN01040611 9
    1999 Ale's Game
    2018 TF1 Game test 1
    2040 Test Game TF1sarah
    2041 BTAgilemedia Game Test
    2061 Copy of Copy of QN0104060

    Your help would be highly appreciated.

    Thanks
    Jayesh

  2. #2
    Join Date
    Oct 2006
    Posts
    14
    I am sending herewith SQL statement for populating data into the concern tables
    To make easier for further testing your script.

    insert into calls values (0772740929, 22-SEP-06 05.22.44.123)
    insert into calls values (0882740929, 22-SEP-06 05.22.44.123)
    insert into calls values (0772740929, 25-SEP-06 05.22.44.123)
    insert into calls values (0662740929, 27-SEP-06 05.22.44.123)
    insert into calls values (0452740929, 22-SEP-06 05.22.44.123)
    insert into calls values (0992740929, 24-SEP-06 05.22.44.123)
    insert into calls values (0992740929, 26-SEP-06 05.22.44.123)


    insert into events values (3, 22-SEP-06 05.22.44.123,1918)
    insert into events values (4, 22-SEP-06 05.32.44.123,1918)
    insert into events values (3, 24-SEP-06 05.22.44,1920)
    insert into events values (4, 24-SEP-06 05.42.44,1920)
    insert into events values (3, 26-SEP-06 05.22.44,1958)
    insert into events values (4, 26-SEP-06 05.52.44,1958)

    Insert into games values (1918,’ Copy of QN27030628’)
    Insert into games values (1920,’ Test Game TF1sarah’)
    Insert into games values (1958,’ Test Car Race’)


    jayesh

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, what have you tried so far ?

  4. #4
    Join Date
    Oct 2006
    Posts
    14

    unsuccessful query

    I tried to write a query but remain unsuccessful to achieve the output.

    I'll appreciate if you can provide me the script.

    Yuor hep would be appreciated.

    thanks in advance.

    jayesh

  5. #5
    Join Date
    Oct 2006
    Posts
    14

    Table script

    I am sending herewith the script for creation of tables to provide me the script about my desired report

    Create table calls1 (cli varchar2(255) not null, call_date timestamp)
    Create table events1 (event_type_id number(19) not null, event_date timestamp not null, game_id number(19))
    Create table games1(id number(19) not null, name varchar2(255) not null)

    Thanks in advance.

    jayesh

  6. #6
    Join Date
    Oct 2006
    Posts
    14
    Any development or idea from your side.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •