Home » Developer & Programmer » Application Express & MOD_PLSQL » creating a chart of creation date/times (4.0)
creating a chart of creation date/times [message #550832] Thu, 12 April 2012 14:39 Go to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
Hopefully this will help someone else too:
I want to create a chart(probably best with scatter) that shows at least:
1. Peak times of record creation according to each day so displayed every 2 hours

Each record is timestamped in the format: 11-APR-2012 20:31:24


Table for the records are is 'Sales'
Sales_Reference
Sales_Creation

I have tried the simple wizard however can't figure out the sql code required to complete the chart correctly..
Re: creating a chart of creation date/times [message #550835 is a reply to message #550832] Thu, 12 April 2012 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you could provide a test case (CREATE TABLE and INSERT INTO several sample records), I'd try to create a report.
Re: creating a chart of creation date/times [message #550836 is a reply to message #550835] Thu, 12 April 2012 14:53 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
hello again !...

Ive attempted to create a line graph and this is the code right now:

select null link, CALL_CREATION label, CALL_REFNO value1
from "HELPDESKAPP"."HELPDESK_CALL"

which is a total mess atm! is this what you require ?

X Axis shouuld be Time (Hours)
Y Axis Number of calls in 10's or whatever


If you think the data will suit a better chart then let me know Smile

[Updated on: Thu, 12 April 2012 14:55]

Report message to a moderator

Re: creating a chart of creation date/times [message #550837 is a reply to message #550836] Thu, 12 April 2012 14:59 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You need to understand that we don't have your tables nor data - that is the reason we ask posters to provide a test case.

I really wouldn't know why you got a "total mess"; most probably because you created it so.
Re: creating a chart of creation date/times [message #550839 is a reply to message #550837] Thu, 12 April 2012 15:02 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
what do you mean by test case?

The table is called
'Sales'
and im guessing the only data from the table required to fill the charts would be:

Sales_Reference
Sales_Creation

As its showing each record (Reference) and when they are created spread out each hour (Creation)

Creation format is automatically generated in the following format e.g. '11-APR-2012 20:31:24'
Re: creating a chart of creation date/times [message #550852 is a reply to message #550839] Fri, 13 April 2012 01:02 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I don't understand what you are saying as your messages are in contradiction.

Once you say that you have a table named SALES with columns SALES_REFERENCE and SALES_CREATION.
Next time you say that query you use selects CALL_CREATION and CALL_REFNO from HELPDESK_CALL.

Which one is true?

"Test case" I asked should look like this:
create table sales
  (sales_reference number,
   sales_creation  date
  );

insert all
  into sales (sales_reference, sales_creation) values (1, to_date('11.04.2012 20:31.24', 'dd.mm.yyyy hh24:mi:ss'))
  into sales (sales_reference, sales_creation) values (2, to_date('11.04.2012 21:45.20', 'dd.mm.yyyy hh24:mi:ss'))
  into sales (sales_reference, sales_creation) values (3, to_date('12.04.2012 02:15.20', 'dd.mm.yyyy hh24:mi:ss'))
  into sales (sales_reference, sales_creation) values (4, to_date('12.04.2012 08:16.33', 'dd.mm.yyyy hh24:mi:ss'))
select * from dual;


Based on such a test case, we would be able to create a graph and suggest how to do that.

This seems to be quite straightforward; I can't imagine what you might have done wrong.
Re: creating a chart of creation date/times [message #551251 is a reply to message #550852] Mon, 16 April 2012 12:25 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
littlefoot or anyone else, I've just sorted other probs on the app... now i need to get this sorted... Smile
Please tell me how to create a test case that you require? it is based on the helpdesk call... i need to create the line graph which filters hours from the creation date/time data so 8am 9am 10am etc to show what the paek times are....
Re: creating a chart of creation date/times [message #551253 is a reply to message #551251] Mon, 16 April 2012 12:56 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/10063/0/
  • Attachment: test_case.png
    (Size: 38.35KB, Downloaded 203 times)
Re: creating a chart of creation date/times [message #551262 is a reply to message #551253] Mon, 16 April 2012 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very nice reply.

Regards
Michel
Re: creating a chart of creation date/times [message #551272 is a reply to message #551262] Mon, 16 April 2012 14:46 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
create table helpdesk_call
(call_refno number,
call_creation date
);

insert all
into helpdesk_call (call_refno, call_creation) values (1, to_date('11.04.2012 20:31.24', 'dd.mm.yyyy hh24:mi:ss'))
into helpdesk_call (call_refno, call_creation) values (2, to_date('11.04.2012 21:45.20', 'dd.mm.yyyy hh24:mi:ss'))
into helpdesk_call (call_refno, call_creation) values (3, to_date('12.04.2012 02:15.20', 'dd.mm.yyyy hh24:mi:ss'))
into helpdesk_call (call_refno, call_creation) values (4, to_date('12.04.2012 08:16.33', 'dd.mm.yyyy hh24:mi:ss'))
select * from dual;

there thats helpdesk_call table with the columns call_refno and call_creation
Re: creating a chart of creation date/times [message #551298 is a reply to message #551272] Tue, 17 April 2012 00:03 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, here's how I understood what you said: from these values, you need to select HOURS (20, 21, 02, 08) and display them on the graph. If that's so, here's a query:
select null link,
  call_refno,
  to_number(to_char(call_creation, 'hh24')) call_creation_hour
from helpdesk_call
order by call_refno

and the result:

./fa/10065/0/

If that's not what you are looking for, could you describe it once again? No problem if you draw it on a piece of paper, scan and attach with your next message.
Re: creating a chart of creation date/times [message #551337 is a reply to message #551298] Tue, 17 April 2012 07:40 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
at the moment my call_creation in sql workshop as DATE so in the data of the sql workshop it is displayed as only 04/16/2012.. where as in the actual application its formmated to show the hours and seconds... should I change the data type in my sql workshop to varchar2 or something so it shows hours and seconds in the actual data? the application is ok but im guessing the chart will not pick the hours/seconds up from the sql workshop

Re: creating a chart of creation date/times [message #551338 is a reply to message #551337] Tue, 17 April 2012 07:44 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
should I change the data type in my sql workshop to varchar2

No, not even in your dreams!!!

Date values should be stored in DATE datatype columns. If you choose VARCHAR2, expect a miserable end of your life (well, just kidding, but this will bring numerous problems, sooner or later).

To display dates in different format, use TO_CHAR function with appropriate format mask.

P.S. As far as I can tell, a VALUE column returned by a chart query must be a NUMERIC value. It means that you can't display these values in any format you want; choose something like HH24MISS or YYYYMMDD or similar (numberic) interpretation of a date value.

[Updated on: Tue, 17 April 2012 07:48]

Report message to a moderator

Re: creating a chart of creation date/times [message #551339 is a reply to message #551338] Tue, 17 April 2012 07:47 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
i've currently got to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') in default value which is uneditable by the user as it stamps it... how do I use to_char function for the actual data in sql workshop?
Re: creating a chart of creation date/times [message #551340 is a reply to message #551339] Tue, 17 April 2012 07:49 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In SQL Workshop?
select to_char(call_creation, 'dd.mm.yyyy hh24:mi:ss') from your_table
Re: creating a chart of creation date/times [message #551341 is a reply to message #551340] Tue, 17 April 2012 07:54 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
what if i wanted the chart to total the number of calls each days of the week?
16-APR-2012 so pickng out DD and identifying its a monday, tues etc and the X columns will be mon,tues,wed,thurs,fri... and Y the total number?
Re: creating a chart of creation date/times [message #551342 is a reply to message #551341] Tue, 17 April 2012 07:54 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
can oracle work out what day the dates are?
Re: creating a chart of creation date/times [message #551343 is a reply to message #551342] Tue, 17 April 2012 07:56 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> select to_char(sysdate, 'day') from dual;

TO_CHAR(S
---------
tuesday

SQL>


If you want to chart NUMBER OF CALLS, you'll first have to CALCULATE that number, and then display it. This is not a difficult task.

P.S. Here's an example:
SQL> select to_char(hiredate, 'day') n_day,
  2         count(*) cnt
  3  from emp
  4  group by to_char(hiredate, 'day');

N_DAY            CNT
--------- ----------
monday             1
thursday           5
sunday             1
tuesday            3
wednesday          2
friday             2

6 rows selected.

SQL>

[Updated on: Tue, 17 April 2012 07:58]

Report message to a moderator

Re: creating a chart of creation date/times [message #551344 is a reply to message #551343] Tue, 17 April 2012 07:58 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
do you mind telling me exactly how to do that? i think that chart will be better than the hours...
so ill display that in a bar chart i guess thats more presentable.
so X column is Day of the week and Y is number of calls
Re: creating a chart of creation date/times [message #551350 is a reply to message #551344] Tue, 17 April 2012 09:23 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
so first i have to create a table that shows the results?
Re: creating a chart of creation date/times [message #551379 is a reply to message #551350] Tue, 17 April 2012 14:15 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No, you don't need a table. Simply mimic what I've done in my previous message.
Re: creating a chart of creation date/times [message #551456 is a reply to message #551379] Wed, 18 April 2012 08:20 Go to previous messageGo to next message
newprogrammerSQ
Messages: 48
Registered: April 2012
Member
sorry this dont make sense to me do you mind explaining bit better?

select to_char(call_creation, 'day') n_day,
  2         count(*) cnt
  3  from helpdesk_call
  4  group by to_char(call_creation, 'day');

N_DAY            CNT
--------- ----------
monday             1
thursday           5
sunday             1
tuesday            3
wednesday          2
friday             2

6 rows selected.


i've tried putting that in the sql of my chart but that doesn't work
Re: creating a chart of creation date/times [message #551516 is a reply to message #551456] Wed, 18 April 2012 14:45 Go to previous message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know what to describe better ... I thought that it is straightforward. Use a Chart wizard, copy/paste that SQL when prompted and ugly but working chart is here.

At least, for me ...

./fa/10071/0/

A query:
select null link,
       to_char(hiredate, 'day') n_day,
       count(*) cnt
from emp
group by to_char(hiredate, 'day')


  • Attachment: chart.png
    (Size: 12.76KB, Downloaded 145 times)
Previous Topic: Error in intalling apex.
Next Topic: How do I make password login case sensitive?
Goto Forum:
  


Current Time: Thu Aug 21 17:52:14 CDT 2014

Total time taken to generate the page: 0.09526 seconds