SQL & PL/SQL :: Generate Hourly Based Number Of Orders Placed Report
Oct 11, 2011
I have a table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .
I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.
e.g Start Number : 1 and End Number : 1001.
Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.
Means
Range 1 1 -101 Range 2 102-200 Range 3 201-300 ... Range 10 901-1001
Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.
SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id from (
[Code].....
I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.
I am working on SQL Developer 1.5.1, i need to prepare hourly record of the activity on the database, for that i have a sql query that gives me the report as per selected columns on hourly basis.I need to prepare 24 reports a day.
Each time i have to go to the query , change the date as per hours like form 22:06:2011 10:00:00 to 22:06:2011 11:00:00 and get the report and export it in excel.
I want to automate the script so that whenever i run the script , it just asks me the date and runs the script 24 times and fetch me the hourly report of whole day.
the query syntax is something like this
Select ............................ ........................... .......................... from .......................... ........................ ........................ where And....................... And................... And......................
And req date between to-date( 22:06:2011 10:00:00) And to-date(22:06:2011 11:00:00) Order by 7,1,2,3,4,5
Is there any possibility that i can automate the script to automatically change the hour itself and generate a report in excel 24 times?
I have a query in reports.My requirement is that report should fire everyday in a year.When an order is cancelled/copied and also that when an order is cancelled then the line amount should be set to zero and order_status should automatically set cancelled.
EX: order_no amt product_name quote_no order_status 45434545 200 dell 10022 active when order cancelled then order_no amt product_name quote_no order_status 45434545 0 dell 10022 cancelled
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from (select customer_id from capitalPLAN where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165') order by price desc ) where rownum <= 25
I have a table in some db called retailer. I want to generate an email based trigger. What i want to achieve is as follows
I want to generate an email based trigger. What i want to do is that i should store the current retailer id which is until this point and then on each further insertions in retailer id i want that some trigger or script is running in background which is storing the retailer id. When the retailer id advances by 45 then a trigger should be generated saying that retailer id has advanced to 45 numbers from this date.
I have a requirement to generate a report in csv file, attached scripts, sql query and actual output and expected output.
In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.
I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.
Can we collect information(generate report) about user sessions connected, database hit ratios, memory usage and other system metrics of all target databases from OEM grid control repository database. Which tables/views in repository DB will be holding these information.
I need to generate a report much like a program guide from the script attached. My problem is that my script works, but it's wrong, all the information is wrong. Here's the two queries I came up with;
how do you generate serial numbers? i have an invoice where i have to add items, so i want the numbering to start from 1 everytime i start a new form and increment by 1 as i i enter a new record. For this i have put my code in when-new-record-instance but problem is if i delete a record and enter a new one, the serial number starts from the nex number.
For ex. if i have 4 items with serial numbers 1, 2, 3, 4 and i delete the item at no. 3 and add a new item , the serial number comes as 5.
I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.
I have a requirement to create a generic script to generate awr report on the basis of two timings . We are planning to do load test on multiple server. The user will key in the start time and the end time of the load test and the script should round off the user entered time to the nearest snapshot id and generate a awr report. I have tried using the dba_hist_snapshot table fields begin_interval_time and end_interval_time column. However it failed.
The servers are running on 11g as well as 10g and few are in RAC and few single instances.
Structure is like above. There is a Head Quarter (HQ1) under which we have different division like (DIV1 and DIV2). Under division we have different location where our shopping stores are operating and transactions are done (LOC1, LOC2 for DIV1) and (LOC3 for DIV2) respectively.
DESIRED OUTPUT User can generate reports at two levels i.e. Head Quarter level and Division Level.
When user wants report at Head Quarter Level : Page 1 (Should Display as below) HQ1 DIV1 LOC1 450 HQ1 DIV1 LOC2 400 HQ1 DIV2 LOC3 600
I would like to generate HTML awr report and save it to my local machine.
After running $ORACLE_HOME/rdbms/admin/awrrpt.sql in sqlplus and specifying HTML in (Enter value for report_type: HTML) i hvae get numerous html code in my sqlplus prompt.. I want to save the html report in local machine and open it by double clicking on it.. it will be opened in a browser..
I have come across a requirement to generate a report of all the users in Hyperion shared services console along with their previlages and also the different licences consumed by the users like Essbase, HFM, Hyperion Planning, Workforce planning etc.... The provisioning report in Shared services is not giving these details.
I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.
we have a admin wizard where in there are 850 officials whose details are like
emp id date of birth date of joining basic pay photo etc........
i have the photos of the officials out of the data base.i have created a report to generate the details of the officials. what i would like to have is to get the photo of each official from the file which is not in the data base.
i have the command in pre_form read_image_file('path where the photos are','msg.mcn');
this gives the same photo to all the officials. how do i be able to link the photos to their emp id and get it.