No question at this time
DBA Top 10
1 M. Cadot 23300
2 A. Kavsek 15200
3 M. Hidayathullah ... 11000
4 B. Vroman 8300
5 P. Wisse 6000
6 T. Boles 5000
7 J. Schnackenberg 3800
8 G. Lambregts 2200
8 P. Knibbs 2200
10 K. Van Nieuwenhov... 2100
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48039
Total active users1770
Act. users last 24h10
Act. users last hour1
Registered user hits last week205
Registered user hits last month1405
Go up

Subsequent Addition of Hourly Data
Next thread: Regexp_substr - required last occurrence of string pattern
Prev thread: Patch CPU July 2017

Message Score Author Date
Hi All, I want to fetch hourly data which is th...... rahul singh Sep 05, 2017, 11:04
Hello Rahul, Remarks: - there might be "gaps",...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Sep 05, 2017, 14:12
Thanks Bruno for your prompt response, I will chec...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts rahul singh Sep 05, 2017, 15:13
hi bruno, Can you explain the concept of below ...... rahul singh Sep 06, 2017, 07:45
Hello Rahul, this is just a way to create some ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Sep 06, 2017, 08:49
Thanks Bruno, I got it.... rahul singh Sep 06, 2017, 09:10
Hi Bruno, i got the required data from below mo...... rahul singh Sep 08, 2017, 15:19
Dear, Try to send through html format. Regar...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Mirza Hidayathullah Baig Sep 08, 2017, 19:56
Hi Bruno, i dont want to calculate all hour dat...... rahul singh Sep 09, 2017, 10:28

Follow up by mail Click here


Subject: Subsequent Addition of Hourly Data
Author: rahul singh, India
Date: Sep 05, 2017, 11:04, 15 days ago
Os info: Windows
Oracle info: Oracle 11g
Message: Hi All,

I want to fetch hourly data which is the addition of previous hours data and current hour data, below is the example which shows the exact requirement.

1st hour below is the count:

SJT_EN SJT_EX RJT_EN RJT_EX SV_EN SV_EX TP_EN
1 1 1 1 1 1 1

In next hour (1st hour + Current hour)

SJT_EN SJT_EX RJT_EN RJT_EX SV_EN SV_EX TP_EN
1+2 1+3 1+1 1+2 1+1 1+2 1+1

+2 +3 +1 +2 +1 +2 +1 are the current hour and
1 1 1 1 1 1 1 are the previous hour data

at the end of 24 hours i need addition of all the 23 +1 hour data.

Below is my query which fetch hourly data. but it does not include the previous hours data, can anyone pls help me on this.

SELECT Nvl(To_Char(DECODE (vali_stat_id,'1','VE','2','DN','3','AZ','4','AN','5','WE','6','CH','7','AP','8','CH','9','SA','10','AS','11','JN','12','GH')),'TOTAL'
) AS STS,
Sum(CASE WHEN pass_id=10 AND validation_type=0 THEN 1 ELSE 0 END) SJT_EN,
Sum(CASE WHEN pass_id=10 AND validation_type=1 THEN 1 ELSE 0 END) SJT_EX,
Sum(CASE WHEN pass_id=90 AND validation_type=0 THEN 1 ELSE 0 END) RJT_EN,
Sum(CASE WHEN pass_id=90 AND validation_type=1 THEN 1 ELSE 0 END) RJT_EX,
Sum(CASE WHEN pass_id=81 AND validation_type=0 THEN 1 ELSE 0 END) SV_EN,
Sum(CASE WHEN pass_id=81 AND validation_type=1 THEN 1 ELSE 0 END) SV_EX,
Sum(CASE WHEN pass_id=21 AND validation_type=0 THEN 1 ELSE 0 END) TP_EN,
Sum(CASE WHEN pass_id=21 AND validation_type=1 THEN 1 ELSE 0 END) MP_EX ,
FROM qr_validation WHERE record_date >=SYSDATE-1/24
GROUP BY rollup (vali_stat_id) ORDER BY vali_stat_id;

thanks in advance.


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: Bruno Vroman, Belgium
Date: Sep 05, 2017, 14:12, 15 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hello Rahul,

Remarks:
- there might be "gaps", so I generate a list of "interesting hours" and I use "left join" to join this with the actual data.
Then I group the data "by hour", and last I SUM "the "trick" is to use SUM OVER RANGE UNBOUNDED
- I have "simplified" your table structure, you can generalize my example easily.
- What if the data spans multiple days? You have maybe to take care of this case...
WITH qr_validation AS

( SELECT TRUNC( sysdate ) + 1/24 record_date, 1 x FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 1.1/24, 2 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 1.5/24, 3 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 2.3/24, 4 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 2.6/24, 5 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 2.6/24, 4 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 3.5/24, 3 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 5.0/24, 2 FROM dual
UNION ALL SELECT TRUNC( sysdate ) + 5.5/24, 1 FROM dual
)
, interesting_hours AS
( SELECT TRUNC( sysdate ) + ( LEVEL - 1 ) / 24 hour
FROM dual
CONNECT BY LEVEL <= 8 /* just an example to go from 0 to 7, i.e. 00:00:00 to 07:59:59 */
)
, data_per_hour AS
( SELECT TRUNC( record_date, 'HH24' ) hour
, SUM( x ) sumx
FROM qr_validation
GROUP BY TRUNC( record_date, 'HH24' )
)
SELECT 'up to ' || TO_CHAR( ih.hour, 'DD-MON-YYYY HH24' ) || ':59:59' time
, SUM( dph.sumx ) OVER ( ORDER BY ih.hour RANGE UNBOUNDED PRECEDING ) sjt_en
FROM interesting_hours ih
LEFT OUTER JOIN data_per_hour dph
ON dph.hour = ih.hour
ORDER BY ih.hour
;
TIME SJT_EN
-------------------------------- --------
up to 05-SEP-2017 00:59:59
up to 05-SEP-2017 01:59:59 6
up to 05-SEP-2017 02:59:59 19
up to 05-SEP-2017 03:59:59 22
up to 05-SEP-2017 04:59:59 22
up to 05-SEP-2017 05:59:59 25
up to 05-SEP-2017 06:59:59 25
up to 05-SEP-2017 07:59:59 25
Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: rahul singh, India
Date: Sep 05, 2017, 15:13, 15 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Thanks Bruno for your prompt response, I will check and let you know in case of any issue.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: rahul singh, India
Date: Sep 06, 2017, 07:45, 15 days ago
Message: hi bruno,

Can you explain the concept of below union all.


SELECT TRUNC( sysdate ) + 1/24 record_date, 1 x FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 1.1/24, 2 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 1.5/24, 3 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 2.3/24, 4 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 2.6/24, 5 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 2.6/24, 4 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 3.5/24, 3 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 5.0/24, 2 FROM dual

UNION ALL SELECT TRUNC( sysdate ) + 5.5/24, 1 FROM dual
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: Bruno Vroman, Belgium
Date: Sep 06, 2017, 08:49, 15 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Rahul,

this is just a way to create some "sample data". Run this select "alone" and you will see what is used to run the example (prior to this, run "alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';")
You don't have to use this piece as you have an actual table "qr_qualification" but I used it because I don't have your table and data.

An alternative to this "WITH qr_qualification AS ( SELECT ... )" could have been:
CREATE TABLE qr_qualification( record_date DATE, x NUMBER );
INSERT INTO qr_qualification VALUES ( TRUNC( sysdate ) + 1 / 24, 1 );
INSERT INTO qr_qualification VALUES ( ...

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: rahul singh, India
Date: Sep 06, 2017, 09:10, 15 days ago
Message: Thanks Bruno,

I got it.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: rahul singh, India
Date: Sep 08, 2017, 15:19, 12 days ago
Message: Hi Bruno,

i got the required data from below modified query. now another issue is when i try to send the output over mail the formatting is not proper.

I am taking this output in txt,csv its showing properly when in txt and in csv,but when i tried to send it though mail the formatting is not proper.

i have set the following formatting but it's still not in formatted.

set feedback off pagesize 10 linesize 500 VERIFY OFF;

Query:

SELECT Trunc(sysdate,'HH'),Nvl(To_Char(DECODE (vali_sta_id,'1','VER','2','DNG','3','AZN','4','AND','5','WEH','6','CHK','7','APR','8','CHK','9','SAN','10','ASA','11','JNG','12','GHA')),'TOTAL'
) AS STS,
Sum(CASE WHEN p_id=10 AND validation_type=0 THEN 1 ELSE 0 END) S_EN,
Sum(CASE WHEN p_id=10 AND validation_type=1 THEN 1 ELSE 0 END) S_EX,
Sum(CASE WHEN p_id=90 AND validation_type=0 THEN 1 ELSE 0 END) R_EN,
Sum(CASE WHEN p_id=90 AND validation_type=1 THEN 1 ELSE 0 END) R_EX,
Sum(CASE WHEN p_id=81 AND validation_type=0 THEN 1 ELSE 0 END) SV_EN,
Sum(CASE WHEN p_id=81 AND validation_type=1 THEN 1 ELSE 0 END) SV_EX,
Sum(CASE WHEN p_id=21 AND validation_type=0 THEN 1 ELSE 0 END) TP_EN ,
Sum(CASE WHEN p_id=21 AND validation_type=1 THEN 1 ELSE 0 END) TP_EX ,
Sum(CASE WHEN p_id IN (10,90,81,21) AND validation_type=0 THEN 1 ELSE 0 END) TOTAL_EN,
Sum(CASE WHEN p_id IN (10,90,81,21) AND validation_type=1 THEN 1 ELSE 0 END) TOTAL_EX
FROM qr_validation@QRCODE
GROUP BY rollup (vali_sta_id), Trunc(sysdate,'HH')


The mail Ouput

DT STS SJT_ENTRY SJT_EXIT RJT_ENTRY RJT_EXIT SV_ENTRY SV_EXIT TP_ENTRY MP_EXIT TOTAL_ENTRY TOTAL_EXIT
--------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
07-SEP-17 VER 25 34 6 7 7 14 2 7 40 62
07-SEP-17 DNG 81 77 9 7 46 47 17 18 153 149
07-SEP-17 AZN 26 50 2 13 9 11 2 15 39 89
07-SEP-17 AND 237 283 55 45 67 61 55 66 414 455
07-SEP-17 WEH 224 213 20 23 70 69 73 67 387 372
07-SEP-17 CHK 52 83 18 23 12 13 35 26 117 145
07-SEP-17 APR 20 24 3 7 3 4 10 19 36 54
07-SEP-17 CHK 31 34 11 12 5 9 20 24 67 79
07-SEP-17 SAN 52 51 18 17 11 15 18 24 99 107
07-SEP-17 ASA 18 25 1 3 0 0 8 13 27 41
07-SEP-17 JNG 18 15 0 0 8 1 7 7 33 23
07-SEP-17 GHA 121 151 21 20 54 63 91 86 287 320
07-SEP-17 TOTAL 905 1040 164 177 292 307 338 372 1699 1896


Can you pls help me on this i need the output as it showing in sql developer.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Sep 08, 2017, 19:56, 12 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Dear,

Try to send through html format.

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Subsequent Addition of Hourly Data
Author: rahul singh, India
Date: Sep 09, 2017, 10:28, 12 days ago
Message: Hi Bruno,

i dont want to calculate all hour data, i only want in below fashion.

Suppose at 12:00 hour sum of tnx is 10
at 01:00 hour sum of tns is 5 (then 10+5)=15
at 02:00 hour sum of Tnx is 5 (then 15+5)=20

I only want the query should be display the data of 02:00 hours i.e 20.

Can you pls help me on this.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here