Home » SQL & PL/SQL » SQL & PL/SQL » Need a query to get the ouput in an expected format
| Need a query to get the ouput in an expected format [message #640510] |
Wed, 29 July 2015 21:26  |
 |
nyaduguru
Messages: 1
Registered: July 2015
Location: devon,pa
|
Junior Member |
|
|
create table sample(USUBJID varchar2(20),LBDTC varchar2(20),VISIT varchar2(50),VISITNUM number(4));
insert into sample values('114-001','2014-07-10T09:30','Screening',1);
insert into sample values('114-001','2014-07-24T08:20','Unscheduled',9999);
insert into sample values('114-001','2014-07-24T09:10','Unscheduled',9999);
insert into sample values('114-001','2014-07-29T09:10','Unscheduled',9999);
insert into sample values('114-001','2014-08-07T08:16','Cycle 1 Day 1',101);
insert into sample values('114-001','2014-08-14T08:25','Cycle 1 Day 8',108);
insert into sample values('114-001','2014-08-28T08:42','Cycle 2 Day 1',201);
insert into sample values('114-001','2014-09-11T08:12','Cycle 2 Day 15',215);
insert into sample values('114-001','2014-09-25T08:34','Cycle 3 Day 1',301);
insert into sample values('114-001','2014-10-12T06:04','Unscheduled',9999);
insert into sample values('114-001','2014-10-13T05:37','Unscheduled',9999);
insert into sample values('114-001','2014-10-20T06:01','Unscheduled',9999);
insert into sample values('114-001','2015-01-15T08:55','Cycle 7 Day 1',701);
select * from sample;
The above is the data I have in SAMPLE table.
I need a query to get the output as follows. I have also atatched the file with the expected output.Any help is greatly appreciated.
USUBJID LBDTC VISIT VISITNUM
114-001 2014-07-10T09:30 Screening 1
114-001 2014-07-24T08:20 Screening Unscheduled 1 1.1
114-001 2014-07-24T09:10 Screening Unscheduled 1 1.1
114-001 2014-07-29T09:10 Screening Unscheduled 2 1.2
114-001 2014-08-07T08:16 Cycle 1 Day 1 101
114-001 2014-08-14T08:25 Cycle 1 Day 8 108
114-001 2014-08-28T08:42 Cycle 2 Day 1 201
114-001 2014-09-11T08:12 Cycle 2 Day 15 215
114-001 2014-09-25T08:34 Cycle 3 Day 1 301
114-001 2014-10-12T06:04 Cycle 3 Day 1 Unscheduled 1 301.1
114-001 2014-10-13T05:37 Cycle 3 Day 1 Unscheduled 2 301.2
114-001 2014-10-20T06:01 Cycle 3 Day 1 Unscheduled 3 301.3
114-001 2015-01-15T08:55 Cycle 7 Day 1 701
Thanks
Lalit : Added code tags. Please do it yourself in future.
-
Attachment: output.PNG
(Size: 23.54KB, Downloaded 940 times)
[Updated on: Thu, 30 July 2015 00:20] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Need a query to get the ouput in an expected format [message #640514 is a reply to message #640510] |
Thu, 30 July 2015 01:05  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Something like:
SQL> col original_visit format a20
SQL> col new_visit format a30
SQL> with
2 data as (
3 select s.*,
4 row_number() over (partition by USUBJID order by LBDTC) rn
5 from sample s
6 )
7 select LBDTC, visit original_visit,
8 case
9 when visit = 'Unscheduled'
10 then last_value(decode(visit, 'Unscheduled','', visit) ignore nulls)
11 over (partition by USUBJID order by LBDTC)
12 || ' ' || visit || ' ' ||
13 to_char(rn
14 - last_value(decode(visit, 'Unscheduled','', rn) ignore nulls)
15 over (partition by USUBJID order by LBDTC))
16 else visit
17 end new_visit,
18 visitnum,
19 case
20 when visit = 'Unscheduled'
21 then to_number(
22 to_char(last_value(decode(visit, 'Unscheduled','', visitnum) ignore nulls)
23 over (partition by USUBJID order by LBDTC))
24 || '.' ||
25 to_char(rn
26 - last_value(decode(visit, 'Unscheduled','', rn) ignore nulls)
27 over (partition by USUBJID order by LBDTC)))
28 else visitnum
29 end new_visitnum
30 from data
31 order by USUBJID, to_date(LBDTC,'YYYY-MM-DD"T"HH24:MI')
32 /
LBDTC ORIGINAL_VISIT NEW_VISIT VISITNUM NEW_VISITNUM
-------------------- -------------------- ------------------------------ ---------- ------------
2014-07-10T09:30 Screening Screening 1 1
2014-07-24T08:20 Unscheduled Screening Unscheduled 1 9999 1.1
2014-07-24T09:10 Unscheduled Screening Unscheduled 2 9999 1.2
2014-07-29T09:10 Unscheduled Screening Unscheduled 3 9999 1.3
2014-08-07T08:16 Cycle 1 Day 1 Cycle 1 Day 1 101 101
2014-08-14T08:25 Cycle 1 Day 8 Cycle 1 Day 8 108 108
2014-08-28T08:42 Cycle 2 Day 1 Cycle 2 Day 1 201 201
2014-09-11T08:12 Cycle 2 Day 15 Cycle 2 Day 15 215 215
2014-09-25T08:34 Cycle 3 Day 1 Cycle 3 Day 1 301 301
2014-10-12T06:04 Unscheduled Cycle 3 Day 1 Unscheduled 1 9999 301.1
2014-10-13T05:37 Unscheduled Cycle 3 Day 1 Unscheduled 2 9999 301.2
2014-10-20T06:01 Unscheduled Cycle 3 Day 1 Unscheduled 3 9999 301.3
2015-01-15T08:55 Cycle 7 Day 1 Cycle 7 Day 1 701 701
(Assuming that:114-001 2014-07-24T08:20 Screening Unscheduled 1 1.1
114-001 2014-07-24T09:10 Screening Unscheduled 1 1.1
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 27 21:26:54 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
