Search code examples
sqlsql-serverssmspowerapps

SQL Server: computed column to allow for adjustments?

I have a computed column known as TotalTime. This column is populated using the DATEDIFF function from two datapoints that we are getting entered in from Microsoft PowerApps - StartTime and EndTime.

However, sometimes it would be nice to be able to make adjustments to the calculation. For example, if the PowerApps user selects a certain control, it would add five minutes to TotalTime.

This doesn't necessarily have to come from PowerApps itself - but could be a stored proc or function. What is the cleanest way to do this without having to break the computed column?

Solution

  • Add a new column (TotalTimeAdjustment) reserved for admin adjustment. This will be normally NULL (or can be defaulted to 0). Modify your calculated column to include TotalTimeAdjustment column as the 3rd input alongside StartTime and EndTime. Whenever an adjustment is needed the admin can update TotalTimeAdjustment for the relevant record(s).