Jump to content

alaingomez

Members
  • Posts

    2
  • Joined

  • Last visited

alaingomez's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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.
  2. 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.
×
×
  • Create New...