Hi all,

I have an SQL problem... (as usual)...

Two tables TMEMBER and TDUTY

        Member  
ID      CRCODE  DAY
52      11323   05/12/1999
10      11323   06/12/1999
50      11323   07/12/1999
9       11323   08/12/1999
131     11323   08/12/1999
135     11323   08/12/1999
903     11323   08/12/1999

                Duty            
CROUTE  DUTYSTART               DUTYEND         DEP     ARR

1132305 05/12/1999 14:15        05/12/1999 18:50        HRG     MUC
1132306 06/12/1999 09:50        06/12/1999 11:40        CDG     DUB
1132307 07/12/1999 14:05        07/12/1999 15:55        CDG     DUB
1132308 08/12/1999 01:00        08/12/1999 08:00        MIA     DUB

i.e. person with ID 52 worked on Duty 11323 on the 5th of December
which started at 14:15 and finished at 18:50 which left HURGHADA
(Egypt) and finished in MUNICH.

The tables are related in that (Member.CRCODE * 100) +
DayOfMonth(Member.Day) = Duty.CROUTE

i.e. in the case in question, (11323 * 100) + 5 = 1132305.

Can any kind genius out there tell me how to construct a table which
would be a report of

MEMBER.ID DUTY.CROUTE D.START D.END DEP and ARR.

Please note that this is not the way I would have *_chosen_* to
organise my data, but I am constrained by a legacy system that I have
to work with...

However, any ideas, suggestions are, as usual, more than welcome.

TIA.

Paul...

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

Paul Linehan (Paul Linehan) wrote:

select TMEMBER.ID, TDUTY.*
from TMEMBER, TDUTY
where
TMEMBER.CRCODE = 11323
and TDUTY.CROUTE > 1132300
and TDUTY.CROUTE < 11323332
and ((DATEPART(day, TMEMBER.DAY)) + (TMEMBER.CRCODE * 100)) =
TDUTY.CROUTE
order by TMEMBER.ID, TMEMBER.DAY

The DATEPART function proved useful. The only thing that I don't like
is that the word "DAY" seems to be a reserved word in MS SQL Server,
so I'll have to choose another word in my database...

Mind you, the database that I'm copying from uses field names like
"primary", "date" and  "idx", which SQL Server won't even let you make
into table names, so I have just prefixed them with "x", guess I'll
just have to that here.

Paul...

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!