Search the Community
Showing results for tags 'calculated fields'.
Hi all, I'm newbie here. I would like to create a number input field for logged in user to update their daily number of chanting. There will be a number input field, update button, total number of chanting (updated number will sum total automatically) and last is total number of chanting for all users. I was no idea to get the solution on the function, so I was here to ask for proS to help me on this
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.