Query help-Teradata

Database
Enthusiast

Query help-Teradata

Hi,
can one help me in sorting out the query.

I have the following table with me

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,run_date,invo_num;

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

the output i need to get is

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

The logic am using here is
1. If Invoice number was not on 'yesterday's' downloaded data:
a. Days_in_area = 1
2. Compare current area from 'today's' downloaded data to current area from 'yesterday's downloaded data:
a. If equal, add 1 to number of days in area
b. If different, days_in_area = 1.

am struggling with the same from 2 days.
help me.
thanks in advance.
1 REPLY
Enthusiast

Re: Query help-Teradata

hi,
The above i have to do without using procs.
Thanks