CONFUSED !! PLEASE HELP !!!! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CONFUSED !! PLEASE HELP !!!!

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-27 : 02:58:30

Hello Everybody :
I have 2 tables
a] RaywoodData
b] TrafficFines
Using 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 1

TRAFFICFINES has the following fields :
Ticket_No Emp_ID FineDate FineTime Plate_No Plate_Colour
03691651 BLANK 2002-11-22 05:00 17472 RED
03691652 BLANK 2002-11-23 15:00 12444 NULL
03691653 BLANK 2002-11-24 11:10 88559 NULL
03691654 BLANK 2002-11-25 03:30 7356 WHITE
03691655 BLANK 2002-11-24 10:00 25632 NULL
03691656 BLANK 2002-11-22 22:20 75669 YELLOW

It 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

Posted - 2002-11-27 : 03:57:42

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21723

Should help with 1 ii) and 2 i)

-------
Moo.

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-27 : 04:26:49

Hi Mist :
I have gone through the link U had sent. But that is not the solution to my problem. I am not having any difficulty with date datatype, its only the logic where i am stuck on.
Kindly guide me if possible.
Thanks,
Marjo.

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-27 : 04:44:55

Inner join the two tables on plate number. (and colour?)

WHERE
((
check for a fine date that is between start date and stop date - the link should give the logic for this.
)
AND
(
drivershift = 3
) or
(
Finetime between starttime and stoptime
)
)

You may have to convert finetime starttime and stoptime into datetimes, I'm not sure.

-------
Moo.

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-27 : 04:59:14

CREATE PROCEDURE GetDriverID
AS
BEGIN
Declare @DriverID varchar(15)
Declare TAXICursor Cursor FOR
Select Driverid From TrafficFines,RaywoodData
Where Convert(Varchar(20),Trafficfines.Plate_No)= RaywoodData.Plate_No
And Convert(Datetime,Finetime) Between Convert(Datetime,Starttime) And Convert(Datetime,Stoptime)
And Finedate Between Startdate And Stopdate
And (RaywoodData.Plate_Colour = TRAFFICFINES.PLATE_COLOUR Or RaywoodData.Plate_Colour Is Null)

Open TAXICursor
Fetch Next from TAXICursor INTO @DriverID

WHILE @@FETCH_STATUS=0
BEGIN
Update TRAFFICFINES
SET Emp_ID = @DriverID
WHERE CURRENT OF TAXICursor
Fetch Next From TAXICursor INTO @DriverID
END
Close TAXICursor
Deallocate TAXICursor
END

This gives me the DriverID but as I had mentioned earlier that for 24 hrs driver the criteria is plate_no,colour and date but not time and this query uses the time criteria also. Therefore according to the business rules the above query fails for 24hrs driver. Becoz the car / Taxi is available with the 3rd shift drivers for all 24hrs, thats the reason the business rule does not depend on the stoptime for this shift. Whereas for other shift drivers, they return the car after there stoptime. I hope u r getting my problem..
Do I have to create a view and one more cursor ?
Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 10:26:04

Trusted4u -- did you see mr_mist's response? I think he is right on. The key I think is to convert all of your dates and times to DateTimes and just say filter to make sure the plates match and the finedate/finetime combo is within the startdate/starttime and the enddate/endtime. You don't have to worry about different types of shifts or anything, because you have the exact beginning and ending times of all the shifts.

I don't work with times much, and don't have query analyzer running right now, but:

SELECT Fines.*, Data.DriverID
FROM
RAYWOODDATA Data
INNER JOIN
TRAFFICFINES Fines
ON
Fines.Plate_No = Data.Plate_No AND
Fines.Plate_Colour = Data.Plate_Colour AND
CONVERT(DateTime, FineDate + FineTime) BETWEEN CONVERT(DateTIme, StartDate + StartTime) AND CONVERT(DateTime, StopDate + StopTime)

should work, or something like it (not sure about my CONVERT's).

I don't think you need to worry about shift's. I think your problem is lining up the dates, which you can't do as you mentioned because of the 24 hours shifts. Just make sure that the datetime of the fine is between the datetime of the driver's shift.

So, I think mr_mist's solution is perfect, but you don't need to worry about the shift type.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources