Home » SQL & PL/SQL » SQL & PL/SQL » Pivot table using unix time stamp
Pivot table using unix time stamp [message #444686] Tue, 23 February 2010 08:31 Go to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Hi,

I am trying to produce results in pivot format.
I want to know how many entries per day per month.

In other words how many entries on a monday for each month, how many on a tuesday for each month and so on.

The main problem I have is that the date column in the database uses unix time stamp.

I have managed to get the results I want for a particular month.
This code selects the entries for last month.
select 
      max(decode(pdayno, 1, cnt, null)) Sunday,
      max(decode(pdayno, 2, cnt, null)) Monday,
      max(decode(pdayno, 3, cnt, null)) Tuesday,
      max(decode(pdayno, 4, cnt, null)) Wednesday,
      max(decode(pdayno, 5, cnt, null)) Thursday,
      max(decode(pdayno, 6, cnt, null)) Friday,
      max(decode(pdayno, 7, cnt, null)) Saturday
From (SELECT pdayno, count(*) cnt
from lcc_Tproblem
where (PLOGDBY = 'WOODWARD_A' OR 
                        PLOGDBY = 'BASSETT_A' OR 
                        PLOGDBY = 'MISTRY_R' OR 
                        PLOGDBY = 'MEADOWS_K' OR 
                        PLOGDBY = 'SKINNER_R' OR 
                        PLOGDBY = 'DAVIS_Z' OR 
                        PLOGDBY = 'BLOUNT_L' OR 
                        PLOGDBY = 'PATEL_IS' OR 
                        PLOGDBY = 'PATEL_RI' OR 
                        PLOGDBY = 'THANDI_D' OR 
                        PLOGDBY = 'FOOT_R' OR 
                        PLOGDBY = 'AHMED_M') 
and (PENTERED BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, - 1), 'MM') 
                       - TO_DATE('01-JAN-1970', 'DD-MON-YYYY')) * (86400) 
AND ((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, - 1))) - TO_DATE('01-JAN-1970', 'DD-MON-YYYY')) +1)* (86400)-1)
group by  pdayno)


This code produces the result:
./fa/7493/0/

What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.

I did try to group by PENTERED(which is the unix time stamp column)
select pentered,
      max(decode(pdayno, 1, cnt, null)) Sunday,
      max(decode(pdayno, 2, cnt, null)) Monday,
      max(decode(pdayno, 3, cnt, null)) Tuesday,
      max(decode(pdayno, 4, cnt, null)) Wednesday,
      max(decode(pdayno, 5, cnt, null)) Thursday,
      max(decode(pdayno, 6, cnt, null)) Friday,
      max(decode(pdayno, 7, cnt, null)) Saturday
      
From (SELECT pentered, pdayno, count(*) cnt
from lcc_Tproblem
where (PLOGDBY = 'WOODWARD_A' OR 
                        PLOGDBY = 'BASSETT_A' OR 
                        PLOGDBY = 'MISTRY_R' OR 
                        PLOGDBY = 'MEADOWS_K' OR 
                        PLOGDBY = 'SKINNER_R' OR 
                        PLOGDBY = 'DAVIS_Z' OR 
                        PLOGDBY = 'BLOUNT_L' OR 
                        PLOGDBY = 'PATEL_IS' OR 
                        PLOGDBY = 'PATEL_RI' OR 
                        PLOGDBY = 'THANDI_D' OR 
                        PLOGDBY = 'FOOT_R' OR 
                        PLOGDBY = 'AHMED_M') 
and (PENTERED BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, - 1), 'MM') 
                       - TO_DATE('01-JAN-1970', 'DD-MON-YYYY')) * (86400) 
AND ((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, - 1))) - TO_DATE('01-JAN-1970', 'DD-MON-YYYY')) +1)* (86400)-1)
group by pentered, pdayno)
group by pentered

This gave me thousands of rows as each unix time stamp is unique.

Is there a way of grouping on unix time stamp.
  • Attachment: results.JPG
    (Size: 8.28KB, Downloaded 468 times)

[Updated on: Tue, 23 February 2010 08:46] by Moderator

Report message to a moderator

Re: Pivot table using unix time stamp [message #444687 is a reply to message #444686] Tue, 23 February 2010 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Asumming pentered is a date column use trunc
Re: Pivot table using unix time stamp [message #444688 is a reply to message #444687] Tue, 23 February 2010 08:45 Go to previous messageGo to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Unfortunately Pentered is a Number(10,0) data type.

This is what is causing so many problems. The data in this column is just a number, which is the number of seconds since 1-Jan-1970, I understand.

Any help appreciated

Andy
Re: Pivot table using unix time stamp [message #444689 is a reply to message #444686] Tue, 23 February 2010 08:48 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if you can compare it to dates - as you have in the where clause - you should be able to convert it to a date. Then you can use trunc.
Re: Pivot table using unix time stamp [message #444690 is a reply to message #444688] Tue, 23 February 2010 08:48 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Can you CREATE VIEW with a new pseudo-column which present the Pentered data as an actual Oracle DATE column?

Then you can query the new VIEW.
Previous Topic: sql query
Next Topic: Sql -Formatting Data
Goto Forum:
  


Current Time: Sat Dec 10 17:02:17 CST 2016

Total time taken to generate the page: 0.10986 seconds