Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-20
12
Medium Priority
?
439 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 400 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 1600 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

885 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