R
Richard
OS: MS XP
Excel 2000
Database Oracle 8 I
Having problems getting it right.
I was able to copy the results near the end of this question B/4 I sent it
and if you can copy it after I send it then you can see it in Excel. Copy
the portion between the "************".
The results are correct until a the same "client Id" appears on the same
"EvStrName" going and returning. It's not linking correctly. You can see
that the BookingId's are different and they should be the same. The trip for
Mickey Mouse should be 1st booking Id = 3147557 and the 2nd booking Id =
3147556.
Any help would be appreciated.
Thanks in advanced.
SELECT SCHEDULES.LDATE, EVENTS.SCHID, EVENTS.CLIENTID,
CLIENTS.LASTNAME||','||CLIENTS.FIRSTNAME||' '||CLIENTS.MIDDLENAME,
EVENTSTRINGS.EVSTRNAME, EVENTS.SCHTIME/60, EVENTS.ESTTIME/60, ED.ESTTIME/60,
EVENTS.CITY, ED.CITY, EVENTS.SPACEONBOARD, EVENTS.VIOLATIONS,
EVENTS.ACTIVITY, ED.ACTIVITY, EVENTS.BOOKINGID, ED.BOOKINGID
FROM Door.CLIENTS CLIENTS, Door.EVENTS ED, Door.EVENTS EVENTS,
Door.EVENTSTRINGS EVENTSTRINGS, Door.SCHEDULES SCHEDULES
WHERE (EVENTS.SCHTIME/60>0) AND (CLIENTS.CLIENTID=EVENTS.CLIENTID And
CLIENTS.CLIENTID=ED.CLIENTID) AND (EVENTS.EVSTRID=EVENTSTRINGS.EVSTRID) AND
(ED.EVSTRID=EVENTSTRINGS.EVSTRID) AND (EVENTS.SCHID=EVENTSTRINGS.SCHID And
EVENTS.SCHID=SCHEDULES.SCHID And EVENTS.SCHID=ED.SCHID) AND
(ED.SCHID=EVENTSTRINGS.SCHID) AND
(SCHEDULES.LDATE>=TO_CHAR(SYSDATE-5,'YYYYMMDD') And
SCHEDULES.LDATE<=TO_CHAR(SYSDATE+6,'YYYYMMDD')) AND (EVENTSTRINGS.EVSTRNAME
Like '2%') AND (ED.ACTIVITY=1)
ORDER BY SCHEDULES.LDATE, EVENTSTRINGS.EVSTRNAME, EVENTS.EVORDER
Results:
********************************************
LDATE SCHID CLIENTID CLIENTS.LASTNAME||','||CLIENTS EVSTRNAME EVENTS.SCHTIME/60 EVENTS.ESTTIME/60 ED.ESTTIME/60 CITY CITY SPACEONBOARD VIOLATIONS ACTIVITY ACTIVITY BOOKINGID BOOKINGID
20070108 217 555 Buggs
Bunny 123 540 545 563 Middletwn Middletwn AM17 0 1 7150995 7150995
20060108 217 444 Woody the Wood Pecker 345 600 607 626 Any Town Any
Town AM17 0 1 9151065 9151065
20060108 217 333 Scrooge
McDuck 345 630 635 669.0166667 Middletwn Middletwn AM17 0 1 4147928 4147928
20060108 217 12345 Mickey
Mouse 867 630 660 762 Middletwn Middletwn AM17 0 1 3147556 3147557
20060108 217 12345 Mickey
Mouse 867 630 660 667 Middletwn Middletwn AM17 0 1 3147556 3147556
20060108 217 345 Donald Duck 345 660 680 724 Middletwn Any
Town AM17 0 1 6110573 6110573
20060108 217 4322 Porkey Pig 867 660 690 727.0166667 Middletwn Any
Town AM17 0 1 2139223 2139223
*******************************************************
Please help.
Thanks.
Excel 2000
Database Oracle 8 I
Having problems getting it right.
I was able to copy the results near the end of this question B/4 I sent it
and if you can copy it after I send it then you can see it in Excel. Copy
the portion between the "************".
The results are correct until a the same "client Id" appears on the same
"EvStrName" going and returning. It's not linking correctly. You can see
that the BookingId's are different and they should be the same. The trip for
Mickey Mouse should be 1st booking Id = 3147557 and the 2nd booking Id =
3147556.
Any help would be appreciated.
Thanks in advanced.
SELECT SCHEDULES.LDATE, EVENTS.SCHID, EVENTS.CLIENTID,
CLIENTS.LASTNAME||','||CLIENTS.FIRSTNAME||' '||CLIENTS.MIDDLENAME,
EVENTSTRINGS.EVSTRNAME, EVENTS.SCHTIME/60, EVENTS.ESTTIME/60, ED.ESTTIME/60,
EVENTS.CITY, ED.CITY, EVENTS.SPACEONBOARD, EVENTS.VIOLATIONS,
EVENTS.ACTIVITY, ED.ACTIVITY, EVENTS.BOOKINGID, ED.BOOKINGID
FROM Door.CLIENTS CLIENTS, Door.EVENTS ED, Door.EVENTS EVENTS,
Door.EVENTSTRINGS EVENTSTRINGS, Door.SCHEDULES SCHEDULES
WHERE (EVENTS.SCHTIME/60>0) AND (CLIENTS.CLIENTID=EVENTS.CLIENTID And
CLIENTS.CLIENTID=ED.CLIENTID) AND (EVENTS.EVSTRID=EVENTSTRINGS.EVSTRID) AND
(ED.EVSTRID=EVENTSTRINGS.EVSTRID) AND (EVENTS.SCHID=EVENTSTRINGS.SCHID And
EVENTS.SCHID=SCHEDULES.SCHID And EVENTS.SCHID=ED.SCHID) AND
(ED.SCHID=EVENTSTRINGS.SCHID) AND
(SCHEDULES.LDATE>=TO_CHAR(SYSDATE-5,'YYYYMMDD') And
SCHEDULES.LDATE<=TO_CHAR(SYSDATE+6,'YYYYMMDD')) AND (EVENTSTRINGS.EVSTRNAME
Like '2%') AND (ED.ACTIVITY=1)
ORDER BY SCHEDULES.LDATE, EVENTSTRINGS.EVSTRNAME, EVENTS.EVORDER
Results:
********************************************
LDATE SCHID CLIENTID CLIENTS.LASTNAME||','||CLIENTS EVSTRNAME EVENTS.SCHTIME/60 EVENTS.ESTTIME/60 ED.ESTTIME/60 CITY CITY SPACEONBOARD VIOLATIONS ACTIVITY ACTIVITY BOOKINGID BOOKINGID
20070108 217 555 Buggs
Bunny 123 540 545 563 Middletwn Middletwn AM17 0 1 7150995 7150995
20060108 217 444 Woody the Wood Pecker 345 600 607 626 Any Town Any
Town AM17 0 1 9151065 9151065
20060108 217 333 Scrooge
McDuck 345 630 635 669.0166667 Middletwn Middletwn AM17 0 1 4147928 4147928
20060108 217 12345 Mickey
Mouse 867 630 660 762 Middletwn Middletwn AM17 0 1 3147556 3147557
20060108 217 12345 Mickey
Mouse 867 630 660 667 Middletwn Middletwn AM17 0 1 3147556 3147556
20060108 217 345 Donald Duck 345 660 680 724 Middletwn Any
Town AM17 0 1 6110573 6110573
20060108 217 4322 Porkey Pig 867 660 690 727.0166667 Middletwn Any
Town AM17 0 1 2139223 2139223
*******************************************************
Please help.
Thanks.