# Ranking query

**Tiny Penguin**Oct 28, 2011 11:35 PM

Hi All,

Could anyone help me out please?

I need to rank/order a set of data by date and period type (blocktype) for groups of worker records. Where the blocktype is 'Ind(ividual)', I need to increment the rank by 1. Where the type is 'Cont(inuous)', I don't want to increment the rank until the next 'Ind' record. This is so I can then get the min/max start/end date for that rank number.

I'm not sure if that's clear, but it's really obvious what I need to do with the attached SQL below (please see 'Desired' column). I'm guessing rank or dense_rank is the way forward, but I'm not sure what combination of columns to partition/order by?

If anyone could help it would make me smile. :)

Thank you.

create table mytable

(pid varchar(10),

startdate date,

enddate date,

blocktype varchar(4),

desired varchar (1));

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('23/10/2007','DD/MM/RRRR'),to_date('05/11/2007','DD/MM/RRRR'), 'ind',1);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('16/11/2007','DD/MM/RRRR'),to_date('19/11/2007','DD/MM/RRRR'), 'ind',2);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('26/11/2007','DD/MM/RRRR'),to_date('25/04/2008','DD/MM/RRRR'), 'cont',3);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('25/04/2008','DD/MM/RRRR'),to_date('28/08/2008','DD/MM/RRRR'), 'cont',3);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('01/04/2009','DD/MM/RRRR'),to_date('09/09/2009','DD/MM/RRRR'), 'ind',4);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('01/05/2010','DD/MM/RRRR'),to_date('03/05/2010','DD/MM/RRRR'), 'cont',5);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('03/05/2010','DD/MM/RRRR'),to_date('19/11/2010','DD/MM/RRRR'), 'cont',5);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('19/11/2010','DD/MM/RRRR'),to_date('06/02/2011','DD/MM/RRRR'), 'cont',5);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('29/04/2011','DD/MM/RRRR'),to_date('14/11/2011','DD/MM/RRRR'), 'ind',6);

Edited by: user9363122 on 28-Oct-2011 16:35

Could anyone help me out please?

I need to rank/order a set of data by date and period type (blocktype) for groups of worker records. Where the blocktype is 'Ind(ividual)', I need to increment the rank by 1. Where the type is 'Cont(inuous)', I don't want to increment the rank until the next 'Ind' record. This is so I can then get the min/max start/end date for that rank number.

I'm not sure if that's clear, but it's really obvious what I need to do with the attached SQL below (please see 'Desired' column). I'm guessing rank or dense_rank is the way forward, but I'm not sure what combination of columns to partition/order by?

If anyone could help it would make me smile. :)

Thank you.

create table mytable

(pid varchar(10),

startdate date,

enddate date,

blocktype varchar(4),

desired varchar (1));

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('23/10/2007','DD/MM/RRRR'),to_date('05/11/2007','DD/MM/RRRR'), 'ind',1);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('16/11/2007','DD/MM/RRRR'),to_date('19/11/2007','DD/MM/RRRR'), 'ind',2);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('26/11/2007','DD/MM/RRRR'),to_date('25/04/2008','DD/MM/RRRR'), 'cont',3);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('25/04/2008','DD/MM/RRRR'),to_date('28/08/2008','DD/MM/RRRR'), 'cont',3);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('01/04/2009','DD/MM/RRRR'),to_date('09/09/2009','DD/MM/RRRR'), 'ind',4);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('01/05/2010','DD/MM/RRRR'),to_date('03/05/2010','DD/MM/RRRR'), 'cont',5);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('03/05/2010','DD/MM/RRRR'),to_date('19/11/2010','DD/MM/RRRR'), 'cont',5);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('19/11/2010','DD/MM/RRRR'),to_date('06/02/2011','DD/MM/RRRR'), 'cont',5);

insert into mytable (pid, startdate,enddate,blocktype, desired) values ('A123', to_date('29/04/2011','DD/MM/RRRR'),to_date('14/11/2011','DD/MM/RRRR'), 'ind',6);

Edited by: user9363122 on 28-Oct-2011 16:35

- 4388 Views
- Tags: none (add)