alaingomez Posted November 12, 2014 Share Posted November 12, 2014 (edited) 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 November 13, 2014 by alaingomez Link to comment Share on other sites More sharing options...
niche Posted November 12, 2014 Share Posted November 12, 2014 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 More sharing options...
alaingomez Posted November 12, 2014 Author Share Posted November 12, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now