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  |
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:

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 1116 times)
[Updated on: Tue, 23 February 2010 08:46] by Moderator Report message to a moderator
|
|
|
|
Re: Pivot table using unix time stamp [message #444688 is a reply to message #444687] |
Tue, 23 February 2010 08:45   |
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
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 12 21:44:42 CST 2025
|