Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need a sql placeholder in an outer select

Posted on 2009-04-02
5
Medium Priority
?
1,003 Views
Last Modified: 2013-12-07
I have the below code and it works and does exactly what I am trying to accomplish which is to display by day, hour and 15 minute interval for each hour of the day a count for each interval.  What I need is to build in a placeholder and I don't know how.
For example below I have the first 3 hours of a 24 hour period on day 1
1      0      15      13
1      0      30      12
1      0      45      5
1      0      59      12
1      1      15      7
1      1      30      6
1      1      45      7
1      1      59      8
1      2      15      11
1      2      30      8


If there is nothing to count for hour 2 on the interval  45 or 59 how do I insert a 0 instead so that I don't have to manually add them to the spreadsheet when I import?


SELECT   DAY, HOUR, '15', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE       mycode  = 'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and       EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  < 16
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
union all
SELECT  DAY, HOUR, '30', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE      mycode  =  'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and (EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  > 15 AND  EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) < 31) 
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
union all
SELECT  DAY, HOUR, '45', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE     mycode  = 'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and (EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  > 30 AND  EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) < 46) 
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
union all
SELECT   DAY, HOUR, '59', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE       mycode  = 'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and (EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  > 45 AND  EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) < 60) 
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR;

Open in new window

0
Comment
Question by:thamilto0410
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:yuching
ID: 24057510
try this
Select X.Day, X.hour, X.minute, nvl(mycount,0) As MyCount
From (
        SELECT   Day, hour, minute
        FROM   (
            SELECT To_Number(To_Char(TRUNC(TO_DATE('01-DEC-05', 'DD-MON-YY')) + 1 * Level -1 , 'DD')) As Day
                FROM   DUAL
                CONNECT BY   LEVEL <=TO_DATE('31-DEC-05', 'DD-MON-YY') - TO_DATE('01-DEC-05', 'DD-MON-YY') + 1
        ) a, 
		( Select 1* Level  As hour
		 From dual Connect By Level <= 12)b, 
        (Select '15' As Minute From dual
        Union All
        Select '30' From dual
        Union All
        Select '45' From dual
        Union All
        Select '59' From dual
        ) c
 
) X Left Outer Join 
( 
SELECT   DAY, HOUR, '15' As Minute, sum(mycount) As MyCount 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE       mycode  = 'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and       EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  < 16
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
union all
SELECT  DAY, HOUR, '30', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE      mycode  =  'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and (EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  > 15 AND  EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) < 31) 
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
union all
SELECT  DAY, HOUR, '45', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE     mycode  = 'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and (EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  > 30 AND  EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) < 46) 
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
union all
SELECT   DAY, HOUR, '59', sum(mycount)
FROM 
(SELECT EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)) DAY, 
        EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)) HOUR,
        EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) MINUTE,
        COUNT(mydate) mycount
FROM    table
WHERE       mycode  = 'ABCD'
AND       trunc(mydate) between '01-DEC-05' and '31-DEC-05'
and (EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP))  > 45 AND  EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)) < 60) 
GROUP BY  EXTRACT(DAY FROM CAST(mydate AS TIMESTAMP)),
          EXTRACT(HOUR FROM CAST(mydate AS TIMESTAMP)),      
          EXTRACT(MINUTE FROM CAST(mydate AS TIMESTAMP)))
GROUP BY
DAY,HOUR
) Y On  X.Day = Y.Day And X.hour = Y.hour And X.Minute = Y.Minute;

Open in new window

0
 
LVL 1

Author Comment

by:thamilto0410
ID: 24058915
yuching,

That did not work.  Below is an example of what I got when it ran.

1      0      30      3
1      0      45      2

This is day 1 hour 0 and you can see it skipped over the 15 minute interval and 59 minute interval.  There were no new customers in that timeframe understood but for every instance like that I want a zero result returned.  In additional ideally I would like to fill in zeros even if 1 full hour passed and there were no new customers.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 24059749
try this...

should be more efficient too since you don't need to query yourtable 4 times, just once is enough.
  SELECT TO_NUMBER(TO_CHAR(quarterhour, 'dd')) day,
         TO_NUMBER(TO_CHAR(quarterhour, 'hh24')) hour,
         CASE
             WHEN TO_NUMBER(TO_CHAR(quarterhour, 'mi')) < 15 THEN 0
             WHEN TO_NUMBER(TO_CHAR(quarterhour, 'mi')) < 30 THEN 15
             WHEN TO_NUMBER(TO_CHAR(quarterhour, 'mi')) < 45 THEN 30
             ELSE 45
         END
             minute,
         nvl(mycount,0) mycount
    FROM (  SELECT CASE
                       WHEN TO_NUMBER(TO_CHAR(mydate, 'mi')) < 15
                       THEN
                           TRUNC(mydate, 'hh24')
                       WHEN TO_NUMBER(TO_CHAR(mydate, 'mi')) < 30
                       THEN
                           TRUNC(mydate, 'hh24') + 15 / 1440
                       WHEN TO_NUMBER(TO_CHAR(mydate, 'mi')) < 45
                       THEN
                           TRUNC(mydate, 'hh24') + 30 / 1440
                       ELSE
                           TRUNC(mydate, 'hh24') + 45 / 1440
                   END
                       mydate,
                   COUNT(mydate) mycount
              FROM yourtable
             WHERE mycode = 'ABCD'
               AND mydate >= TO_DATE('01-DEC-2005', 'DD-MON-YYYY')
               AND mydate < TO_DATE('31-DEC-2005', 'DD-MON-YYYY') + 1
          GROUP BY CASE
                       WHEN TO_NUMBER(TO_CHAR(mydate, 'mi')) < 15
                       THEN
                           TRUNC(mydate, 'hh24')
                       WHEN TO_NUMBER(TO_CHAR(mydate, 'mi')) < 30
                       THEN
                           TRUNC(mydate, 'hh24') + 15 / 1440
                       WHEN TO_NUMBER(TO_CHAR(mydate, 'mi')) < 45
                       THEN
                           TRUNC(mydate, 'hh24') + 30 / 1440
                       ELSE
                           TRUNC(mydate, 'hh24') + 45 / 1440
                   END) d,
         (    SELECT TO_DATE('2005-12-01', 'yyyy-mm-dd') + (LEVEL - 1) * 15 / 1440 quarterhour
                FROM DUAL
          CONNECT BY TO_DATE('2005-12-01', 'yyyy-mm-dd') + ((LEVEL - 1) * 15 / 1440) <
                         TO_DATE('31-DEC-2005', 'DD-MON-YYYY') + 1) t
   WHERE d.mydate(+) = t.quarterhour
ORDER BY quarterhour

Open in new window

0
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 31566041
That was exactly what I needed and it improved the efficiency of my statement.  And I learned something new.  THANK YOU SO MUCH!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24061916
glad I could help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question