Counting how many minutes something is turned on?

[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

 

[leeh007]

Hi,

I've got me treads mixed up....i'm trying to work out the amount of minutes that the compressor will be turned on ie. equal to 1 this the code i'm using now, but i'mnot sure that it's working...

SELECT @PenHow2On =
ISNULL((SELECT COUNT(*) FROM DiscreteHistory
WHERE datetime BETWEEN @BeginDateId AND @EndDateId
AND TagName = 'Gis_Pen_Howden_2_Run'
AND Value = 1), 0)

SELECT @PenHow2Count =
ISNULL((SELECT COUNT(*) FROM DiscreteHistory
WHERE datetime BETWEEN @BeginDateId AND @EndDateId
AND TagName = 'Gis_Pen_Howden_2_Run'), 0)

IF @PenHow2On <> 0
BEGIN
SELECT @PenHow2Perc = @PenHow2On * 100 / @PenHow2Count
SELECT @PenHow2Min = ((DATEDIFF(n, @BeginDateID, @EndDateID )) * @PenHow2Perc) / 100
END
ELSE
BEGIN
SELECT @PenHow2Perc = 0,
@PenHow2Min = 0
END

As you can see from the code i'm trying to wokr out the percentage of the total time the compressor is on against the total time, i feel like i should be working out the total mintues it's on, not a total count.

I could go on...please ask any questions for more information

Cheers

Lee