Jump to content

Calculated Field With Conditions


alaingomez

Recommended Posts

I need your expertise on this problem I have. I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.Example:Date of Offence - Number of Points

  • [*]Aug. 6, 2013 - 6[*]Sept. 14, 2013 - 4[*]Feb. 4, 2014 - 4[*]Apr. 25, 2014 - 4[*]Apr. 25, 2014 - 2[*]May 8, 2014 - 2

Explanation:On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).Below are DB Tables:tbl_Driver

  • [*]DriverID[*]Driver License[*]FirstName[*]LastName

tbl_Event

  • [*]EventID[*]EventTime[*]DriverID[*]CarID

tbl_EventViolation

  • [*]EventViolatinID[*]EventID[*]ViolationID[*]Status (1 = no, 2 = yes radio buttons)

tbl_Violation

  • [*]ViolationID[*]ViolationName[*]Points

tbl_ViolationClass

  • [*]ViolationClassID[*]ClassName

I made an SQL Code but to sum the accumulated points only but not the whole condition. See below:

SELECT  a.DriverId,         a.DriverLicense,         a.FirstName + ' ' + a.LastName as DriverName,                a.FirstName,                 a.LastName,                 a.DriverMobileNo1,                         a.Notes,                 SUM(a.Points) as TotalPoints                    FROM        (                      SELECT dbo.Event.EventTime, dbo.Drivers.DriverID, dbo.Drivers.DriverLicense, dbo.Drivers.FirstName,                    dbo.Drivers.LastName, dbo.Drivers.DriverMobileNo1, dbo.Violation.ViolationName,                     dbo.Violation.Points, dbo.Drivers.Notes                       FROM   dbo.Drivers INNER JOIN dbo.Event ON dbo.Drivers.DriverId = dbo.Event.DriverId INNER JOIN                         dbo.EventViolation ON dbo.Event.EventId = dbo.EventViolation.EventId INNER JOIN                        dbo.Violation ON dbo.EventViolation.ViolationId = dbo.Violation.ViolationId INNER JOIN                        dbo.ViolationClass ON dbo.Violation.ViolationClassId = dbo.ViolationClass.ViolationClassID                WHERE  dbo.EventViolation.Status = 2 AND dbo.ViolationClass.ClassName Like 'C'                         AND dbo.Violation.ViolationName Not Like 'LOAD NOT CORRECTLY RESTRAINED'                        AND dbo.Violation.ViolationName Not Like 'DRIVING WITHOUT UAE LICENSE'                  ) as a GROUP BY a.DriverId, a.DriverLicense, a.FirstName, a.LastName, a.DriverMobileNo1, a.NotesORDER BY Points desc

Please anyone try to modify the code to work on the conditions set above to have a column with Total Points (with conditions above)Thank you for your time. Looking forward for the solution.

Edited by alaingomez
Link to comment
Share on other sites

Let's start with your question:

 

On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4)

Shouldn't that be 6+6+4+4=20 according to your posted data?

Link to comment
Share on other sites

I'm sorry, the value for Sept 2013 is 4 points. So the sum is 6+4+4 which is 14 points and suspension is served then back to zero after it is served, the 2 points from April 25, 2014 was carried over the next because it is less than compared from the other which is si 4 points.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...