Solved

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

Posted on 2015-01-20
12
426 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

829 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