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 - 2explanation:
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 lastnametbl_event
eventid eventtime driverid caridtbl_eventviolation
eventviolatinid eventid violationid status (1 = no, 2 = yes radio buttons)tbl_violation
violationid violationname pointstbl_violationclass
violationclassid classnamei 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
No comments:
Post a Comment