Solved

ORACLE 10G: Format date field to represent a date per column

Posted on 2015-01-20
12
424 Views
Last Modified: 2015-01-20
Experts,

I need help on taking my date field and displaying it by columns and showing the total counts for each ticket.  Here is the query that I am working with.

SELECT IMPACTED_DIVISIONS, COUNT(TICKET_ID) TICKET_CNT
            FROM DIVISION.EVENTS
             WHERE DATE_CREATED >= ADD_MONTHS(TRUNC(SYSDATE), -3)
               AND DATE_CREATED < TRUNC(SYSDATE)
 GROUP BY IMPACTED_DIVISIONS

Open in new window



OUTPUT:
IMPACTED_DIVISIONS      TICKET_CNT
Indianapolis             135
Bakersfield             243
Central Florida    1245
Tampa                     1457

What I would like for the output to be the following (not real data):
IMPACTED_DIVISIONS      TICKET_CNT      1/13/2015      1/14/2015      1/15/2015      1/16/2015      1/17/2015      1/18/2015
Indianapolis      135      22      35      18      75      15      16
Bakersfield      243      30      75      36      85      15      20
Central Florida      1245      36      36      78      300      253      396
Tampa      1457      100      250      250      148      360      510

Also my date output is the following:
10/20/2014 2:16:15 AM

Thank you!
0
Comment
Question by:Maliki Hassani
  • 7
  • 3
  • 2
12 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40559819
You can't have a dynamic number of columns.  
SQL requires all of the columns to be known at parse time (i.e. before it executes)

We can do a fixed number of columns, say 7 days.  Would that be adequate?
0
 

Author Comment

by:Maliki Hassani
ID: 40559840
Yes that would work!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40559841
That kind of output is called "matrix" or "cross-tab" output. Reporting tools (like: Crystal Reports, Oracle Reports, etc.) have been able to do this for years, but simple SQL queries cannot.  It is possible to construct rather complex SQL queries to do this, but they require "hard-coding" of the various values for each column that you want in the output.  Thus, they do not dynmically adjust to new data over time.

Oracle may have introduced an ability to do this dynamically directly in a SQL query fairly recently, but I have not explored that option.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Maliki Hassani
ID: 40559852
Thank you markgeer,  It would work if I showed a fixed amount of days like sdstuber stated?  The reason for this request is to build a date selection to use in ArcMap (Esri map software).  More ArcMap talk..  With this software it queries my view for each point and click.  This is making it very slow.  Possible solution..  Create a function with this query request and have it load into a table.  It will run the job every 15 minutes.  This will hopefully make the interactions with the map faster.  Does this sound like a good solution?
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 40559913
This does not look to me like the best option: " Create a function with this query request and have it load into a table.  It will run the job every 15 minutes."  To me, that looks like the last option that could be considered if nothing else works better.

The best option I can think of is to create a view that can return this multi-column output dynamically, as the data changes over time.  I think that may be possible, but I don't know of a way to do this .
0
 

Author Comment

by:Maliki Hassani
ID: 40559928
Okay, I will review the pros and cons.
0
 

Author Comment

by:Maliki Hassani
ID: 40559943
sdstuber - Please let me know if you have any questions on building the matrix.  Thank you so much.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 40559945
SELECT impacted_divisions,
         COUNT(*) ticket_cnt,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 7 THEN ticket_id END) ticket_cnt_day1,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 6 THEN ticket_id END) ticket_cnt_day2,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 5 THEN ticket_id END) ticket_cnt_day3,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 4 THEN ticket_id END) ticket_cnt_day4,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 3 THEN ticket_id END) ticket_cnt_day5,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 2 THEN ticket_id END) ticket_cnt_day6,
         COUNT(CASE WHEN TRUNC(date_created) = TRUNC(SYSDATE) - 1 THEN ticket_id END) ticket_cnt_day7
    FROM division.events
   WHERE date_created >= ADD_MONTHS(TRUNC(SYSDATE), -3) AND date_created < TRUNC(SYSDATE)
GROUP BY impacted_divisions
ORDER BY ticket_cnt
0
 

Author Comment

by:Maliki Hassani
ID: 40559959
Ahh, thank you so much!  I will review what is best approach for my map speed. Again thank you.
0
 

Author Comment

by:Maliki Hassani
ID: 40560070
If I need to open up another question I will.  I seem to have nulls being displayed when I change the ticket_Id to a number field (Impact_Score).  I changed the COUNT to SUM and I get nulls in the records.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40560109
off hand, I'd say the problem is you are either missing rows or have null data in that column.

If that doesn't apply then please open a new question with some sample data to illustrate the problem
0
 

Author Comment

by:Maliki Hassani
ID: 40560122
Fixed the issue thanks
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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

777 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