FORMATING qUERY Teradata

Database
Enthusiast

FORMATING qUERY Teradata

Hi,
I have the following table with me.
can i help me i formating the query

Create Table iw_patstrat_genpact_workdb.days_area (
ind integer,
current_area varchar(30),
run_date date,
invo_num integer
)
Primary Index (ind)
;

select * from iw_patstrat_genpact_workdb.days_area
order by ind,invo_num,run_date

ind current_area run_date invo_num
1 AU 4/19/2007 41574351
2 AU 4/19/2007 41574351
3 AU 4/20/2007 41574351
4 AU 4/19/2007 41574352
5 AU 4/20/2007 41574352
6 B15 4/23/2007 41594992
7 B16 4/24/2007 41594993
8 B16 4/25/2007 41594993
9 SP 4/19/2007 41593391
10 SP 4/19/2007 41593391
11 SP 4/20/2007 41593391
12 SP 4/21/2007 41593391
13 SP 4/22/2007 41593391

The desired ouput is
Output:-

ind current_area run_date invo_num days_in_area
1 AU 4/19/2007 41574351
2 AU 4/19/2007 41574351
3 AU 4/20/2007 41574351 2
4 AU 4/19/2007 41574352
5 AU 4/20/2007 41574352 2
6 B15 4/23/2007 41594992 1
7 B16 4/24/2007 41594993
8 B16 4/25/2007 41594993 2
9 SP 4/19/2007 41593391
10 SP 4/19/2007 41593391
11 SP 4/20/2007 41593391
12 SP 4/21/2007 41593391
13 SP 4/22/2007 41593391 4

The logic behind this is:-
1.If Invoice number was not on 'yesterday's' downloaded data:
a. Days_in_area = 1
2. we havee to Compare current area from 'today's' downloaded data to current area from 'yesterday's downloaded data:
a. If both are equal, add 1 to number of days in area
b. If different, days_in_area = 1.

there are restrictions for me of not using any procs.
Thanks in advance.

Regards,
krishna