sql - Calculated Field With Conditions -

sql - Calculated Field With Conditions -

i need expertise on problem have. have vehicle/driver inspection database , i'm trying create traffic violation point system, scheme when drivers commit traffic offences incur driving-offence points.

system: points violations occurred within lastly 12 months of 1 added calculate point total. if accumulated points reached 14 points driver suspended points removed or minus 14 points after suspension has been served.

example:

date of offence - number of points

aug. 6, 2013 - 6 aug. 6, 2013 - 6 feb. 4, 2014 - 4 apr. 25, 2014 - 4 apr. 25, 2014 - 2 may 8, 2014 - 2

explanation:

on apr 25, 2014 2 offences incurred , (aug 6, 2013 - apr. 25 2014) 14 points have accumulated (6 + 4 + 4) , suspension carried out 14 points removed. 2nd offence on apr. 25, 2014 carried forwards , added may 8, 2014 points (total 4 points).

below 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 sql code sum accumulated points not whole condition. see below:

select a.driverid, a.driverlicense, a.firstname + ' ' + a.lastname drivername, a.firstname, a.lastname, a.drivermobileno1, a.notes, sum(a.points) totalpoints ( 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 dbo.drivers inner bring together dbo.event on dbo.drivers.driverid = dbo.event.driverid inner bring together dbo.eventviolation on dbo.event.eventid = dbo.eventviolation.eventid inner bring together dbo.violation on dbo.eventviolation.violationid = dbo.violation.violationid inner bring together dbo.violationclass on dbo.violation.violationclassid = dbo.violationclass.violationclassid dbo.eventviolation.status = 2 , dbo.violationclass.classname 'c' , dbo.violation.violationname not 'load not correctly restrained' , dbo.violation.violationname not 'driving without uae license' ) grouping a.driverid, a.driverlicense, a.firstname, a.lastname, a.drivermobileno1, a.notes order points desc

please seek modify code work on conditions set above

thank time. looking forwards solution.

sql tsql sql-server-2008-r2 conditional-statements calculated-columns