Home » SQL & PL/SQL » SQL & PL/SQL » How to Modify this Query to make it loop through Certain defined intervals
How to Modify this Query to make it loop through Certain defined intervals [message #184946] Fri, 28 July 2006 17:59 Go to next message
fahadsami
Messages: 31
Registered: May 2006
Member
hI ALL,

I have a TABLE1 with one Column as ColumnA which will always have values either Value1,Value2,Value3.

TABLE1:
ColumnA CreationDateandTime

Value1 8:56

Value1 8:51

Value3 8:49

Value1 8:47

Value2 8:45

Value3 8:30

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.

SELECT
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
WHERE A.CASECREATIONDATEANDTIME
between '19-JUL-2006 8:00:00 pM' and '19-JUL-2006 9:00:00 PM' .
Result::

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
Re: How to Modify this Query to make it loop through Certain defined intervals [message #184951 is a reply to message #184946] Fri, 28 July 2006 19:20 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
CREATE TABLE test AS
    SELECT 'value' || floor(dbms_random.VALUE(1, 4)) cola
          ,SYSDATE + LEVEL / (24 * 20) colb
    FROM dual
    CONNECT BY LEVEL < 1000;


SELECT hr
      ,COUNT(CASE
                 WHEN v.cola = 'value1' THEN
                  0
                 ELSE
                  NULL
             END) value1
      ,COUNT(CASE
                 WHEN v.cola = 'value2' THEN
                  0
                 ELSE
                  NULL
             END) value2
      ,COUNT(CASE
                 WHEN v.cola = 'value3' THEN
                  0
                 ELSE
                  NULL
             END) value3
FROM (SELECT t.*
            ,trunc(colb, 'hh') hr
      FROM test t
      WHERE colb BETWEEN SYSDATE AND SYSDATE + 1
      ORDER BY colb) v
GROUP BY hr;
Re: How to Modify this Query to make it loop through Certain defined intervals [message #184999 is a reply to message #184946] Sat, 29 July 2006 12:43 Go to previous message
fahadsami
Messages: 31
Registered: May 2006
Member
Hi scott,
I couldn't understan what this part of ur Query does..
""FROM (SELECT t.* ,trunc(colb, 'hh') hr FROM test t ""
Can u explain it a little more Clear.
T
** I tried running it but it gave me some error like you cant run it in a closed DB.I can not figure out what does that mean..
thanks in Advance,
Previous Topic: No of days approaching birthday
Next Topic: Scheduling the select statement
Goto Forum:
  


Current Time: Thu Dec 08 18:18:41 CST 2016

Total time taken to generate the page: 0.09431 seconds