Solved

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

Posted on 2015-01-20
12
430 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
[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
  • 7
  • 3
  • 2
12 Comments
 
LVL 74

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 74

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 74

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

752 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