PL/SQL :: Summation Of A Bucket
Jun 13, 2012
i am facing a very challenging situation and i am not able to write a query. consider the following data
SELECT 16 num, 17 num2 , 3 num3, 22 num4, 10 num5 FROM dual
UNION ALL
SELECT 9 num, 15 num2 , 21 num3, 2 num4, 24 num5 FROM dual
UNION ALL
[Code]....
[URL]..... i am trying to write a query that given two values it will give you summation of the values within a particular bucket. for example, please see attachments, highlight in yellow.
if value 15 and 19 is given, i want to find the total sum of all values yellow (capture.jpg).
if value 8 and 21 is given, i want to find the total sum of all values in yellow (capture2.jpg)
if value 8 and 14 is given, i want to find the total sum all of values in yellow (capture3.jpg)
basically, when given two values, i draw a square or rectangle from starting number(first number) to end number and sum up all the values
write a query for such scenario. i heard it can be done with analytic functions but dont know how to use it. im using oracle 10g and 11g
View 4 Replies
Sep 18, 2012
I have a table with 2 columns storing information on start time and end time and i want to find out the time difference between them and i want a total for this , how can i get the summation on this calculated duration field.
create table ot_job (jh_no number,jh_start_01 date ,jh_end_dt date,jh_st_time varchar2(30),jh_end_time varchar2(30));
insert into ot_job values (1,'01-SEP-2012','01-SEP-2012','11:30','11:50');
insert into ot_job values (2,'01-SEP-2012','01-SEP-2012','08:15','14:00');
insrt into ot_job values (3,'01-SEP-2012','02-SEP-2012','23:00','23:00');
SQL> SELECT jh_no, jh_end_time, jh_st_time,
2 (TRUNC ( ( TO_DATE (SUBSTR (jh_end_time, 1, 5), 'HH24:MI')
3 - TO_DATE (SUBSTR (jh_st_time, 1, 5), 'HH24:MI')
4 )* 24 )
[code]...
-- i want to add a total for this duration column .
View 10 Replies
View Related
Aug 16, 2012
I need joining the below 2 tables. I had used "between and " for Joining the table by using Inner Bound and Outer bound Key Columns and i use (current date - due_dt) for day calculation
My requirement is to show all the time buckets if at all no days falls in the particular time bucket.as like Outer join on Time bucket table
Loan_Account_summary
SNo Due_dt AMT
-----------------------------
102-08-201250000
203-05-201245000
309-05-201230000
415-09-201225000
531-05-201215000
608-06-201235788
719-10-201255000
Time Bucket
TB_IDTB_DESCReserve%Innner Bound outer Bound
------------------------------------------------------------------
1011-72.25 1 7
1028-302.5 8 30
10331-603 31 60
10461-901.75 61 90
10591-1201.5 91 120
106121-1801.25 121 180
107180+0 181 99999
Expected Output
TB_DESC Reserve AMT
----------------------------
1-7500000
8-30154822
31-600
61-90750000
91-1200
120-1800
180+65842
View 2 Replies
View Related
May 19, 2011
I have a case expression as follows:
(CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa
I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.
View 1 Replies
View Related
