Help in script

[david_ze]

Dear All, I'll appreciate your help in how to make a clculations within the sript.

I would like to calculate our customer-base with the following logic:

The data looks like this

ID	Connect Date	Dis-Connect Date
1	01/01/2004	01/01/2004
2	02/01/2004	05/01/2004
3	02/01/2004	03/01/2004
4	02/01/2004
5	02/01/2004	04/01/2004
6	05/01/2004
7	05/01/2004	07/01/2004
8	05/01/2004	10/01/2004
9	05/01/2004
10	05/01/2004
11	09/01/2004	12/01/2004
12	09/01/2004

At each date I would like to have a number which is a calculation of the existing connected customers.

Means that, on the date of 2/1/2004 i will have 4 customers (5 connected till 2/1/04 and one dis-connected).

On the Feb/5 I will have 6 customers (10 connected and 4 dis-connected).

I'll appreciate your help on how to handle this in the script.

Thanks in advance

David

[luciancotea]

First step is to transform your data to this:

ID	Date	Action	Counter
1	1/1/2004	Connect	1
1	1/1/2004	Disconnect	-1
2	2/1/2004	Connect	1
3	2/1/2004	Connect	1
4	2/1/2004	Connect	1
5	2/1/2004	Connect	1
3	3/1/2004	Disconnect	-1
5	4/1/2004	Disconnect	-1
6	5/1/2004	Connect	1
7	5/1/2004	Connect	1
8	5/1/2004	Connect	1
9	5/1/2004	Connect	1
10	5/1/2004	Connect	1
2	5/1/2004	Disconnect	-1
7	7/1/2004	Disconnect	-1
11	9/1/2004	Connect	1
12	9/1/2004	Connect	1
8	10/1/2004	Disconnect	-1
11	12/1/2004	Disconnect	-1

Then, it's a simply "Counter" sum operation.

[Anonymous]

I would create separate Flag table with this:

LOAD

ID,

date([Connect Date]+Iterno()-1) as Date

if(iterno()=1, 1,0) as Flag_Start,

if[Connect Date]+iterno()-1= [Dis-Connect Date],0,1) as Flag_Connected

if[Connect Date]+iterno()-1= [Dis-Connect Date]1,0) as Flag_Disconnected

Resident Data

while [Connect Date]+iterno()-1) <= IF(isnull([Dis-Connect Date]),today(),[Dis-Connect Date]);

With this you can easily get number of connected ID with Sum(Distinct {<Flag_Connected={1}>} ID), but you can easily answer question like how many customers connected/disconnected this day/this month/this year by summing start and end flags.