On the fly formula - SQL Server Forums

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

On the fly formula

Author

Topic

nhighton
Starting Member

11 Posts

Posted - 2011-01-23 : 08:22:05

Hi all,

I'm having trouble with creating something like a computed column. My database is updated every hour with up to 96 new rows or entries. Columns are:
-Date
-Hour (0-23)
-Channel (0-96)
-KwH
-A
-B
-Metric

Date, Hour and KwH already work, but A and B are a combination of several KwH readings for specific channels. So 'A' might be the KwH combination of channels 32, 46, 81 and 90 for any given hour. The Metric column is set up and functional - I used the computed column formula: ([A] / [B]) and this works fine.

How could I represent the total of specific channels as A and B? I've done this with a one off query (MD is date, Ch is channel and Hr is hour):

UPDATE kWHrByHour
SET Metric = (SELECT SUM(KwH) FROM kWHrByHour WHERE MD='1/3/2011' AND Ch IN (84, 87, 92) AND Hr = 1)
WHERE (Ch = 1) AND (MD = '1/3/2011') AND (Hr = 1)

This does exactly what I want, but obviously only does one query at a time, whereas I want it automated to perform this every hour, with the correct query criteria. I.e. the next query for the next hour would contain (Hr = 2) and so on...

Could I use a stored procedure or would another computed column work for A and B?

I hope I explained myself properly!

Any help appreciated.

Nhighton

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-23 : 09:55:00

You can write a function (which accepts input parameters) and set the function as the computed column value.
However, you cannot make the column persisted.

In your case, I would write a DELETE, UPDATE and INSERT trigger to calculate the new Metric.
In the trigger you can easy calculate the current hour, and you can also have a "translate" table which has two columns; source channel and target channel. That way, there is no need to ever hardwire channel values. If (and when) the channel values changes in the future, all you have to do is to change the translate table, not the trigger code!

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-23 : 10:07:17

Your best bet would be to have a trigger to call a stored procedure which calculate the new values.
This procedure can also be called by a job, for which schedule you can set to run every hour. This ensures the values are updated every hour even if there is no activity.

Your procedure could look something like this

CREATE PROCEDURE dbo.uspCalculateColumns
AS

SET NOCOUNT ON

DECLARE	@Today DATE = GETDATE(),
	@Now INT = DATEPART(HOUR, GETDATE())

UPDATE		w
SET		w.Metric = q.Measure
FROM		dbo.kWHrByHour AS w
INNER JOIN	(
			SELECT		t.TargetChannel,
					SUM(x.KwH) AS Measure
			FROM		dbo.kWHrByHour AS x
			INNER JOIN	dbo.Translate AS t ON t.SourceChannel = x.Ch
			WHERE		x.MD = @Today
					AND x.Hr = @Now
			GROUP BY	t.TargetChannel
		) AS q ON q.TargetChannel = w.Ch
WHERE		w.MD = @Today
		AND w.Hr = @Now

and you can call it form either

1) A trigger to ensure aggregation is up to date whenever a record is changed
2) A job to ensure aggregation is up to date every hour even if there are no changes

Your dbo.Translate table could look like this

TargetChannel	SourceChannel
1		84
1		87
1		92

N 56°04'39.26"
E 12°55'05.63"

nhighton
Starting Member

11 Posts

Posted - 2011-01-23 : 10:40:53

Thanks for the reply!

I'm not at all familiar with triggers or stored procedures but understand what they are. So I'm not sure what x.MD and t and w are.

Could you explain what the steps of this procedure do?

Thanks again,

Nhighton

nhighton
Starting Member

11 Posts

Posted - 2011-01-23 : 11:05:15

Ok I see kind of what that procedure does. I'd like the channels to be used defined from the front end of the web app. So have a text box with a button click event that applies the user's selection of channels to the stored procedure. Also how would I get the format of $now to match that of Hr in my database, i.e. extract just the hour and discard the day, month, year, minutes, seconds and AM or PM? I'm unsure about how to use or implement the translate table!

Thanks

nhighton
Starting Member

11 Posts

Posted - 2011-01-23 : 12:38:58

I worked some more on it, and wouldn't the below (as a procedure) carry out the desired function?


DECLARE	@Today DATE = GETDATE(),
	                @Now INT = DATEPART(HOUR, GETDATE())

UPDATE kWHrByHour
SET PUEA = (SELECT SUM(KwH) FROM kWHrByHour WHERE (MD=@Today) AND (Ch IN (84, 87, 92)) AND (Hr = @Now))
WHERE (Ch = 1) AND (MD = CONVERT(DATE,@Today,101)) AND (Hr = @Now)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-23 : 13:00:30

Yes, but you have to change the code EVERY time the requirements change; for example adding Ch86.
Try to avoid building solutions that requires change of code whenever business requirements change.
Having a "Translate" table (in this case) only requires you to add {1, 86} to the "Translate" table.

What do you do if management decides to aggregate channels {19, 21, 44 and 71} to channel 2? Add a new update statement?
Or just add the four new records to the "Translate" table?

N 56°04'39.26"
E 12°55'05.63"

nhighton
Starting Member

11 Posts

Posted - 2011-01-23 : 13:17:06

Because I can pass user-defined variables in from the front end of the web app into the procedure. E.g. instead of