numbers intervals - script solution

[#1]

Hello and thanks in advance

Please help me in numbers intervals

I have 2 tables:

• intervals

CityID	FromSum	ToSum	ClientType	CriteriaID	Level
100	0	10	A	20151	1
100	11	20	A	20151	2
100	21	30	A	20151	3
101	0	100	A	20151	1
101	101	200	A	20151	2
101	201	300	A	20151	3
100	0	15	A	20152	1
100	16	20	A	20152	2
100	21	30	A	20152	3
101	0	100	A	20152	1
101	101	200	A	20152	2
101	201	500	A	20152	3
101	0	20	B	20152	1
101	21	30	B	20152	2
101	31	40	B	20152	3

• clients data

ClientID	CityID	ClientType	Sum
22222	100	A	10
33333	100	A	15
44444	100	A	30
55555	101	A	150
66666	101	A	100
77777	101	A	20
88888	101	B	600

I need to know client's level for each type and criteriaID (one client has only one type) . Also I would like to know clients without any level for each criteria

Finaly, I need to get this data in my model:

ClientID	CriteriaID	Level
22222	20151	1
33333	20151	2
44444	20151	3
55555	20151	2
66666	20151	1
77777	20151	1
88888	20151	0
22222	20152	1
33333	20152	1
44444	20152	3
55555	20152	2
66666	20152	1
77777	20152	1
88888	20152	0

[miguelbraga]

Hi Eva,

Something like this?

Regards,

MB

[#3]

No, because I have also a sum intervals. I have 2 factors: city, client type and sum interval.

I can have 3 levels for each client type.

Now I see that  my first table is not the good example (sorry, my fault)- I've changed it