Home » SQL & PL/SQL » SQL & PL/SQL » SQL query for collection load (oracle 9.2, unix)
SQL query for collection load [message #304012] Mon, 03 March 2008 13:27 Go to next message
prashanth.spl
Messages: 4
Registered: March 2008
Junior Member
Hi,
i have a 2 tables
1. CRM_AUDIT with fields
SERVICE_REQ_KEY_ID VARCHAR(15),
BUSCOMP_NAM VARCHAR(75) ,
FIELD_NAM VARCHAR(75),
OPER_CD VARCHAR(30) ,
OLD_VAL VARCHAR(255) ,
NEW_VAL VARCHAR(255),
OPER_DT TIMESTAMP(0),
FISCAL_YYYYWW_NUM NUMBER(6),
ROW_CREATE_GMT_DTTM TIMESTAMP(0),
ROW_MODIFY_GMT_DTTM TIMESTAMP(0),
ROW_CREATE_MST_DTTM TIMESTAMP(0),
ROW_MODIFY_MST_DTTM TIMESTAMP(0)

with data:
[SERVICE_REQ_KEY_ID][BUSCOMP_NAM] [FIELD_NAM OPER_CD][OLD_VAL][NEW_VAL][OPER_DT][FISCAL_YYYYWW_NUM]
[1-40NRUH][SERVICE REQUEST][STATUS][MODIFY][OPEN][UNASSIGNED][2006-01-01 04:44:54][200601]
[1-40NRUH][SERVICE REQUEST][CONTACT] [LAST NAME][MODIFY][NULL][2006-01-01 04:44:54][200601]
[1-40NRUH][SERVICE REQUEST][ID] [NEW RECORD][NULL][NULL][2006-01-01 04:44:54][200601]
[1-40NRUH][SERVICE REQUEST][OWNER GROUP][MODIFY][NULL][TIC-INBOX][2006-01-01 04:45:33][200601]
[1-40NRUH][SERVICE REQUEST][OWNER][MODIFY][NULL][RE006C][2006-01-02 08:42:46][200601]
[1-40NRUH][SERVICE REQUEST][STATUS][MODIFY][UNASSIGNED][OPEN][2006-01-02 08:42:46][200601]
[1-40NRUH][SERVICE REQUEST][STATUS][MODIFY][OPEN][CUSTOMER REPLY PERIOD][2006-01-02 09:28:37][200601]
[1-40NRUH][SERVICE REQUEST][STATUS][MODIFY][CUSTOMER REPLY PERIOD][CLOSED][2006-01-09 09:31:55][200602]

2.CRM_RQST_DETAIL with fields
SERVICE_REQ_KEY_ID VARCHAR(15) PRIMARY KEY,
SERVICE_REQ_ID VARCHAR(64),
CREATE_DT TIMESTAMP(0)

with data:
[SERVICE_REQ_KEY_ID][SERVICE_REQ_ID][CREATE_DT]
[1-40NRUH][1-242973881][2005-12-26 04:44:54]

i want to create a table and load the data so that new table contains the no.of days(excluding weekends and including weekends,both) where particular status situated.....(OPEN,ASSIGNED,PENDING......etc.. are status codes)..

a status can repeat any no of times in a lifecycle of a ticket...



i want to load table ticket_status_collect table with data by calculating floating point days per status...

and main point is : CRM_AUDIT table contains ..around 10 million rows...


how can i calculate the floting point days??? and load the data in to the new table...

the final result should be like :

[SERVICE_REQ_ID][SERVICE_REQ_KEY_ID][STATUS][Sum(days_incl_weekends)][Sum(days_excl_weekends)]
[1-242973881][1-40NRUH][CUSTOMER REPLY PERIOD][7.00229][5.00229]
[1-242973881][1-40NRUH][OPEN][6.03184][4.03184]
[1-242973881][1-40NRUH][UNASSIGNE][1.16519][0.36303]



here i am giving the create table staments and insert statements.

CREATE TABLE IDAHADM.CRM_AUDIT

(
SERVICE_REQ_KEY_ID VARCHAR(15),
BUSCOMP_NAM VARCHAR(75) ,
FIELD_NAM VARCHAR(75),
OPER_CD VARCHAR(30) ,
OLD_VAL VARCHAR(255) ,
NEW_VAL VARCHAR(255),
OPER_DT TIMESTAMP(0),
FISCAL_YYYYWW_NUM NUMBER(6),
ROW_CREATE_GMT_DTTM TIMESTAMP(0),
ROW_MODIFY_GMT_DTTM TIMESTAMP(0),
ROW_CREATE_MST_DTTM TIMESTAMP(0),
ROW_MODIFY_MST_DTTM TIMESTAMP(0)
)


CREATE TABLE IDAHADM.CRM_RQST_DETAIL

(
SERVICE_REQ_KEY_ID VARCHAR(15) PRIMARY KEY,
SERVICE_REQ_ID VARCHAR(64),
CREATE_DT TIMESTAMP(0)
)

INSERT INTO CRM_RQST_DETAIL ( SERVICE_REQ_KEY_ID, SERVICE_REQ_ID,
CREATE_DT ) VALUES (
'1-40NRUH', '1-242973881', TO_Date( '12/26/2005 04:44:54 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO CRM_AUDIT ( SERVICE_REQ_KEY_ID, BUSCOMP_NAM, FIELD_NAM, OPER_CD, OLD_VAL, NEW_VAL,
OPER_DT, FISCAL_YYYYWW_NUM, ROW_CREATE_GMT_DTTM, ROW_MODIFY_GMT_DTTM, ROW_CREATE_MST_DTTM,
ROW_MODIFY_MST_DTTM ) VALUES (
'1-40NRUH', 'SERVICEREQUEST', 'STATUS', 'MODIFY', 'OPEN', 'CUSTOMERREPLYPERIOD', TO_Date( '01/02/2006 09:28:37 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 200601, TO_Date( '02/20/2008 09:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 09:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '02/20/2008 02:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 02:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO CRM_AUDIT ( SERVICE_REQ_KEY_ID, BUSCOMP_NAM, FIELD_NAM, OPER_CD, OLD_VAL, NEW_VAL,
OPER_DT, FISCAL_YYYYWW_NUM, ROW_CREATE_GMT_DTTM, ROW_MODIFY_GMT_DTTM, ROW_CREATE_MST_DTTM,
ROW_MODIFY_MST_DTTM ) VALUES (
'1-40NRUH', 'SERVICEREQUEST', 'STATUS', 'MODIFY', 'CUSTOMERREPLYPERIOD', 'CLOSED'
, TO_Date( '01/09/2006 09:31:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), 200602, TO_Date( '02/20/2008 09:13:16 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '02/20/2008 09:13:16 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 02:13:16 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '02/20/2008 02:13:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));


INSERT INTO CRM_AUDIT ( SERVICE_REQ_KEY_ID, BUSCOMP_NAM, FIELD_NAM, OPER_CD, OLD_VAL, NEW_VAL,
OPER_DT, FISCAL_YYYYWW_NUM, ROW_CREATE_GMT_DTTM, ROW_MODIFY_GMT_DTTM, ROW_CREATE_MST_DTTM,
ROW_MODIFY_MST_DTTM ) VALUES (
'1-40NRUH', 'SERVICEREQUEST', 'STATUS', 'MODIFY', 'UNASSIGNED', 'OPEN', TO_Date( '01/02/2006 08:42:46 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 200601, TO_Date( '02/20/2008 09:13:07 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 09:13:07 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '02/20/2008 02:13:07 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 02:13:07 PM', 'MM/DD/YYYY HH:MI:SS AM'));


INSERT INTO CRM_AUDIT ( SERVICE_REQ_KEY_ID, BUSCOMP_NAM, FIELD_NAM, OPER_CD, OLD_VAL, NEW_VAL,
OPER_DT, FISCAL_YYYYWW_NUM, ROW_CREATE_GMT_DTTM, ROW_MODIFY_GMT_DTTM, ROW_CREATE_MST_DTTM,
ROW_MODIFY_MST_DTTM ) VALUES (
'1-40NRUH', 'SERVICEREQUEST', 'STATUS', 'MODIFY', 'OPEN', 'UNASSIGNED', TO_Date( '01/01/2006 04:44:54 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 200601, TO_Date( '02/20/2008 09:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 09:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '02/20/2008 02:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '02/20/2008 02:13:08 PM', 'MM/DD/YYYY HH:MI:SS AM'));

please help me on this.......

i am attaching the complete insert statements.
Please let me know if furthere details needed.
i am using oracle 9.2 version.


Thanks in advance
Prashanth
Re: SQL query for collection load [message #304013 is a reply to message #304012] Mon, 03 March 2008 13:40 Go to previous messageGo to next message
prashanth.spl
Messages: 4
Registered: March 2008
Junior Member
I tried to solve this and i got the answer some how....
is this is feasible solution??


SELECT
SERVICE_REQ_ID,
SERVICE_REQ_KEY_ID,
STATUS,
SUM(DAYS_INCL_WEEKENDS),
SUM(DAYS_EXCL_WEEKENDS)

FROM
(


Select
SERVICE_REQ_ID
,SERVICE_REQ_KEY_ID
,NEW_VAL As STATUS
,days AS days_incl_weekends
,
CASE
WHEN WORKDAYS = 0 THEN DAYS
WHEN BEG_DAY_OF_WEEK IN(1,7)
AND END_DAY_OF_WEEK IN (1,7) THEN WORKDAYS
WHEN BEG_DAY_OF_WEEK IN(1,7)
AND END_DAY_OF_WEEK NOT IN (1,7) THEN WORKDAYS-1+END_DAYS
WHEN BEG_DAY_OF_WEEK NOT IN(1,7)
AND END_DAY_OF_WEEK IN (1,7) THEN WORKDAYS-1+ START_DAYS
ELSE WORKDAYS-2+START_DAYS+END_DAYS
END As days_excl_weekends



From
(

Select
rs4.SERVICE_REQ_ID
,rs4.SERVICE_REQ_KEY_ID
,rs4.NEW_VAL
,rs4.BEG_DTTM, EDN_DTTM
,rs4.BEG_DT
,rs4.END_DT
,rs4.BEG_DAY_OF_WEEK
,rs4.END_DAY_OF_WEEK
,rs4.START_DAYS
,rs4.END_DAYS
,rs4.DAYS
,rs4.TOTAL_DAYS
,Sum(rs4.WORKDAYS) As WORKDAYS
,Sum(rs4.WEEKENDS) As WEEKENDS

From
(
Select
SERVICE_REQ_ID
,SERVICE_REQ_KEY_ID
,NEW_VAL
,BEG_DTTM
,EDN_DTTM
,BEG_DT
,END_DT
,BEG_DAY_OF_WEEK
,END_DAY_OF_WEEK
,START_DAYS
,END_DAYS
,DAYS
,TOTAL_DAYS
,Cast(COUNT(1) As decimal(12,5)) As WORKDAYS
,Cast(0 As decimal(12,5)) As WEEKENDS
From
(


Select
SR.SERVICE_REQ_ID
,x.SERVICE_REQ_KEY_ID
,x.NEW_VAL
,x.OPER_DT1 As BEG_DTTM
,x.OPER_DT2 As EDN_DTTM
,Cast(x.OPER_DT1 As date) As BEG_DT
,Cast(x.OPER_DT2 As date) As END_DT
,x.DAY1 As BEG_DAY_OF_WEEK
,x.DAY2 As END_DAY_OF_WEEK
, (CAST( EXTRACT(
HOUR
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second)
)
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
AS decimal(12,5) ) )/86400 As START_DAYS

, ( CAST(
EXTRACT(HOUR
FROM (x.OPER_DT2) )
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (x.OPER_DT2) )
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (x.OPER_DT2) )
AS decimal(12,5) )) /86400 AS END_DAYS,

((x.oper_dt2 - x.oper_dt1) DAY(4) TO SECOND) AS diff_time,
Cast(( (EXTRACT (DAY
FROM diff_time) * 24 * 60 * 60) +
EXTRACT (HOUR
FROM diff_time) * 60 * 60 +
EXTRACT (MINUTE
FROM diff_time) * 60 +
EXTRACT (SECOND
FROM diff_time)) /86400 As decimal(12,5)) AS DAYS

,Sum(x.DATE_C) As TOTAL_DAYS

From


(

Select
a.SERVICE_REQ_KEY_ID
,a.OLD_VAL
,a.NEW_VAL
,a.OPER_DT1
,b.OPER_DT2
,a.RNK1
,b.RNK2
,a.DAY1
,b.DAY2
,Cast(b.OPER_DT2 As timestamp(0))- Cast(a.OPER_DT1 As timestamp(0)) day(4) To second(0) As DATE_B
,Cast(Cast(b.OPER_DT2 As date)- Cast(a.OPER_DT1 As date) As integer) +1 As DATE_C
From
(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT1
,DAY1
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT1 ASC) AS RNK1

From

(
Select

SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT1
,DAY_OF_WEEK As DAY1
From
CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

UNION ALL

Select
SERVICE_REQ_KEY_ID,
'CREATE' AS OLD_VAL
,OLD_VAL As NEW_VAL
,create_dt AS OPER_dT1
,DAY_OF_WEEK As DAY1
From
CRM_RQST_DETAIL,
CRM_AUDIT corr_query1,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'
And
SERVICE_REQ_KEY_ID = service_req_key_id

And oper_dt In (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(corr_query1.SERVICE_REQ_KEY_ID)
And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify'
)

) zz

)a,

(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT2
,DAY2
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT2 ASC) AS RNK2

From

(
Select

SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT2
,DAY_OF_WEEK As DAY2

From
CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'
UNION ALL

Select
SERVICE_REQ_KEY_ID,
'CREATE' AS OLD_VAL
,OLD_VAL As NEW_VAL
,create_dt AS OPER_dT2
,DAY_OF_WEEK As DAY2

From
CRM_RQST_DETAIL,
CRM_AUDIT corr_query2,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'
And
SERVICE_REQ_KEY_ID = service_req_key_id
And oper_dt In (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(corr_query2.SERVICE_REQ_KEY_ID)

And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify'
)

) yy

)b
Where a. SERVICE_REQ_KEY_ID=b. SERVICE_REQ_KEY_ID
And
a.RNK1+1=b.RNK2
And a.new_val = b.old_val


) x , CRM_RQST_DETAIL SR

Where SERVICE_REQ_KEY_ID = SR.service_req_key_id
AND SR.CREATE_DT <> (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(x.SERVICE_REQ_KEY_ID)

And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify')
Group By 1,2,3,4,5,6,7,8,9,10


) y , sys_calendar.calendar

Where
calendar_date Between BEG_DT
And END_DT
And
day_of_week Not In (1, 7)
Group By 1,2,3,4,5,6,7,8,9,10,11,12,13



/* MAIN UNION************************************************************/
UNION


Select
SERVICE_REQ_ID
,SERVICE_REQ_KEY_ID
,NEW_VAL
,BEG_DTTM
,EDN_DTTM
,BEG_DT
,END_DT
,BEG_DAY_OF_WEEK
,END_DAY_OF_WEEK
,START_DAYS
,END_DAYS
,DAYS
,TOTAL_DAYS
,Cast(0 As decimal(12,5)) As WORKDAYS
,Cast(COUNT(2) As decimal(12,5)) As WEEKENDS
From
(


Select
SR.SERVICE_REQ_ID
,x.SERVICE_REQ_KEY_ID
,x.NEW_VAL
,x.OPER_DT1 As BEG_DTTM
,x.OPER_DT2 As EDN_DTTM
,Cast(x.OPER_DT1 As date) As BEG_DT
,Cast(x.OPER_DT2 As date) As END_DT
,x.DAY1 As BEG_DAY_OF_WEEK
,x.DAY2 As END_DAY_OF_WEEK
, (CAST( EXTRACT(
HOUR
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second)
)
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
AS decimal(12,5) ) )/86400 As START_DAYS

, ( CAST(
EXTRACT(HOUR
FROM (x.OPER_DT2) )
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (x.OPER_DT2) )
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (x.OPER_DT2) )
AS decimal(12,5) )) /86400 AS END_DAYS,


((x.oper_dt2 - x.oper_dt1) DAY(4) TO SECOND) AS diff_time,
Cast(( (EXTRACT (DAY
FROM diff_time) * 24 * 60 * 60) +
EXTRACT (HOUR
FROM diff_time) * 60 * 60 +
EXTRACT (MINUTE
FROM diff_time) * 60 +
EXTRACT (SECOND
FROM diff_time)) /86400 As decimal(12,5)) AS DAYS

,Sum(x.DATE_C) As TOTAL_DAYS

From


(

Select
a.SERVICE_REQ_KEY_ID
,a.OLD_VAL
,a.NEW_VAL
,a.OPER_DT1
,b.OPER_DT2
,a.RNK1
,b.RNK2
,a.DAY1
,b.DAY2
,Cast(b.OPER_DT2 As timestamp(0))- Cast(a.OPER_DT1 As timestamp(0)) day(4) To second(0) As DATE_B
,Cast(Cast(b.OPER_DT2 As date)- Cast(a.OPER_DT1 As date) As integer) +1 As DATE_C
From
(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT1
,DAY1
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT1 ASC) AS RNK1
From

(
Select

SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT1
,DAY_OF_WEEK As DAY1

From
CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'


UNION ALL

Select
SERVICE_REQ_KEY_ID,
'CREATE' AS OLD_VAL
,OLD_VAL As NEW_VAL
,create_dt AS OPER_DT1
,DAY_OF_WEEK As DAY1
From
CRM_RQST_DETAIL,
CRM_AUDIT corr_query3,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

And
SERVICE_REQ_KEY_ID = service_req_key_id
And oper_dt In (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(corr_query3.SERVICE_REQ_KEY_ID)

And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify'
)



) zz )a,

(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT2
,DAY2
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT2 ASC) AS RNK2
From

(
Select

SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT2
,DAY_OF_WEEK As DAY2

From
CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'


UNION ALL

Select
SERVICE_REQ_KEY_ID,
'CREATE' AS OLD_VAL
,OLD_VAL As NEW_VAL
,create_dt AS OPER_dT2
,DAY_OF_WEEK As DAY2

From
CRM_RQST_DETAIL,
CRM_AUDIT corr_query4,

sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

And
SERVICE_REQ_KEY_ID = service_req_key_id
And oper_dt In (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(corr_query4.SERVICE_REQ_KEY_ID)

And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify'
)




) yy
)b
Where a. SERVICE_REQ_KEY_ID=b. SERVICE_REQ_KEY_ID
And
a.RNK1+1=b.RNK2
And a.new_val = b.old_val


) x , CRM_RQST_DETAIL SR

Where SERVICE_REQ_KEY_ID = SR.service_req_key_id
AND SR.CREATE_DT <> (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(x.SERVICE_REQ_KEY_ID)

And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify')
Group By 1,2,3,4,5,6,7,8,9,10


) y , sys_calendar.calendar

Where
calendar_date Between BEG_DT
And END_DT
And
day_of_week In (1, 7)
Group By 1,2,3,4,5,6,7,8,9,10,11,12,13

) rs4

Group By 1,2,3,4,5,6,7,8,9,10,11,12,13

) rs3


) LAST_SEL
Group By 1,2,3
--Order By 2

---********************************************************************************************************************************** ******************
UNION ALL

--*****************************************************************************
SELECT
SERVICE_REQ_ID,
SERVICE_REQ_KEY_ID,
STATUS,
SUM(DAYS_INCL_WEEKENDS),
SUM(DAYS_EXCL_WEEKENDS)

FROM
(

Select

rs3.SERVICE_REQ_ID
,rs3.SERVICE_REQ_KEY_ID
,rs3.NEW_VAL As STATUS
,rs3.days AS days_incl_weekends
,
CASE
WHEN rs3.WORKDAYS = 0 THEN rs3.DAYS
WHEN rs3.BEG_DAY_OF_WEEK IN(1,7)
AND rs3.END_DAY_OF_WEEK IN (1,7) THEN rs3.WORKDAYS
WHEN rs3.BEG_DAY_OF_WEEK IN(1,7)
AND rs3.END_DAY_OF_WEEK NOT IN (1,7) THEN rs3.WORKDAYS-1+rs3.END_DAYS
WHEN rs3.BEG_DAY_OF_WEEK NOT IN(1,7)
AND rs3.END_DAY_OF_WEEK IN (1,7) THEN rs3.WORKDAYS-1+rs3.START_DAYS
ELSE rs3.WORKDAYS-2+rs3.START_DAYS+rs3.END_DAYS
END As days_excl_weekends


From
(
Select
rs4.SERVICE_REQ_ID
,rs4.SERVICE_REQ_KEY_ID
,rs4.NEW_VAL
,rs4.BEG_DTTM
,rs4.EDN_DTTM
,rs4.BEG_DT
,rs4.END_DT
,rs4.BEG_DAY_OF_WEEK
,rs4.END_DAY_OF_WEEK
,rs4.START_DAYS
,rs4.END_DAYS
,rs4.DAYS
,rs4.TOTAL_DAYS
,Sum(rs4.WORKDAYS) As WORKDAYS
,Sum(rs4.WEEKENDS) As WEEKENDS

From
(
Select
SERVICE_REQ_ID
,SERVICE_REQ_KEY_ID
,NEW_VAL
,BEG_DTTM
,EDN_DTTM
,BEG_DT
,END_DT
,BEG_DAY_OF_WEEK
,END_DAY_OF_WEEK
,START_DAYS
,END_DAYS
,DAYS
,TOTAL_DAYS
,Cast(COUNT(1) As decimal(12,5)) As WORKDAYS
,Cast(0 As decimal(12,5)) As WEEKENDS
From
(
Select
SR.SERVICE_REQ_ID
,x.SERVICE_REQ_KEY_ID
,x.NEW_VAL
,x.OPER_DT1 As BEG_DTTM
,x.OPER_DT2 As EDN_DTTM
,Cast(x.OPER_DT1 As date) As BEG_DT
,Cast(x.OPER_DT2 As date) As END_DT
,x.DAY1 As BEG_DAY_OF_WEEK
,x.DAY2 As END_DAY_OF_WEEK
, (CAST( EXTRACT(
HOUR
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second)
)
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
AS decimal(12,5) ) )/86400 As START_DAYS

, ( CAST(
EXTRACT(HOUR
FROM (x.OPER_DT2) )
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (x.OPER_DT2) )
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (x.OPER_DT2) )
AS decimal(12,5) )) /86400 AS END_DAYS,


((x.oper_dt2 - x.oper_dt1) DAY(4) TO SECOND) AS diff_time,
Cast(( (EXTRACT (DAY
FROM diff_time) * 24 * 60 * 60) +
EXTRACT (HOUR
FROM diff_time) * 60 * 60 +
EXTRACT (MINUTE
FROM diff_time) * 60 +
EXTRACT (SECOND
FROM diff_time)) /86400 As decimal(12,5)) AS DAYS

,Sum(x.DATE_C) As TOTAL_DAYS




From
(
Select
a.SERVICE_REQ_KEY_ID
,a.OLD_VAL
,a.NEW_VAL
,a.OPER_DT1
,b.OPER_DT2
,a.RNK1
,b.RNK2
,a.DAY1
,b.DAY2
,Cast(b.OPER_DT2 As timestamp(0))- Cast(a.OPER_DT1 As timestamp(0)) day(4) To second(0) As DATE_B
,Cast(Cast(b.OPER_DT2 As date)- Cast(a.OPER_DT1 As date) As integer) +1 As DATE_C
From
(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT1
,DAY_OF_WEEK As DAY1
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT1 ASC) AS RNK1

From
CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'
) a ,

(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT2
,DAY_OF_WEEK As DAY2
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT2 ASC) AS RNK2

From CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

)b

Where
a. SERVICE_REQ_KEY_ID=b. SERVICE_REQ_KEY_ID
And
a.RNK1+1=b.RNK2
And a.new_val = b.old_val

) x, CRM_RQST_DETAIL SR

Where SERVICE_REQ_KEY_ID = SR.SERVICE_REQ_KEY_ID
AND SR.CREATE_DT = (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(x.SERVICE_REQ_KEY_ID)

And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify')


Group By 1,2,3,4,5,6,7,8,9,10


) y ,sys_calendar.calendar

Where
calendar_date Between BEG_DT
And END_DT
And
day_of_week Not In (1, 7)
Group By 1,2,3,4,5,6,7,8,9,10,11,12 ,13

UNION

Select
SERVICE_REQ_ID
,SERVICE_REQ_KEY_ID
,NEW_VAL
,BEG_DTTM
,EDN_DTTM
,BEG_DT
,END_DT
,BEG_DAY_OF_WEEK
,END_DAY_OF_WEEK
,START_DAYS
,END_DAYS
,DAYS
,TOTAL_DAYS
,Cast(0 As decimal(12,5)) As WORKDAYS
,Cast(COUNT(2) As decimal(12,5)) As WEEKENDS
From
(
Select
SR.SERVICE_REQ_ID
,x.SERVICE_REQ_KEY_ID
,x.NEW_VAL
,x.OPER_DT1 As BEG_DTTM
,x.OPER_DT2 As EDN_DTTM
,Cast(x.OPER_DT1 As date) As BEG_DT
,Cast(x.OPER_DT2 As date) As END_DT
,x.DAY1 As BEG_DAY_OF_WEEK
,x.DAY2 As END_DAY_OF_WEEK
, (CAST( EXTRACT(
HOUR
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second)
)
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (Cast (Cast (x.OPER_DT1 + interval '01' day As date) As timestamp(0) ) - x.OPER_DT1 hour To second ))
AS decimal(12,5) ) )/86400 As START_DAYS

, ( CAST(
EXTRACT(HOUR
FROM (x.OPER_DT2) )
* 3600 AS decimal(12,5) )
+
CAST(
EXTRACT(MINUTE
FROM (x.OPER_DT2) )
* 60 AS decimal(12,5) )
+
CAST(
EXTRACT(SECOND
FROM (x.OPER_DT2) )
AS decimal(12,5) )) /86400 AS END_DAYS,


((x.oper_dt2 - x.oper_dt1) DAY(4) TO SECOND) AS diff_time,
Cast(( (EXTRACT (DAY
FROM diff_time) * 24 * 60 * 60) +
EXTRACT (HOUR
FROM diff_time) * 60 * 60 +
EXTRACT (MINUTE
FROM diff_time) * 60 +
EXTRACT (SECOND
FROM diff_time)) /86400 As decimal(12,5)) AS DAYS

,Sum(x.DATE_C) As TOTAL_DAYS
From
(
Select
a.SERVICE_REQ_KEY_ID
,a.OLD_VAL
,a.NEW_VAL
,a.OPER_DT1
,b.OPER_DT2
,a.RNK1
,b.RNK2
,a.DAY1
,b.DAY2
,Cast(b.OPER_DT2 As timestamp(0))- Cast(a.OPER_DT1 As timestamp(0)) day(4) To second(0) As DATE_B
,Cast(Cast(b.OPER_DT2 As date)- Cast(a.OPER_DT1 As date) As integer) +1 As DATE_C

From
(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT1
,day_of_week As DAY1
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT1 ASC) AS RNK1
From CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

) a ,

(
Select
SERVICE_REQ_KEY_ID
,OLD_VAL
,NEW_VAL
,OPER_DT As OPER_DT2
,day_of_week As DAY2
,ROW_NUMBER( ) OVER (PARTITION BY SERVICE_REQ_KEY_ID
ORDER BY OPER_DT2 ASC) AS RNK2

From CRM_AUDIT,
sys_calendar.calendar
Where
calendar_date = Cast(OPER_DT As date)
And
field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

) b

Where
a. SERVICE_REQ_KEY_ID=b. SERVICE_REQ_KEY_ID
And

a.RNK1+1=b.RNK2
And a.new_val = b.old_val

) x , CRM_RQST_DETAIL SR

Where SERVICE_REQ_KEY_ID = SR.service_req_key_id
AND SR.CREATE_DT = (
Select Min(oper_dt)
From CRM_AUDIT
Where SERVICE_REQ_KEY_ID In(x.SERVICE_REQ_KEY_ID)
And field_nam = 'status'
And buscomp_nam = 'service request'
And oper_cd = 'modify')


Group By 1,2,3,4,5,6,7,8,9,10

) y , sys_calendar.calendar

Where
calendar_date Between BEG_DT
And END_DT
And
day_of_week In (1, 7)
Group By 1,2,3,4,5,6,7,8,9,10,11,12,13
) rs4

Group By 1,2,3,4,5,6,7,8,9,10,11,12,13

) rs3

) last_sel
Group By 1,2,3
Order By 2
Re: SQL query for collection load [message #304014 is a reply to message #304012] Mon, 03 March 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain why you get these values.
How come [7.00229][5.00229],[6.03184][4.03184],[1.16519][0.36303]?

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: SQL query for collection load [message #304016 is a reply to message #304013] Mon, 03 March 2008 14:09 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
prashanth.spl wrote on Mon, 03 March 2008 19:40
I tried to solve this and i got the answer some how....
is this is feasible solution??


For the sanity of the individual that comes after you and has to maintain that code I hope not. Shocked

Seriously though, that statement runs to over 900 lines of code, and without me having any idea what it does (or is meant to do), I'm gonna guess that there has to be a way to solve your problem without the use of a single 900 line statement. Ouch!

Think of poor chappie who's next to look at the code.

Dagnabit, think of the children!!

moving along .... *whistle*

Cheers,
Michael

PS : perhaps some of the inline views can be turned into proper views?
Re: SQL query for collection load [message #304018 is a reply to message #304014] Mon, 03 March 2008 14:14 Go to previous messageGo to next message
prashanth.spl
Messages: 4
Registered: March 2008
Junior Member
SERVICE_REQ_KEY_ID BUSCOMP_NAM FIELD_NAM OPER_CD OLD_VAL NEW_VAL OPER_DT FISCAL_YYYYWW_NUM
1-40NRUH SERVICE REQUEST STATUS MODIFY OPEN UNASSIGNED 2006-01-01 04:44:54 200,601
1-40NRUH SERVICE REQUEST CONTACT LASTNAME MODIFY NULL 2006-01-01 04:44:54 200,601
1-40NRUH SERVICE REQUEST ID NEW RECORD NULL 1-40NRUH 2006-01-01 04:44:54 200,601
1-40NRUH SERVICE REQUEST OWNERGROUP MODIFY NULL TIC-INBOX 2006-01-01 04:45:33 200,601
1-40NRUH SERVICE REQUEST OWNER MODIFY NULL RE006C 2006-01-02 08:42:46 200,601
1-40NRUH SERVICE REQUEST STATUS MODIFY UNASSIGNED OPEN 2006-01-02 08:42:46 200,601
1-40NRUH SERVICE REQUEST STATUS MODIFY OPEN CUSTOMER REPLY PERIOD 2006-01-02 09:28:37 200,601
1-40NRUH SERVICE REQUEST STATUS MODIFY CUSTOMER REPLY PERIOD CLOSED 2006-01-09 09:31:55 200,602

1. Need to get the create_dt from CRM_RQST_DETAIL table and compare the first record of same Service request in CRM_AUDIT.

2. Then get the diffrenece of the create_dt and oper_dt and calcualte the diffrence.

3. go on calcualting... finally if same status comes 2 or more times then sum those values..

in the above example create_Dt is: '12/26/2005 04:44:54' and oper_Dt of 1st record is ;2006-01-01 04:44:54' need to calcualte the diffrence between these two timestamps. only need to consider those values in CRM_AUDIT which satisfy the following conditions:

where field_nam = 'STATUS'
And
oper_cd = 'MODIFY'
And
buscomp_nam = 'SERVICE REQUEST'

if we applied to our example
1-40NRUH SERVICE REQUEST STATUS MODIFY OPEN UNASSIGNED 2006-01-01 04:44:54 200,601
1-40NRUH SERVICE REQUEST STATUS MODIFY UNASSIGNED OPEN 2006-01-02 08:42:46 200,601
1-40NRUH SERVICE REQUEST STATUS MODIFY OPEN CUSTOMER REPLY PERIOD 2006-01-02 09:28:37 200,601
1-40NRUH SERVICE REQUEST STATUS MODIFY CUSTOMER REPLY PERIOD CLOSED 2006-01-09 09:31:55 200,602

we will get the above result.
and based on this we have to calculate the time diffrences...in floating point days.
in the above example the open status transit 2 times. so we add the 2 timings and shown as single.

The new_value of current row = old_val of next record.(we have to arrenge in the order by using oper_dt).
Re: SQL query for collection load [message #304021 is a reply to message #304018] Mon, 03 March 2008 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use LAG function.

Regards
Michel
Re: SQL query for collection load [message #304061 is a reply to message #304021] Mon, 03 March 2008 22:52 Go to previous messageGo to next message
prashanth.spl
Messages: 4
Registered: March 2008
Junior Member
But first time i have to calcualte the diffrence by taking value create_dt from CRM_RQST_DETAIL and oper_Dt from CRM_AUDIT.

how can i do this??
Re: SQL query for collection load [message #304102 is a reply to message #304061] Tue, 04 March 2008 01:08 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nvl(lag... ,value from other table)

Regards
Michel
Previous Topic: convert clob to string
Next Topic: sql query for insertion
Goto Forum:
  


Current Time: Wed Dec 07 10:32:02 CST 2016

Total time taken to generate the page: 0.09209 seconds