Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trusted4u
Posting Yak Master
109 Posts |
 Posted - 2002-11-27 : 02:56:17
|
| Hello Everybody :I have 2 tablesa] RaywoodData b] TrafficFinesUsing the Plate_no, FineDate and FineTime, I want to find out which driver has made the fine and accordingly dump the driverID from the RaywoodData in the Emp_Id field of TrafficFines Table. The complete driver information is given in the raywooddata table. Just have a look at the tables given below.RAYWOODDATA - TABLE has the following fields :DriverID Plate_No Plate_Colour StartDate StartTime StopDate StopTime DriverShift 2001433 17472 RED 2002-11-22 04:57 2002-11-22 20:00 3 981335 66258 NULL 2002-11-21 22:05 2002-11-22 22:47 3 981868 5891 NULL 2002-11-21 22:59 2002-11-22 20:33 3 950767 7896 NULL 2002-11-12 04:14 2002-11-12 15:48 2 950727 78196 NULL 2002-11-12 04:14 2002-11-12 15:48 2 917367 7896 White 2002-11-12 15:14 2002-11-12 22:48 1 930342 7356 white 2002-11-12 14:14 2002-11-12 21:14 1TRAFFICFINES has the following fields :Ticket_No Emp_ID FineDate FineTime Plate_No Plate_Colour03691651 BLANK 2002-11-22 05:00 17472 RED03691652 BLANK 2002-11-23 15:00 12444 NULL03691653 BLANK 2002-11-24 11:10 88559 NULL03691654 BLANK 2002-11-25 03:30 7356 WHITE03691655 BLANK 2002-11-24 10:00 25632 NULL03691656 BLANK 2002-11-22 22:20 75669 YELLOWIt sounds very simple but read further ....There r 3 shifts of drivers :a] Shift 1 [Evening]b] Shift 2 [Morning]c] Shift 3 [24 hrs]While finding the DriverID for that particular fine, I have to check the following criteria :1] If the DriverShift is not 3 i.e. not 24 hrs. then i] check for records in RaywoodData where finetime of Trafficfines falls between starttime and stoptime. ii] check for finedate between startdate and stopdate iii] chk for matching plate_no in both tables.2] If the Drivershift is 3 i.e. 24hrs. then i] Just chk for the matching plateno and finedate between startdate and stopdate.I am lost and confused. Can anybody pls tell me what will be the best solution for this.Thanks.Marjo. |
|
|
mr_mist
Grunnio
1870 Posts |
|
|
|
|
|
CONFUSED !! PLEASE HELP !!!!