Using to tables for an average number....

[leeh007]

Experts!

I've been having a bit of a problem....

I have data on a compressor in two tables. One table contains the % load that the compressor is under as integer and the other table tells me when the compressor is turned on as a boolean.

I want to know what the average level of load is during the time the compressor is on.

CREATE PROCEDURE dbo.PenFridgeSumRun
(
@BeginDateID As datetime = 0,
@EnddateID As datetime = 0,
@Tagname As varchar(250),
@RunTagname As varchar(250),
@Avg As float output
)

AS

BEGIN

SELECT @Avg = AVG (ISNULL(AH.Value, 0))
FROM AnalogHistory AH
LEFT JOIN DiscreteHistory DH ON DH.TagName = @RunTagname
WHERE AH.datetime BETWEEN @BeginDateID AND @EndDateID
AND DH.Value = 1

END

I pass the data for the date and or variable from another SP.

Exec [PenFridgeNew] '14 Mar 2006 08:00:00', '21 Mar 2006 08:00:00' 'Gis_Pen_Container_Mycom_Run_Pen', 'Gis_Pen_Container_Mycom_Run', @avg output

I currently get zero's back for the Average.

Any ideas??

Cheers

Lee

 

[VintageWine]

1. In the post above your procedure is named "PenFridgeSumRun" but you call procedure "PenFridgeNew".

2. In your SQL there is no join between the tables. The join condition you give just limits the records from DiscreteHistory. Your query will give the average of ALL records (i.e. all compressors) in AnalogHistory within the time period, providing there is at least one record in DiscreteHistory for the compressor of interest.

3. Are records created into Analog History at regular time intervals for all compressors that are turned on? If not you are going to need a considerably more involved stored procedure.

4. Nit picking this one, and may be insignificant in your case (my programs usually deal with money, so I have to be exact). If there is a record timed at 21 Mar 2006 08:00:00' precisely in AnalogHistory it would be included in both the calls below:

Exec [PenFridgeNew] '14 Mar 2006 08:00:00', '21 Mar 2006 08:00:00' 'Gis_Pen_Container_Mycom_Run_Pen', 'Gis_Pen_Container_Mycom_Run', @avg output

Exec [PenFridgeNew] '21 Mar 2006 08:00:00', '28 Mar 2006 08:00:00' 'Gis_Pen_Container_Mycom_Run_Pen', 'Gis_Pen_Container_Mycom_Run', @avg output

The work-around is to change the WHERE clause thus:
WHERE AH.datetime >= @BeginDateID AND AH.datetime < @EndDateID

Have fun
Simon

 

[leeh007]

Hi Simon,

1. Yep...my mistake it's supposed to call 'PenFridgeSumRun'...

2. The value in @Tagname (Gis_Pen_Container_Mycom_Run_Pen) is supposed to setup up AnalogueHistory, do i need a WHERE under the SELECT to specify the date range?

3. Yeah it's records every 5 seconds for both the power level and change of state for the on/off side.

4. i'll have a play with the date selection.

Cheers for that,

Lee (Always trying to have fun!)

 

[VintageWine]

>do i need a WHERE under the SELECT to specify the date range?
I cannot tell further without seeing schema for the two tables.

>every 5 seconds
>i'll have a play with the date selection
If you want the average of 5 second readings over a whole week, then the error introduced by using BETWEEN will be imperceptible.

Simon

 

[leeh007]

OK, how about you forget the code that i have written...i think it's causing more porblems than it's worth....

How would you do it?

There is no Key for the tables, here is a layout of the tables

DiscreteHistory
DateTime, Tagname, Value
21 Mar 2006 08:00:00, Gis_Pen_Container_Mycom_Run, 1
21 Mar 2006 08:00:15, Gis_Pen_Container_Mycom_Run, 0
etc...

AnanlogueHistory
DateTime, Tagname, Value
21 Mar 2006 08:00:00, Gis_Pen_Container_Mycom_Run_Pen, 50
21 Mar 2006 08:00:05, Gis_Pen_Container_Mycom_Run_Pen, 51
21 Mar 2006 08:00:10, Gis_Pen_Container_Mycom_Run_Pen, 55
21 Mar 2006 08:00:15, Gis_Pen_Container_Mycom_Run_Pen, 3
etc...

So when Gis_Pen_Container_Mycom_Run.Value = 1, take an everage of Gis_Pen_Container_Mycom_Run_Pen.Value.

Gis_Pen_Container_Mycom_Run.Value may equal 1 any number of times during the start and end datetime

Cheers

Lee

 

[VintageWine]

You could try the following.

SELECT
@Avg = AVG(A.Value)
FROM
(
SELECT D1.DateTime AS Start
,MIN(D2.DateTime) AS Finish 
FROM DiscreteHistory D1
JOIN DiscreteHistory D2 ON D2.TagName = D1.TagName AND D2.DateTime > D1.DateTime AND D2.Value = 0
WHERE D1.DateTime >= BeginDateID AND D1.DateTime < @EnddateID
AND D1.TagName = @Tagname 
AND D1.Value = 1
GROUP BY D1.DateTime
) Q
JOIN AnalogueHistory A
ON A.DateTime >= Q.Start AND A.DateTime < Finish
AND A.TagName = @RunTagname

It has shortcomings
- If the compressor is turned on at the beginning of the time period, that time will be ignored.
- If the DiscreteHistory data is not perfect (e.g.
DateTime, Tagname, Value
21 Mar 2006 08:00:00, Gis_Pen_Container_Mycom_Run, 1
21 Mar 2006 08:00:15, Gis_Pen_Container_Mycom_Run, 1
21 Mar 2006 08:00:40, Gis_Pen_Container_Mycom_Run, 0
etc...)
it will give wrong results.


The alternative is to use a cursor, like this:

/* Define cursor and local vars */
DECLARE DiscreteList INSENSITIVE CURSOR FOR
SELECT
DateTime,
Value
FROM
DiscreteHistory
WHERE 
D1.DateTime >= BeginDateID AND D1.DateTime < EnddateID
AND D1.TagName = @Tagname 

DECLARE
  @DateTime    datetime
, @Value       bit        --integer?
, @State       integer    -- 0 = Off, 1 = On, 2 = start, 3 = Finish (switched off), 4 = Finish (switched on)
, @Sum         integer    -- Sum of all valid values of AnalogHistory.Value.  May need bigint
, @Count       integer    -- number of values in @Sum
, @Start       datetime   -- When switched on
, @DoSummation bit        -- Internal use, that have a period to sum

/* Initialise */

SELECT 
  @State = 2
, @Sum = 0
, @Count = 0
, @Start = @BeginDateID
, @DoSummation = 0

/* Cycle through records in the cursor */

OPEN DiscreteList 

WHILE 1=1 BEGIN

    FETCH DiscreteList INTO
      @DateTime
      , @Value

    IF (@@FETCH_STATUS<>0)
        SET @State = 3 + @State  -- Finish

    /* Individual record processing here */
    IF @State = 0
        /* If switching on, note start time */
        IF @Value = 1
        BEGIN
            SELECT @State = 1
              , @Start = @DateTime
        END
    END ELSE
        IF @State = 1
        BEGIN
            /* If switching of, trigger summation */
            IF @Value = 0
                SELECT @DoSummation = 1
                , @State = 0 
            END
         END ELSE
             IF @State = 2
             BEGIN
                 /* Consider period from start of interest to first on/off reading */
                 IF @Value = 0
                     /* assume was switched on at start */
                     SELECT @DoSummation = 1
                      , @State = 0 
                 ELSE
                     /* assume was switched off at start */
                     SELECT @Start = @Datetime
                      , @State = 1 
             END ELSE
                 IF @State = 4
                 BEGIN
                     /* Consider period from last swtich to end of time period */
                     SELECT @DoSummation = 1
                      , @DateTime = @EnddateID
                  END

    IF @DoSummation = 1
    BEGIN
        /* The summation */
        SELECT
        @Sum = @Sum + @Value
        , @Count = @Count + 1
        FROM AnalogHistory
        WHERE
        DateTime >= @Start AND DateTime < @DateTime
        AND TagName = @RunTagname
        AND @Value IS NOT NULL  -- Is this needed>?
    END

    /* Finished? */ 
    IF @State >= 3
        BREAK
END

/* Calculate average */
SET @Avg = @Sum / @Count

/* Cleanup */
CLOSE DiscreteList 
DEALLOCATE DiscreteList

I have not compiled, let alone debugged it, but the concept is there.
To leave you some work to do, it need enhancing to handle the situation where there are no records in DiscreteHistory for the compressor of interest during the specified time period.

 

[leeh007]

Thanks mate, i'm finally going get to have a play and see if it works, thanks for your time

Lee