1

I am very new to SQL programming. I am trying to modify a monthly runtime report so that I can get the information on a weekly time interval. My code just outputs 0's when the starttime and endtime are in the same month. I can't figure out why. 

Function TagCount ( Tag1Name char(24), Tag1Cond integer, Tag2Name char(24), Tag2Cond integer, StartTime timestamp, EndTime timestamp)
local count real;

count = (select count(*) from
(SELECT NAME,TS,VALUE AS V1VALUE FROM HISTORY WHERE (NAME = Tag2Name)
AND (PERIOD = '0:00:15')
AND (REQUEST='1')
AND (STEPPED='1')
AND TS between StartTime  and EndTime  JOIN
(SELECT NAME,TS,VALUE AS F1VALUE FROM HISTORY WHERE (NAME = Tag1Name)
AND (PERIOD = '0:00:15')
AND (REQUEST='1')
AND (STEPPED='1')
AND TS between StartTime  and EndTime ) USING (TS)) where V1VALUE = Tag2Cond and F1Value = Tag1Cond);

Return(count);
END
local
    starttime timestamp,
    run_hours real,
    i integer,
    endtime timestamp;

starttime = '01-JUN-10 00:00:00.0';
endtime = '12-AUG-10 00:00:00.0';

write ',';
FOR i=1 TO 21 DO

run_hours = 1/240.0*TagCount('runningtag',1,'producttag',i,starttime,endtime);
IF run_hours IS NULL THEN run_hours = 0; END
write run_hours;            
END

Sample Data:

NAME TS F1VALUE
runningtag 16-AUG-10 15:35:30.1 1
runningtag 16-AUG-10 15:35:45.1 1
runningtag 16-AUG-10 15:36:00.1 1
runningtag 16-AUG-10 15:36:15.1 1
runningtag 16-AUG-10 15:36:30.1 1
runningtag 16-AUG-10 15:36:45.1 1
runningtag 16-AUG-10 15:37:00.1 1

NAME TS F1VALUE
productcode 16-AUG-10 15:35:30.1 13
productcode 16-AUG-10 15:35:45.1 13
productcode 16-AUG-10 15:36:00.1 13
productcode 16-AUG-10 15:36:15.1 13
productcode 16-AUG-10 15:36:30.1 13
productcode 16-AUG-10 15:36:45.1 13
productcode 16-AUG-10 15:37:00.1 13

I am trying to estimate runtime in hours. Just to clarify.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
tigerfan2010
  • 13
  • 3

1 Answers1

1

Firstly, can you try simplifying your SQL to:

SELECT count(*)
  FROM history h
 WHERE h.period = '0:00:15'
       AND h.request = '1'
       AND h.stepped = '1'
       AND h.ts BETWEEN StartTime AND EndTime
       AND (   (h.name = Tag1Name AND h.value = Tag1Cond) 
            OR (h.name = Tag2Name AND h.value = Tag2Cond));

Other than that, I can't see why you wouldn't get values for two dates in the same month -- unless your data only contained monthly entries, which I'm guessing isn't the case.

Could you post some sample data from the history table?

Tom
  • 4,742
  • 25
  • 32
  • I think I want to add some kind of join statement or something to the code. I your code for the last hour. So I want the code to count the number of times when the running tag is 1 and the product is 13. That should be 240 times. A tag for every 15 secs for an hour. Your code output 480 so it count up both of them. I want to know how to compare the two tables. I appreciate the help I don't think I was clear enough on what I want I am trying to do. – tigerfan2010 Aug 17 '10 at 14:26
  • I was able to modify your code slightly and get it to work. Thanks for the help. – tigerfan2010 Aug 17 '10 at 19:37