Difference between dates in the same table

[#1]

Hello all,

I have the following problem. I need to get the amount of days a car has been in a certain country. What I have to base me on is a table full of events structured like this:

Carnumber	Country	Date	EventType
1	BELGIUM	20/07/2012	ENTRY
2	FRANCE	20/07/2012	ENTRY
43	GERMANY	21/07/2012	EXIT
5	BELGIUM	23/07/2012	ENTRY
21	NETHERLANDS	22/07/2012	EXIT
2	BELGIUM	25/07/2012	ENTRY
2	FRANCE	25/07/2012	EXIT
1	BELGIUM	25/07/2012	EXIT
21	NETHERLANDS	26/07/2012	ENTRY
2	BELGIUM	27/07/2012	EXIT

The problem is that in this table the events do not always come in the right sequence, as I highlighted for car 2. Nevertheless I need to extract from this table how many days the car has been in Belgium and how many days it has been in France. Afterwards it is possible it could return to France and this should be counted seperately. Output should be like this (for car 2)

Carnumber	Country	Begin	End	Total days
2	FRANCE	20/07/2012	25/07/2012	6
2	BELGIUM	25/07/2012	27/07/2012	3
2	FRANCE	27/07/2012	-	-

I have been breaking my head for on this issue for the past few days, maybe someone could help me out on this with a fresh light on things, it shouldn't be so hard but maybe I am thinking the wrong way. Any help is greatly appreciated.

Thanks in advance

Niels

[brenner_martina]

Hi nils,

look at my example!

greetings from Munich

Martina

[#3]

Hi Martina,

Thank you very much for your help!!

The only problem I still face is when a car goes to the same country again. Like First to BELGIUM then to FRANCE en then back to BELGIUM. In your example it will give both enddates for the two times it was in FRANCE giving extra lines which I do not need.

Do you or anyone else have an idea how to solve this?

[giakoum]

maybe this will help

[brenner_martina]

Hi,

look at my example again

Greetings from Munich

Martina

[#6]

Hi Martina,

Thanks again, but there seems to be something wrong. The sum of days is 0 in all cases as you don't have an enddate defined now. I don't know why, script seems right.

[brenner_martina]

Hi nils,

pardon, you are right, I will correct and send you my example!