Solved

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

Posted on 2015-01-20
12
413 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 34

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
 

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 34

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

707 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

10 Experts available now in Live!

Get 1:1 Help Now