August 18, 2008 at 11:06 am
I'm pulling my hair out with this one.....
I have a sql statement that I'm trying to convert from an Oracle 10g db that uses the PERCENTILE_CONT function. I've read about the function over and over and over and over in the various Oracle forums, etc. I have a solution that worked (so I thought), but suddenly, 2 months later, it's not doing what it should be doing now that we have data volume in our database. I've found a few examples on the web that I'm struggling to understand.
Have any of you encountered this nightmare?
If so, would you be able to point me in the direction of finding some useful documentation for such conversions?
Thanks in advance,
Dave :w00t:
August 18, 2008 at 12:26 pm
Never having had the pleasure of working with Oracle, could explain what PERCENTILE_CONT does? Then even a neophyte like myself might be able to help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 12:43 pm
Below is the table create script and insert stmt's. that the query is based on.
Also, the oracle query and result set are below.
--ORACLE QUERY
SELECT DISTINCT CD_RSPNS, TX_RSPNS,
sum(DECODE(FIRST_RPT_FOR_INVS,'Yes',1,0)) OVER (PARTITION BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS ORDER BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS )as NUM_COMPLTD_ASSESS,
SUM(DECODE(FIRST_FACE_TO_FACE_ATTEMPT, NULL,0,1)) OVER (PARTITION BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS ORDER BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS )AS ATTEMPTED_OR_ACTUAL_CONTACTS,
SUM(DECODE(FIRST_FACE_TO_FACE_ACTUAL, NULL,0,1)) OVER (PARTITION BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS )AS ACTUAL_CONTACTS,
ROUND(percentile_cont(0.5) within group (order by (FIRST_FACE_TO_FACE_ATTEMPT - DT_RFRD) desc ) OVER (PARTITION BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS),2)AS MED_DAYS_TO_ATTEMPTED_CONTACT,
ROUND(percentile_cont(0.5) within group (order by (FIRST_FACE_TO_FACE_ACTUAL - DT_RFRD) desc ) OVER (PARTITION BY WORKER_CNTY, WORKER_SITE, CD_RSPNS, TX_RSPNS ),2)AS MED_DAYS_TO_INITIAL
FROM TEMP_SM06A05_DETAIL_ALL;
--ORACLE RESULT SET
CD_RSPNS TX_RSPNS NUM_COMPLTD_ASSESS ATTEMPTED_OR_ACTUAL_CONTACTS ACTUAL_CONTACTS MED_DAYS_TO_ATTEMPTED_CONTACT MED_DAYS_TO_INITIAL
---------- ---------------------------------------- ------------------ ---------------------------- --------------- ----------------------------- -------------------
1 Same Day 7 7 7 -10723.17 -10723.17
2 Within 24 hours 7 7 7 -10706.53 -10706.53
4 Within 5 business days 3 3 3 -10701.44 -10701.44
3 Within 48 hours 3 3 3 -10702.42 -10702.42
--SQL TABLE CREATE AND INSERT STMT'S.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEMP_SM06A05_DETAIL_ALL](
[RUN_DATE] [datetime] NOT NULL,
[RPT_BGN_DATE] [datetime] NOT NULL,
[RPT_END_DATE] [datetime] NOT NULL,
[WORKER_CNTY] [varchar](40) NULL,
[WORKER_SITE] [varchar](40) NULL,
[WORKER_ID_PRSN] [numeric](38, 0) NOT NULL,
[WORKER_NM_LST] [varchar](40) NULL,
[WORKER_NM_FRST] [varchar](40) NULL,
[tx_asgn_type] [varchar](40) NOT NULL,
[tx_asgn_role] [varchar](40) NOT NULL,
[SUPERVISOR_ID_PRSN] [numeric](38, 0) NOT NULL,
[SUPERVISOR_NM_LST] [varchar](40) NULL,
[SUPERVISOR_NM_FRST] [varchar](40) NULL,
[ID_CASE] [numeric](38, 0) NOT NULL,
[CASE_NAME] [varchar](82) NULL,
[tx_case_type] [varchar](40) NULL,
[ID_INVS] [numeric](10, 0) NULL,
[FL_INDEPENDENT_INV] [varchar](5) NULL,
[IA_SECONDARY] [varchar](3) NOT NULL,
[ID_CPS] [numeric](10, 0) NOT NULL,
[CPS_CNTY] [varchar](40) NULL,
[FIRST_RPT_FOR_INVS] [varchar](3) NOT NULL,
[CD_RSPNS] [numeric](5, 0) NOT NULL,
[TX_RSPNS] [varchar](40) NULL,
[TS_INVS_BGN] [datetime] NOT NULL,
[DT_RFRD] [datetime] NULL,
[TS_CPS_ACPT] [datetime] NULL,
[TX_SCREEN_IN] [varchar](40) NULL,
[CPS_FIRST_ASSIGN_TO_IA] [datetime] NULL,
[FIRST_FACE_TO_FACE_ATTEMPT] [datetime] NULL,
[FIRST_FACE_TO_FACE_ACTUAL] [datetime] NULL,
[APPROVAL_OF_IA] [datetime] NULL,
[CD_DISP] [numeric](38, 0) NULL,
[TX_DISP] [varchar](40) NULL,
[CD_ASMNT_RESULT] [char](1) NOT NULL,
[TX_ASMNT_RESULT] [varchar](26) NOT NULL,
[NUM_PS_RPTS_FOR_INVS] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
go
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000194, 'RoseTwo', 'J', 'Courtesy Ongoing', 'Primary', 10000192, 'SICStA', 'Director', 10000028, 'Mendes, Carrie', 'CPS Family', 10000020, 'No', 'NO',10000079, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-05-28 09:22:00.000', '2008-05-28 09:22:00.000', '2008-05-28 09:22:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000194, 'RoseTwo', 'J', 'Courtesy Ongoing', 'Primary', 10000192, 'SICStA', 'Director', 10000031, 'Fernandez, Juanita', 'CPS Family', 10000021, 'No', 'NO',10000082, 'San Juan', 'Yes', 3, 'Within 48 hours', '2008-05-28 01:15:00.000', '2008-05-28 01:15:00.000', '2008-05-28 01:15:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000201, 'ParkOne', 'L', 'CPS Ongoing', 'Primary', 10000200, 'ParkTwo', 'L', 10000029, 'Orange, Mary', 'CPS Family', 10000022, 'No', 'NO',10000080, 'San Juan', 'Yes', 4, 'Within 5 business days', '2008-05-28 10:31:00.000', '2008-05-28 10:31:00.000', '2008-05-28 10:31:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-06-24 11:04:04.737', 8, 'Case transfer to ongoing CPS serv.: Vol.', 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000194, 'RoseTwo', 'J', 'Intake', 'Supervisor', 10000192, 'SICStA', 'Director', 10000034, 'Honto, Romina', 'CPS Family', 10000023, 'No', 'NO',10000084, 'San Juan', 'Yes', 3, 'Within 48 hours', '2008-05-29 10:11:00.000', '2008-05-29 10:11:00.000', '2008-05-29 10:11:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10170 , 'Corn' , 'Conn', 'Intake', 'Supervisor', 10003, 'Tatterson', 'Bryan', 10000025, 'Green, Mary', 'ICPC', 10000024, 'YES','NO',10000073, 'San Juan', 'Yes', 4, 'Within 5 business days', '2008-05-22 04:16:00.000', '2008-05-22 04:16:00.000', '2008-05-22 04:16:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', 10, 'Case transfer for inhome safety services', 'U', 'Unsubstantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000196, 'HarmOne', 'K', 'CPS Initial Assessment', 'Primary', 10000195, 'HarmTwo', 'K', 10000043, 'Sanchez, Sandra', 'CPS Family', 10000025, 'No', 'NO',10000095, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-06-02 12:49:00.000', '2008-06-02 12:49:00.000', '2008-06-02 12:49:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000201, 'ParkOne', 'L', 'CPS Ongoing', 'Primary', 10000200, 'ParkTwo', 'L', 10000037, 'Yellow, Mary', 'CPS Family', 10000026, 'No', 'NO',10000087, 'San Juan', 'Yes', 4, 'Within 5 business days ', '2008-05-29 02:42:00.000', '2008-05-29 02:42:00.000', '2008-05-29 03:22:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'N', 'Not able to locate sources', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000194, 'RoseTwo', 'J', 'Support Services', 'Primary', 10000192, 'SICStA', 'Director', 10000044, 'Garcia, Carol', 'CPS Family', 10000028, 'No', 'NO',10000096, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-06-02 01:48:00.000', '2008-06-02 01:48:00.000', '2008-06-02 01:48:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000193, 'RoseOne', 'J', 'Courtesy Ongoing', 'Primary', 10000194, 'RoseTwo', 'J', 10000090, 'OutlinerMom, Momout', 'Child Welfare', 10000029, 'No', 'NO',10000113, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-06-17 10:34:00.000', '2008-06-17 10:34:00.000', '2008-06-17 10:34:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000196, 'HarmOne', 'K', 'CPS Initial Assessment', 'Primary', 10000195, 'HarmTwo', 'K', 10000051, 'Independent, Sara', 'CPS Family', 10000030, 'No', 'NO',10000101, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-06-03 03:54:00.000', '2008-06-03 03:54:00.000', '2008-06-03 03:54:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000196, 'HarmOne', 'K', 'CPS Initial Assessment', 'Primary', 10000195, 'HarmTwo', 'K', 10000042, 'Lopez, Jose', 'CPS Family', 10000031, 'No', 'NO',10000094, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-06-01 08:07:00.000', '2008-06-01 08:07:00.000', '2008-06-01 08:07:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'N', 'Not able to locate sources', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000196, 'HarmOne', 'K', 'Reopen/Case Closed in Error', 'Primary', 10000195, 'HarmTwo', 'K', 10000094, 'cow, sue', 'CPS Family', 10000032, 'No', 'NO',10000115, 'San Juan', 'Yes', 1, 'Same Day', '2008-06-19 03:58:00.000', '2008-06-19 03:54:00.000', '2008-06-19 15:54:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 2)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000196, 'HarmOne', 'K', 'Reopen/Case Closed in Error', 'Primary', 10000195, 'HarmTwo', 'K', 10000094, 'cow, sue', 'CPS Family', 10000032, 'No', 'NO',10000116, 'San Juan', 'Yes', 1, 'Same Day', '2008-06-19 03:58:00.000', '2008-06-19 03:58:00.000', '2008-06-19 03:58:00.000', 'Screen In - CA/N Non-Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 2)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000206, 'McclTwo', 'P', 'CPS Initial Assessment', 'Primary', 10000192, 'SICStA', 'Director', 10000101, 'McCloskey, Pamela', 'CPS Family', 10000035, 'No', 'NO',10000124, 'San Juan', 'Yes', 2, 'Within 24 hours', '2008-07-05 12:15:00.000', '2008-07-05 12:15:00.000', '2008-07-05 12:15:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'P', 'Pending', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000196, 'HarmOne', 'K', 'CPS Initial Assessment', 'Primary', 10000195, 'HarmTwo', 'K', 10000052, 'Adoption, Sandra', 'CPS Family', 10000036, 'No', 'NO',10000102, 'San Juan', 'Yes', 1, 'Same Day', '2008-06-03 04:12:00.000', '2008-06-03 04:12:00.000', '2008-06-03 04:12:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 9221183 , 'Iowa', 'Supervisor', 'CPS Initial Assessment', 'Supervisor', 10003, 'Tatterson', 'Bryan', 10000041, 'Bluecase, Marci', 'CPS Family', 10000037, 'No', 'NO',10000093, 'San Juan', 'Yes', 1, 'Same Day', '2008-05-31 11:23:00.000', '2008-05-31 11:23:00.000', '2008-05-31 12:26:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000207, 'McclOne', 'P', 'CPS Initial Assessment', 'Primary', 10000206, 'McclTwo', 'P', 10000103, 'Garcia, Maria', 'CPS Family', 10000039, 'No', 'NO',10000125, 'San Juan', 'Yes', 3, 'Within 48 hours', '2008-07-24 10:31:00.000', '2008-07-24 10:31:00.000', '2008-07-24 10:31:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000207, 'McclOne', 'P', 'CPS Initial Assessment', 'Primary', 10000206, 'McclTwo', 'P', 10000104, 'Garcia, Alexandria', 'CPS Family', 10000040, 'No', 'NO',10000126, 'San Juan', 'Yes', 1, 'Same Day', '2008-07-24 12:39:00.000', '2008-07-24 12:39:00.000', '2008-07-24 12:39:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000207, 'McclOne', 'P', 'CPS Initial Assessment', 'Primary', 10000206, 'McclTwo', 'P', 10000106, 'Wilson, Mary', 'CPS Family', 10000041, 'No', 'NO',10000128, 'San Juan', 'Yes', 1, 'Same Day', '2008-07-24 15:50:00.000', '2008-07-24 15:50:00.000', '2008-07-24 15:50:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', NULL, NULL, 'S', 'Substantiated', 1)
insert TEMP_SM06A05_DETAIL_ALL values ('2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', '2008-08-15 11:34:08.640', 'San Juan', 'San Juan', 10000207, 'McclOne', 'P', 'CPS Initial Assessment', 'Primary', 10000206, 'McclTwo', 'P', 10000107, 'Davis, Henry', 'CPS Family', 10000042, 'No', 'NO',10000129, 'San Juan', 'Yes', 1, 'Same Day', '2008-07-24 16:24:00.000', '2008-07-24 16:24:00.000', '2008-07-24 16:24:00.000', 'Screen In - CA/N Primary Caregiver', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '1979-02-09 00:00:00.000', '2008-08-15 10:14:49.640', 8, 'Case transfer to ongoing CPS serv.: Vol.', 'P', 'Pending', 1)
Any and all help would be greatly appreciated.
Thanks again,
Dave
August 18, 2008 at 12:45 pm
Yeah, I guess it would make sense to include some links to the PERCENTILE_CONT help doc's. Sorry about that!!
http://www.java2s.com/Tutorial/Oracle/0320__Analytical-Functions/0420__PERCENTILE_CONT.htm
http://www.acs.ilstu.edu/docs/Oracle/server.101/b10759/functions100.htm
Thanks again,
Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply