|How to Modify this Query to make it loop through Certain defined intervals [message #184946]
||Fri, 28 July 2006 17:59
Registered: May 2006
I have a TABLE1 with one Column as ColumnA which will always have values either Value1,Value2,Value3.
I am using the below query to retrieve the count of how many Value1's,Value2's and Value3's exist in the ColumnA for the Given point of Time by using a 'where' condition to get the count between specified Time.
COUNT (case when A.ColumnA ='Value1' THEN 0 ELSE NULL END) Value1,
COUNT (case when A.ColumnA ='Value2' THEN 0 ELSE NULL END) Value2,
COUNT (case when A.ColumnA ='Value3' THEN 0 ELSE NULL END) Value3
from TABLE1 A
between '19-JUL-2006 8:00:00 pM' and '19-JUL-2006 9:00:00 PM' .
Value1 value2 Value3
3 2 1
Right now i am Manually changing the TimeLimits for every 1hour to find the Count of Values at that particular perioud of Time, I would like to know how can i Loop it so that I will get it for every one hour and also i want to Add a Dummy Column before the Value1 as my First Column which should show me the Lower Time Limit....
Expected Result should be some thing like this..(for some imaginary existance of values for the ColumnA Field)
LowerTimeLimit Value1 value2 Value3
8:00P.M 3 2 1
9:00 P.M 1 4 5
10:00P.M 0 0 0 (Since the Time is only 9:30 by now)
NOte;; It should not go into an Infinite Loop as i would be risking My job.
Thanks in Advance