Solved

Need a sql placeholder in an outer select

Posted on 2009-04-02
5
990 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
  • 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 73

Accepted Solution

by:
sdstuber earned 250 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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now