OraFAQ Forum: SQL & PL/SQL » Complex query - Time difference within duplicates

Home » SQL & PL/SQL » SQL & PL/SQL » Complex query - Time difference within duplicates (Oracle9 NIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Complex query - Time difference within duplicates [message #362132]

Mon, 01 December 2008 03:02

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362135 is a reply to message #362132]

Mon, 01 December 2008 03:13

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362138 is a reply to message #362132]

Mon, 01 December 2008 03:19

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

Rajuvan.

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362143 is a reply to message #362132]

Mon, 01 December 2008 03:25

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362144 is a reply to message #362138]

Mon, 01 December 2008 03:28

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.

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362152 is a reply to message #362144]

Mon, 01 December 2008 03:47

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'));

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362153 is a reply to message #362152]

Mon, 01 December 2008 03:49

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at LAG/LEAD functions.

Regards
Michel

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362157 is a reply to message #362135]

Mon, 01 December 2008 03:56

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.

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362160 is a reply to message #362144]

Mon, 01 December 2008 04:06

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..

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362167 is a reply to message #362152]

Mon, 01 December 2008 04:19

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362168 is a reply to message #362167]

Mon, 01 December 2008 04:22

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.

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362169 is a reply to message #362160]

Mon, 01 December 2008 04:28

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;

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362170 is a reply to message #362153]

Mon, 01 December 2008 04:29

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362174 is a reply to message #362170]

Mon, 01 December 2008 04:39

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362183 is a reply to message #362174]

Mon, 01 December 2008 04:55

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

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362205 is a reply to message #362183]

Mon, 01 December 2008 05:43

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.

Report message to a moderator

Re: Complex query - Time difference within duplicates [message #362213 is a reply to message #362205]

Mon, 01 December 2008 05:52

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;

Report message to a moderator

Previous Topic:	Problem in execution of a function having clob datatype
Next Topic:	rows & columns dynamic [merged many]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 13 22:21:18 CDT 2025