TSQL Calculated Field and Conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TSQL Calculated Field and Conditions

Author

Topic

fenixmarcus
Starting Member

4 Posts

Posted - 2014-11-03 : 01:09:48

Hi guys, 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
Feb. 4, 2014 - 4
Apr. 25, 2014 - 4
- 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
DriverLicense
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.Notes
ORDER BY Points desc

Thank you for your time. Looking forward for the solution.

marionjlite
Starting Member

1 Post

Posted - 2014-11-03 : 01:28:06

Thank you ever so for you article post. Really Great

Thanks and Regards

Quickbook Hosting Provider
IT analyst
SageNext Infotech LLC
207 Hudson Trace
Suite 112, Augusta, Georgia
Call Support: +1.801.610.6141
Sales : +1.855.922.7243 (Toll Free)

Web: http://www.thesagenext.com
http://www.facebook.com/sagenextinfo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-11 : 07:08:10

can you post some sample data of the scenario and expected result ?

quote:
suspension is carried out then 14 points is removed

How is this reflected in your table ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources