Home » SQL & PL/SQL » SQL & PL/SQL » reports using three tables
reports using three tables [message #197487] Wed, 11 October 2006 07:52 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member
Hi,

I'm getting the error message that I need to submit the form as it expires the session. Therefore, I am sending you thie query directly to you.

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.

Thanks

jayesh

Report message to a moderator

Re: reports using three tables [message #197488 is a reply to message #197487] Wed, 11 October 2006 07:53 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

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 I’m 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')

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)


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')

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)

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)

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

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



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’)

Report message to a moderator

Re: reports using three tables [message #197522 is a reply to message #197488] Wed, 11 October 2006 10:59 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
jaydba wrote on Wed, 11 October 2006 08:53


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

1) desc calls

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

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

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

insert into calls values (0772740929, 22-SEP-06 05.22.44.123)
insert into calls values (0882740929, 22-SEP-06 05.22.44.123)



These are invalid INSERT statements for that table structure.

Report message to a moderator

Re: reports using three tables [message #197960 is a reply to message #197522] Fri, 13 October 2006 08:47 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member
Can you pl. correct it and give me the script asap.

thanks in advance.

jayesh

Report message to a moderator

Re: reports using three tables [message #197966 is a reply to message #197960] Fri, 13 October 2006 09:07 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
lol
funny guy!

Report message to a moderator

Previous Topic: ORA-29024: Certificate validation failure
Next Topic: GRANTING PROBLEM ,TELL ME THE CORRECT SYNTAX.WITH EXAMPLE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 05 16:04:57 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.