Board index » delphi » A tricky little problem - is SQL the answer or do I need some sort of Pascal processing?

A tricky little problem - is SQL the answer or do I need some sort of Pascal processing?

Hi all,

I have the following sample data, for pilots who have done x amount of
flying in the month of October last.

What I need to be able to do is to "condense" this table a little
further.

ID      DEP     ARR     STD                     STA

1       DUB     LGW     13/10/1999 08:00        13/10/1999 09:00
1       LGW     DUB     14/10/1999 17:00        14/10/1999 18:00

1       DUB     STN     22/10/1999 11:30        22/10/1999 12:30
1       STN     LGW     22/10/1999 14:00        22/10/1999 15:00

1       LGW     RHO     23/10/1999 15:00        23/10/1999 19:00
1       RHO     BHX     23/10/1999 19:55        24/10/1999 00:10

1       BHX     DUB     24/10/1999 15:00        24/10/1999 15:55

1       DUB     RHO     29/10/1999 20:00        30/10/1999 00:10
1       RHO     BHX     30/10/1999 16:30        30/10/1999 21:00
1       BHX     DUB     30/10/1999 21:55        30/10/1999 22:55

If you look at the first two records, he obviously flew out from
Dublin at 8:00 on the 13th, overnighted in Gatwick and therefore those
two records are OK and should remain as they are.

The second group of two - he flew from Dublin to Stanstead (UK) and
then flew to Gatwick (prob. a taxi in this case...) and then
overnighted in Gatwick.

The next day (third group of two) he flew out of Gatwick to Rhodes and
then the same night he flew to Birmingham where he overnighted.

The next day (group of one) he flew from Birmingham to Dublin.

Basically, I want to condense the DUB->STN->LGW records to just one,
DUB->LGW, since I am trying to allocate these guys hotel money and
that depends on which airport they overnighted in.

Obviously I also want to "condense" LGW->RHO->BHX to just one, i.e.
LGW->BHX.

The rules are that if a pilot spends more than 10 hours without
flying, he is deemed to have spent the night at the last airport he
landed in.

In the case of the first group of two flights, he will be paid the
Gatwick rate per hour from the time he takes off in Dublin till the
time he returns to Dublin.

For the second set of flights, i.e. DUB->STN->LGW->RHO->BHX->DUB,
he will be paid Gatwick rates from the time he takes off in Dublin
till the time he takes off in Gatwick for the (final) destination of
Birmingham (i.e. final in the sense of where he overnights) from when
he will be paid Birmingham rates until he lands in Dublin.

Just to make sure you've understood all that, for the last group of
three flights he will get paid Rhodes rates from time of take off in
Dublin to time of landing in Dublin, since he only overnights in
Rhodes and only stops in Birmingham for a short while.

I have a full table of such data, but I cannot know in advance how
many records in advance I will need to overnight, since he could (in
theory anyway, fly up to approx. six shuttle flights somewhere and
then overnight.

He is deemed to have overnighted if the time of take off of flight X
minus the time of landing of flight X-1 is greater than 10 hours.

What I would like as a first step is for this

1       DUB     STN     22/10/1999 11:30        22/10/1999 12:30
1       STN     LGW     22/10/1999 14:00        22/10/1999 15:00

to become

1 DUB  LGW  22/10/19999 11:30 22/10/1999 15:00

and

1       LGW     RHO     23/10/1999 15:00        23/10/1999 19:00
1       RHO     BHX     23/10/1999 19:55        24/10/1999 00:10

to become

1 LGW BHX 23/10/1999 15:00 24/10/1999 00:10

Possible in SQL? Other methods?

Any ideas, comments, suggestions or questions welcome...

Paul...

p.s. I hope this makes sense...

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

 

Re:A tricky little problem - is SQL the answer or do I need some sort of Pascal processing?


You should be able to write a stored procedure that consolidates the data
into a summary table.  Barring that, you can always compute the totals in
the client.  I don't think you'll be able to do what you want using straight
SQL, though.

Dan

<Paul Linehan (Paul Linehan)> wrote in message
news:38ecbeba.6464525@forums.inprise.com...

Quote

> Hi all,

> I have the following sample data, for pilots who have done x amount of
> flying in the month of October last.

> What I need to be able to do is to "condense" this table a little
> further.

> ID DEP ARR STD STA

> 1 DUB LGW 13/10/1999 08:00 13/10/1999 09:00
> 1 LGW DUB 14/10/1999 17:00 14/10/1999 18:00

> 1 DUB STN 22/10/1999 11:30 22/10/1999 12:30
> 1 STN LGW 22/10/1999 14:00 22/10/1999 15:00

> 1 LGW RHO 23/10/1999 15:00 23/10/1999 19:00
> 1 RHO BHX 23/10/1999 19:55 24/10/1999 00:10

> 1 BHX DUB 24/10/1999 15:00 24/10/1999 15:55

> 1 DUB RHO 29/10/1999 20:00 30/10/1999 00:10
> 1 RHO BHX 30/10/1999 16:30 30/10/1999 21:00
> 1 BHX DUB 30/10/1999 21:55 30/10/1999 22:55

> If you look at the first two records, he obviously flew out from
> Dublin at 8:00 on the 13th, overnighted in Gatwick and therefore those
> two records are OK and should remain as they are.

> The second group of two - he flew from Dublin to Stanstead (UK) and
> then flew to Gatwick (prob. a taxi in this case...) and then
> overnighted in Gatwick.

> The next day (third group of two) he flew out of Gatwick to Rhodes and
> then the same night he flew to Birmingham where he overnighted.

> The next day (group of one) he flew from Birmingham to Dublin.

> Basically, I want to condense the DUB->STN->LGW records to just one,
> DUB->LGW, since I am trying to allocate these guys hotel money and
> that depends on which airport they overnighted in.

> Obviously I also want to "condense" LGW->RHO->BHX to just one, i.e.
> LGW->BHX.

> The rules are that if a pilot spends more than 10 hours without
> flying, he is deemed to have spent the night at the last airport he
> landed in.

> In the case of the first group of two flights, he will be paid the
> Gatwick rate per hour from the time he takes off in Dublin till the
> time he returns to Dublin.

> For the second set of flights, i.e. DUB->STN->LGW->RHO->BHX->DUB,
> he will be paid Gatwick rates from the time he takes off in Dublin
> till the time he takes off in Gatwick for the (final) destination of
> Birmingham (i.e. final in the sense of where he overnights) from when
> he will be paid Birmingham rates until he lands in Dublin.

> Just to make sure you've understood all that, for the last group of
> three flights he will get paid Rhodes rates from time of take off in
> Dublin to time of landing in Dublin, since he only overnights in
> Rhodes and only stops in Birmingham for a short while.

> I have a full table of such data, but I cannot know in advance how
> many records in advance I will need to overnight, since he could (in
> theory anyway, fly up to approx. six shuttle flights somewhere and
> then overnight.

> He is deemed to have overnighted if the time of take off of flight X
> minus the time of landing of flight X-1 is greater than 10 hours.

> What I would like as a first step is for this

> 1 DUB STN 22/10/1999 11:30 22/10/1999 12:30
> 1 STN LGW 22/10/1999 14:00 22/10/1999 15:00

> to become

> 1 DUB  LGW  22/10/19999 11:30 22/10/1999 15:00

> and

> 1 LGW RHO 23/10/1999 15:00 23/10/1999 19:00
> 1 RHO BHX 23/10/1999 19:55 24/10/1999 00:10

> to become

> 1 LGW BHX 23/10/1999 15:00 24/10/1999 00:10

> Possible in SQL? Other methods?

> Any ideas, comments, suggestions or questions welcome...

> Paul...

> p.s. I hope this makes sense...

> D4 Pro SP3 - NT 4, SP5

> Database - MS SQL Server 7.0

> Seo mo sini!

Other Threads