Home » SQL & PL/SQL » SQL & PL/SQL » Complex query - Time difference within duplicates (Oracle9 NIX)
Complex query - Time difference within duplicates [message #362132] Mon, 01 December 2008 03:02 Go to next message
andybob
Messages: 6
Registered: December 2008
Junior Member
Hello,

I have a table that looks like this:

ColName   DataType
SEQ_NO    VARCHAR2
ACCNO     VARCHAR2
REGDATE   DATE

I want it to count records that has duplicate ACCNOs and the timedifference is greater than 10 minutes or records that has unique ACCNOs.

SEQ_NO    ACCNO     REGDATE
1         00001     01.01.2008 12:00:00
2         00001     01.01.2008 12:08:00
3         00001     01.01.2008 12:11:00
4         00002     02.02.2008 10:00:00
5         00002     02.02.2008 10:01:00
6         00002     02.02.2008 10:02:00
7         00003     03.03.2008 08:00:00
8         00003     03.03.2008 08:11:00
9         00003     03.03.2008 08:22:00
10        00003     03.03.2008 08:33:00
11        00004     04.04.2008 06:00:00

This should result 2+1+4+1 = 8

I hope somebody can help me with this.

Kind regards,
Andy Bock




Re: Complex query - Time difference within duplicates [message #362135 is a reply to message #362132] Mon, 01 December 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Post what you already tried.

Regards
Michel
Re: Complex query - Time difference within duplicates [message #362138 is a reply to message #362132] Mon, 01 December 2008 03:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There is a way .

But how do you get results as

Quote:
2+1+4+1 = 8


Smile
Rajuvan.
Re: Complex query - Time difference within duplicates [message #362143 is a reply to message #362132] Mon, 01 December 2008 03:25 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@andybob,

Quote:

I want it to count records that has duplicate ACCNOs and the timedifference is greater than 10 minutes or records that has unique ACCNOs.


I didn't quite get what you mentioned in your above statement... Can you explain it with the examples you gave?

Regards,
Jo
Re: Complex query - Time difference within duplicates [message #362144 is a reply to message #362138] Mon, 01 December 2008 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I nearly ubnderstand what the required result is, but some clarification is required:

These rows count as 1 because they're all within 10 minutes of each other
4         00002     02.02.2008 10:00:00
5         00002     02.02.2008 10:01:00
6         00002     02.02.2008 10:02:00

These rows count as 4, as they're all more than 10 minutes apart
7         00003     03.03.2008 08:00:00
8         00003     03.03.2008 08:11:00
9         00003     03.03.2008 08:22:00
10        00003     03.03.2008 08:33:00

This row counts as 1 as it's unique
11        00004     04.04.2008 06:00:00


I don't know how the OP wants us to count these rows - the 3rd row is more than 10 minutes after the first, but within 10 minutes of the second.
1         00001     01.01.2008 12:00:00
2         00001     01.01.2008 12:08:00
3         00001     01.01.2008 12:11:00


How should the following be counted:
1         00001     01.01.2008 12:00:00
2         00001     01.01.2008 12:08:00
3         00001     01.01.2008 12:11:00
3         00001     01.01.2008 12:17:00

Is that 1 record - no record more than 10 mins after the previous
2 records - 1&2 within 10 minutes of each other and 2,3,4 within 10 minutes of each other
3 records - 2 within 10 minutes of 1, but 3,4 more than 10 minutes after 1

A more detailed explanation of how to calculate the results is needed.
Re: Complex query - Time difference within duplicates [message #362152 is a reply to message #362144] Mon, 01 December 2008 03:47 Go to previous messageGo to next message
andybob
Messages: 6
Registered: December 2008
Junior Member
Thanks all for replies. I am sorry for not posting code and my bad explanations.

The answer for JRowbottons Q is that the example he gave should be counted as 1 record.

If the ACCNO is duplicate it should count +1 if REGDATE is more than 10mins ahead of previous record with same ACCNO.

Dont know if that explenation made more sense Smile

CREATE TABLE T1
(
  SEQ_NO   VARCHAR2(15 BYTE),
  ACC      VARCHAR2(8 BYTE),
  REGDATE  DATE                                 NOT NULL
)

INSERT INTO t1 VALUES ('1','00001',TO_DATE('01.01.2008 12:00:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('2','00001',TO_DATE('01.01.2008 12:08:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('3','00001',TO_DATE('01.01.2008 12:11:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('4','00002',TO_DATE('02.02.2008 10:00:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('5','00002',TO_DATE('02.02.2008 10:01:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('6','00002',TO_DATE('02.02.2008 10:02:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('7','00003',TO_DATE('03.03.2008 08:00:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('8','00003',TO_DATE('03.03.2008 08:11:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('9','00003',TO_DATE('03.03.2008 08:22:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('10','00003',TO_DATE('03.03.2008 08:33:00','DD.MM.YYYY HH:MI:SS'));
INSERT INTO t1 VALUES ('11','00004',TO_DATE('03.03.2008 06:00:00','DD.MM.YYYY HH:MI:SS'));




Re: Complex query - Time difference within duplicates [message #362153 is a reply to message #362152] Mon, 01 December 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Regards
Michel
Re: Complex query - Time difference within duplicates [message #362157 is a reply to message #362135] Mon, 01 December 2008 03:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

andybob wrote on Mon, 01 December 2008
I want it to count records that has duplicate ACCNOs and the timedifference is greater than 10 minutes or records that has unique ACCNOs.




JRowbottom wrote on Mon, 01 December 2008
These rows count as 1 because they're all within 10 minutes of each other




andybob Again wrote on Mon, 01 December 2008
f the ACCNO is duplicate it should count +1 if REGDATE is more than 10mins ahead of previous record with same ACCNO.



Re: Complex query - Time difference within duplicates [message #362160 is a reply to message #362144] Mon, 01 December 2008 04:06 Go to previous messageGo to next message
andybob
Messages: 6
Registered: December 2008
Junior Member
JRowbotton: Did my answer clearify it?

Count all duplicate (same ACC) that are more than 10 minutes apart from the previous record with same ACC.

I have tried fiddeling around with a difftime function, various regdate conversions (REGDATE + 10/1440) or even stuff like "AND (a.regdate - b.regdate) * 24 * 60" none of which I can get nested into a working query returning the correct count..
Re: Complex query - Time difference within duplicates [message #362167 is a reply to message #362152] Mon, 01 December 2008 04:19 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Quote 1 =>
This should result 2+1+4+1 = 8


Quote 2 =>

The answer for JRowbottons Q is that the example he gave should be counted as 1 record.

If the ACCNO is duplicate it should count +1 if REGDATE is more than 10mins ahead of previous record with same ACCNO.



In the quote 1, "ACCNO 00001" is returning 2. In the second, it's returning 1. How is that??


Rajy
Re: Complex query - Time difference within duplicates [message #362168 is a reply to message #362167] Mon, 01 December 2008 04:22 Go to previous messageGo to next message
andybob
Messages: 6
Registered: December 2008
Junior Member
Sorry!

Just stick with:

How should the following be counted:

1         00001     01.01.2008 12:00:00
2         00001     01.01.2008 12:08:00
3         00001     01.01.2008 12:11:00
3         00001     01.01.2008 12:17:00


Is that 1 record - no record more than 10 mins after the previous
2 records - 1&2 within 10 minutes of each other and 2,3,4 within 10 minutes of each other
3 records - 2 within 10 minutes of 1, but 3,4 more than 10 minutes after 1

The answer for JRowbottons Q is that the example he gave should be counted as 1 record.

If the ACCNO is duplicate it should count +1 if REGDATE is more than 10mins ahead of previous record with same ACCNO.


Re: Complex query - Time difference within duplicates [message #362169 is a reply to message #362160] Mon, 01 December 2008 04:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think this queryhere should give you everything you need to build a solution:
select seq_no
      ,acc
      ,(regdate - nvl(lag(regdate) over (partition by acc order by to_number(seq_no)),regdate))*24*60 minutes_difference
from   t1;


Re: Complex query - Time difference within duplicates [message #362170 is a reply to message #362153] Mon, 01 December 2008 04:29 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Michel Cadot wrote on Mon, 01 December 2008 11:49
Have a look at LAG/LEAD functions.

Regards
Michel



Rajy
Re: Complex query - Time difference within duplicates [message #362174 is a reply to message #362170] Mon, 01 December 2008 04:39 Go to previous messageGo to next message
andybob
Messages: 6
Registered: December 2008
Junior Member
Thanks all for your replies.

I was still not able to get a corerct count but I did learn something about writing forum posts Smile

I probably should not have posted this question in this forum.
I will ask my questions in some newbie forum instead.

I am still not able to get the correct count for the exmaples/questions above based on the query below. Guess it is just me being to stupid to understand the answers Smile

Thanks again for your help people!
Excellent response time in this forum.

SELECT seq_no
      ,acc
      ,(regdate - NVL(lag(regdate) OVER (PARTITION BY acc ORDER BY TO_NUMBER(seq_no)),regdate))*24*60 minutes_difference
FROM   T1


SEQ_NO;ACC;MINUTES_DIFFERENCE
1;00001;0
2;00001;8
3;00001;3
4;00002;0
5;00002;1
6;00002;1
7;00003;0
8;00003;11
9;00003;11
10;00003;11
11;00004;0


Re: Complex query - Time difference within duplicates [message #362183 is a reply to message #362174] Mon, 01 December 2008 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, I overlooked one point- that you wanted to count each group at least once.
This should do the job for you. I've removed the NVL from the minutes_difference function, so each group will have a row in it where min-dif is null.
If you count the null rows, plus the rows where minutes_difference > 10 then I reckon yuo get the answer you're looking for.

select acc,count(*)
from  (select seq_no
             ,acc
             ,(regdate - lag(regdate) over (partition by acc order by to_number(seq_no)))*24*60 minutes_difference
       from   t1)
where minutes_difference > 10 or minutes_difference is null
group by acc
Re: Complex query - Time difference within duplicates [message #362205 is a reply to message #362183] Mon, 01 December 2008 05:43 Go to previous messageGo to next message
andybob
Messages: 6
Registered: December 2008
Junior Member
Thanks alot!

I've been googling around for a bit and this was the only working solution for my problem I have found.

I did read up on LAG/LEAD and even learned a thing or two.
Re: Complex query - Time difference within duplicates [message #362213 is a reply to message #362205] Mon, 01 December 2008 05:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it like this too, but I can't quite convince myself that it will always work...

select acc,max(level)
from   t1
connect by prior acc = acc and prior seq_no = seq_no-1 and prior regdate < regdate-(10/(24*60))
group by acc
order by acc;
Previous Topic: Problem in execution of a function having clob datatype
Next Topic: rows & columns dynamic [merged many]
Goto Forum:
  


Current Time: Sun Dec 11 02:32:06 CST 2016

Total time taken to generate the page: 0.08036 seconds