Home » SQL & PL/SQL » SQL & PL/SQL » how t0 generate missing dates
how t0 generate missing dates [message #280941] Thu, 15 November 2007 04:15 Go to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
hi all,

I have a data in table as attached in enclosed file
having qsi,time,oh values
and
I need the output as

ROUNDTIME1 QSI DAY
1 1/2/2007 10:00:00 AM 41 02/01/2007
2 1/2/2007 11:00:00 AM 41 02/01/2007
3 1/2/2007 12:00:00 PM 41 02/01/2007
4 1/2/2007 1:00:00 PM 41 02/01/2007
5 1/2/2007 2:00:00 PM 41 02/01/2007
6 10/22/2006 3:00:00 AM 182 22/10/2006
7 10/22/2006 4:00:00 AM 182 22/10/2006
8 10/22/2006 5:00:00 AM 182 22/10/2006
9 10/22/2006 6:00:00 AM 182 22/10/2006
10 10/22/2006 7:00:00 AM 182 22/10/2006
11 10/22/2006 8:00:00 AM 182 22/10/2006
12 10/22/2006 9:00:00 AM 182 22/10/2006

look in this the missing dates are generated for this i have written this query

SELECT distinct mint + (LEVEL-1)/24 roundtime1,qsi,day
FROM
(select qsi,to_char(time,'DD/MM/YYYY') day,
min(trunc(time,'HH24')) mint ,
max(trunc(time,'HH24')) maxt
from t1
group by qsi,to_char(time,'DD/MM/YYYY')
)
CONNECT BY LEVEL < ((maxt - mint) * 24 + 2)
order by qsi,roundtime1


but this generates too many duplicate rows that i don't want
and to remove those i have used the distinct which again
eaten up all the performace . as i have to apply this method to
the table having data in lakhs.

So. please suggest some alternative for that so that
there will be no performance issues


thanks in advance
  • Attachment: data.csv
    (Size: 1.08KB, Downloaded 172 times)
Re: how t0 generate missing dates [message #280946 is a reply to message #280941] Thu, 15 November 2007 04:19 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
1. Format your post. We know you know how to do it.
2. Where is lakhs? Wink Seriously though Not everyone knows what a Lakh is
3. Provide a test case i.e. provide create table and insert scripts NOT a file

[Updated on: Thu, 15 November 2007 04:20]

Report message to a moderator

Re: how t0 generate missing dates [message #280947 is a reply to message #280941] Thu, 15 November 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: how t0 generate missing dates [message #280953 is a reply to message #280941] Thu, 15 November 2007 04:27 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
test data is

TIME OH QSI
1 10/22/2006 3:51:00 AM 0 182
2 10/22/2006 5:21:00 AM 5400 182
3 10/22/2006 5:51:00 AM 1800 182
4 10/22/2006 6:21:00 AM 1800 182
5 10/22/2006 6:51:00 AM 1800 182
6 10/22/2006 7:21:00 AM 1800 182
7 10/22/2006 7:51:00 AM 1800 182
8 10/22/2006 8:21:00 AM 1800 182
9 10/22/2006 8:51:00 AM 1800 182
10 10/22/2006 9:21:00 AM 1800 182
11 10/22/2006 9:51:00 AM 1800 182
12 1/2/2007 10:25:00 AM 41
13 1/2/2007 10:34:00 AM 540 41
14 1/2/2007 10:46:00 AM 720 41
15 1/2/2007 10:58:00 AM 720 41
16 1/2/2007 11:10:00 AM 720 41
17 1/2/2007 11:22:00 AM 720 41
18 1/2/2007 11:36:00 AM 840 41
19 1/2/2007 11:46:00 AM 600 41
20 1/2/2007 11:57:00 AM 660 41
21 1/2/2007 12:25:00 PM 1680 41
22 1/2/2007 12:35:00 PM 600 41
23 1/2/2007 2:23:00 PM 6480 41
24 1/2/2007 2:35:00 PM 720 41
25 1/2/2007 2:47:00 PM 720 41
26 1/2/2007 2:59:00 PM 720 41


and the formatted query is

SELECT DistinctMint + (LEVEL - 1) / 24 RoundTime1,
qsi,
DAY
FROM (SELECT qsi,
To_char(TIME,'DD/MM/YYYY') DAY,
MIN(Trunc(TIME,'HH24')) Mint,
MAX(Trunc(TIME,'HH24')) Maxt
FROM t1
GROUP BY qsi,
To_char(TIME,'DD/MM/YYYY'))
CONNECT BY LEVEL < ((Maxt - Mint) * 24 + 2)
ORDER BY qsi,
RoundTime1

and the extra rows generated by this query is highlighted in the previous post. those are main rows needed
Re: how t0 generate missing dates [message #280963 is a reply to message #280953] Thu, 15 November 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

the formatted query is

No it is not formatted, read the link we posted and use the code tags.

Test data must be provided as insert statements along with create table one.

Regards
Michel
Re: how t0 generate missing dates [message #280964 is a reply to message #280941] Thu, 15 November 2007 04:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Is this what you are trying to achieve ?

select distinct TRUNC (TIME_D,'HH') from t1 order by 1


Thumbs Up
Rajuvan.
Re: how t0 generate missing dates [message #280965 is a reply to message #280953] Thu, 15 November 2007 04:55 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
No create table statement. No insert statements. No assistance from me.
Re: how t0 generate missing dates [message #280966 is a reply to message #280941] Thu, 15 November 2007 04:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Rather ..


select distinct TRUNC (TIME_D,'HH'),QSI , TRUNC (TIME_D)from orafaq_t order by 1


@ pablolee :

Lakh is Indian version of Mathemantics

1 Lakh : 100000 (Hundred Thousand )
1 Crore (karod) : 10000000 (10 million)

Smile

Thumbs Up
Rajuvan.

[Updated on: Thu, 15 November 2007 05:01]

Report message to a moderator

Re: how t0 generate missing dates [message #280972 is a reply to message #280941] Thu, 15 November 2007 05:08 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
create table T1
(
TIME DATE,
OH NUMBER,
QSI NUMBER
)
/


insert into t1 values ('22-OCT-06',0,182);
insert into t1 values ('22-OCT-06',5400,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('22-OCT-06',1800,182);
insert into t1 values ('02-JAN-07',,41);
insert into t1 values ('02-JAN-07',540,41);
insert into t1 values ('02-JAN-07',720,41);
insert into t1 values ('02-JAN-07',720,41);
insert into t1 values ('02-JAN-07',720,41);
insert into t1 values ('02-JAN-07',720,41);
insert into t1 values ('02-JAN-07',840,41);
insert into t1 values ('02-JAN-07',600,41);
insert into t1 values ('02-JAN-07',660,41);
insert into t1 values ('02-JAN-07',1680,41);
insert into t1 values ('02-JAN-07',600,41);
insert into t1 values ('02-JAN-07',6480,41);
insert into t1 values ('02-JAN-07',720,41);
insert into t1 values ('02-JAN-07',720,41);
insert into t1 values ('02-JAN-07',720,41);


and final outcome include the missing timings as

ROUNDTIME1 QSI DAY
1 1/2/2007 10:00:00 AM 41 02/01/2007
2 1/2/2007 11:00:00 AM 41 02/01/2007
3 1/2/2007 12:00:00 PM 41 02/01/2007
4 1/2/2007 1:00:00 PM 41 02/01/2007
5 1/2/2007 2:00:00 PM 41 02/01/2007
6 10/22/2006 3:00:00 AM 182 22/10/2006
7 10/22/2006 4:00:00 AM 182 22/10/2006
8 10/22/2006 5:00:00 AM 182 22/10/2006
9 10/22/2006 6:00:00 AM 182 22/10/2006
10 10/22/2006 7:00:00 AM 182 22/10/2006
11 10/22/2006 8:00:00 AM 182 22/10/2006
12 10/22/2006 9:00:00 AM 182 22/10/2006

query written by me
for this is

SELECT DistinctMint + (LEVEL - 1) / 24 RoundTime1,
qsi,
DAY
FROM (SELECT qsi,
To_char(TIME,'DD/MM/YYYY') DAY,
MIN(Trunc(TIME,'HH24')) Mint,
MAX(Trunc(TIME,'HH24')) Maxt
FROM t1
GROUP BY qsi,
To_char(TIME,'DD/MM/YYYY'))
CONNECT BY LEVEL < ((Maxt - Mint) * 24 + 2)
ORDER BY qsi,
RoundTime1

this give exact result but i need some other alternative
with good performance
now i thing it's okay
Re: how t0 generate missing dates [message #280973 is a reply to message #280966] Thu, 15 November 2007 05:10 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:

Lakh is Indian version of Mathemantics

1 Lakh : 100000 (Hundred Thousand )
1 Crore (karod) : 10000000 (10 million)

Cheers Rajavu I was already aware of that, my point was that not everyone else is and that the OP should try to use universally accepted terms for values rather than regionally specific terms.
Re: how t0 generate missing dates [message #281190 is a reply to message #280941] Fri, 16 November 2007 01:16 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
I have this sort of data for ex..



Val time

X 10:01

X 12:15

X 1:25

Y 3:06

Y 5:10

Y 6:11

Y 9:12



and i need missing hour's included in it like
in X it's 11 missing and in y it's 4 ,7,8 are missing .

and finally i need this sort of output in which
i got the missing date too with rest of the data



Val time

X 10:01

X 11:00

X 12:15

X 1:25

Y 3:06

Y 4:00

Y 5:10

Y 6:11

Y 7:00

Y 8:00

Y 9:12

Re: how t0 generate missing dates [message #281200 is a reply to message #281190] Fri, 16 November 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad for you you didn't follow the rules and format your post nor post a test case, I could give you a solution.

Regards
Michel
Re: how t0 generate missing dates [message #281252 is a reply to message #280941] Fri, 16 November 2007 04:14 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
actually this is the data i used as an example or test data
to elaborate the problem

Val   time
X     10:01 
X     12:15
X     1:25
Y     3:06
Y     5:10
Y     6:11
Y     9:12


in this u can see that data for 10th hour is missin
in case of val='X'
and data for 4th,7th and 8th hour is missing in
case of val='Y'
but i need all the hours exists in between the
min and max time of distinct val

Val     time
X       10:01 
X       11:00
X       12:15
X        1:25
Y        3:06
Y        4:00
Y        5:10
Y        6:11
Y        7:00
Y        8:00
Y        9:12
Re: how t0 generate missing dates [message #281262 is a reply to message #281252] Fri, 16 November 2007 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a first step:
SQL> create table t (val varchar2(10), tim date);
SQL> insert into t values ('X', to_date('01/01/01 10:01', 'DD/MM/RR HH24:MI'));
SQL> insert into t values ('X', to_date('01/01/01 12:15', 'DD/MM/RR HH24:MI'));
SQL> insert into t values ('X', to_date('01/01/01 13:25', 'DD/MM/RR HH24:MI'));
SQL> insert into t values ('Y', to_date('01/01/01 15:06', 'DD/MM/RR HH24:MI'));
SQL> insert into t values ('Y', to_date('01/01/01 17:10', 'DD/MM/RR HH24:MI'));
SQL> insert into t values ('Y', to_date('01/01/01 18:11', 'DD/MM/RR HH24:MI'));
SQL> insert into t values ('Y', to_date('01/01/01 21:12', 'DD/MM/RR HH24:MI'));
SQL> commit;
SQL> select val, to_char(tim,'HH24:MI') tim from t order by tim;
VAL        TIM
---------- -----
X          10:01
X          12:15
X          13:25
Y          15:06
Y          17:10
Y          18:11
Y          21:12

7 rows selected.

SQL> with 
  2    cal as (
  3      select to_date('01/01/01','DD/MM/RR')+(level-1)/24 tim
  4      from dual
  5      connect by level <= 25
  6    )
  7  select max(nvl(t.val,' ')) over (order by cal.tim) val,
  8         to_char(nvl(t.tim,cal.tim),'HH24:MI') tim 
  9  from cal, t
 10  where trunc(t.tim (+),'HH24') = trunc(cal.tim,'HH24')
 11  order by cal.tim, t.tim
 12  /
VAL        TIM
---------- -----
           00:00
           01:00
           02:00
           03:00
           04:00
           05:00
           06:00
           07:00
           08:00
           09:00
X          10:01
X          11:00
X          12:15
X          13:25
X          14:00
Y          15:06
Y          16:00
Y          17:10
Y          18:11
Y          19:00
Y          20:00
Y          21:12
Y          22:00
Y          23:00
Y          00:00

25 rows selected.

Regards
Michel
Re: how t0 generate missing dates [message #281270 is a reply to message #280941] Fri, 16 November 2007 05:03 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
i think u have not seen the output given below and my words
i don't need the whole set of 24 hours
what i need is given below

in this u can see that data for 11th hour is generated
in case of val='X'
and data for 4th,7th and 8th hour is generated in
case of val='Y'
what i need is all the hours exists in between the
min and max time of distinct val
means as min value of x is 10.01 and max is 1.25
then i need all the hours exist in between
like 10,11,12,1


Val     time
X       10:01 
X       11:00
X       12:15
X        1:25
Y        3:06
Y        4:00
Y        5:10
Y        6:11
Y        7:00
Y        8:00
Y        9:12
Re: how t0 generate missing dates [message #281285 is a reply to message #280941] Fri, 16 November 2007 06:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@vikesh : Why can't you cusomise Michel queries to avoid NULL "VAL" field values ? What prevents from doing so ? Anyway you changed you requiremnt from you starting post

Thumbs Up
Rajuvan

[Updated on: Fri, 16 November 2007 06:00]

Report message to a moderator

Re: how t0 generate missing dates [message #281297 is a reply to message #281270] Fri, 16 November 2007 06:25 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you know what "Here's a first step" mean?
Now work a little bit and try to complete. I gave you the hard part.
I can do your whole job but you have to pay me.

Regards
Michel
Previous Topic: using reslts from a function
Next Topic: database writer process
Goto Forum:
  


Current Time: Sun Dec 11 04:34:17 CST 2016

Total time taken to generate the page: 0.13024 seconds