Link to home
Start Free TrialLog in
Avatar of snguyen888
snguyen888

asked on

Ranking by Group

Hi All,

I have a question regarding to the ranking by consecutive grouping.  I try to avoid using loop since it a huge database and it slow.  Please let me know if it possible.  Here is what I am looking for:

Current

VisitID      ServiceStartDate      ServiceDesc
100      9/27/12 4:34      Hospitalist
100      9/27/12 16:21      Hospitalist
100      9/29/12 10:42      CCU
100      9/29/12 12:42      CCU
100      10/2/12 0:32      Cardiology Firm
100      10/2/12 2:17      Hospitalist
100      10/2/12 5:09      Hospitalist
100      10/2/12 6:53      Hospitalist

Desire
VisitID      ServiceStartDate      ServiceDesc      Ranking
100      9/27/12 4:34      Hospitalist      1
100      9/27/12 16:21      Hospitalist      1
100      9/29/12 10:42      CCU                        2
100      9/29/12 12:42      CCU                        2
100      10/2/12 0:32      Cardiology Firm      3
100      10/2/12 2:17      Hospitalist      4
100      10/2/12 5:09      Hospitalist      4
100      10/2/12 6:53      Hospitalist      4

Thanks!
Avatar of mplomin
mplomin

You can use DENSE_RANK()

SELECT visitID,ServiceStartDate,ServiceDesc,
DENSE_RANK() OVER (  ORDER BY cast(DATEADD(DAY, 0, DATEDIFF(DAY, 0, ServiceStartDate)) as varchar(50)) + ServiceDesc )
FROM ...

edit: it works if ServiceStartDate is a datetime type
Avatar of Tony303
I came close with this...
Problem is the ranking

create table thetable (VisitID int, ServiceStartDate datetime, ServiceDesc varchar(50))

insert thetable values (100, '9/27/12 4:34','Hospitalist')
insert thetable values (100, '9/27/12 16:21', 'Hospitalist')
insert thetable values (100, '9/29/12 10:42', 'CCU')
insert thetable values (100, '9/29/12 12:42', 'CCU')
insert thetable values (100, '10/2/12 0:32','Cardiology Firm')
insert thetable values (100, '10/2/12 2:17', 'Hospitalist')
insert thetable values (100, '10/2/12 5:09', 'Hospitalist')
insert thetable values (100, '10/2/12 6:53', 'Hospitalist')

SELECT *
FROM thetable

with t as (
 select VisitID, ServiceStartDate, ServiceDesc, DENSE_RANK() OVER (ORDER BY ServiceDesc) as rn
 from (select distinct VisitID, ServiceStartDate,  ServiceDesc from thetable) t1 )
select VisitID,  ServiceStartDate, ServiceDesc ,rn
from t
group by VisitID, ServiceStartDate, ServiceDesc, rn
order by VisitID, ServiceStartDate
mplomin's suggestion also has the same problem as the ranking

If you still use my "thetable" example.

SELECT visitID,ServiceStartDate,ServiceDesc,
DENSE_RANK() OVER (  ORDER BY cast(DATEADD(DAY, 0, DATEDIFF(DAY, 0, ServiceStartDate)) as varchar(50)) + ServiceDesc )
FROM thetable
ORDER BY VisitID, ServiceStartDate
Avatar of snguyen888

ASKER

Thanks mplomin & Tony303 for your help.  It partial working only when it a same ServiceStartDate.  I have another scenerio, I want to count it in a group if the different 1 days for less.  Here is the sample data that different day but I still count in a group unless it more than 1 day apart.  

Current

VisitID      ServiceStartDate      ServiceDesc
100      9/27/12 4:34      Hospitalist
100      9/28/12 16:21      Hospitalist
100      9/29/12 10:42      CCU
100      9/29/12 12:42      CCU
100      10/2/12 0:32      Cardiology Firm
100      10/2/12 2:17      Hospitalist
100      10/3/12 5:09      Hospitalist
100      10/4/12 6:53      Hospitalist

Desire
VisitID      ServiceStartDate      ServiceDesc      Ranking
100      9/27/12 4:34      Hospitalist      1
100      9/28/12 16:21      Hospitalist      1
100      9/29/12 10:42      CCU                        2
100      9/29/12 12:42      CCU                        2
100      10/2/12 0:32      Cardiology Firm      3
100      10/2/12 2:17      Hospitalist      4
100      10/3/12 5:09      Hospitalist      4
100      10/4/12 6:53      Hospitalist      4

---------------------------------------------------
temp table for using:

create table #thetable (VisitID int, ServiceStartDate datetime, ServiceDesc varchar(50))

insert #thetable values (100, '9/27/12 4:34','Hospitalist')
insert #thetable values (100, '9/28/12 16:21', 'Hospitalist')
insert #thetable values (100, '9/29/12 10:42', 'CCU')
insert #thetable values (100, '9/29/12 12:42', 'CCU')
insert #thetable values (100, '10/2/12 0:32','Cardiology Firm')
insert #thetable values (100, '10/2/12 2:17', 'Hospitalist')
insert #thetable values (100, '10/3/12 5:09', 'Hospitalist')
insert #thetable values (100, '10/4/12 6:53', 'Hospitalist')
------------------------------

Have any idea that might work?  Thank you so much for your help
alter table #thetable
add ranking int

declare @ServiceStartDate datetime,@ServiceDesc varchar(50)
declare @rank int =1
declare @sd varchar(50)

select @sd=ServiceDesc from #thetable
declare cr cursor
for select ServiceStartDate,ServiceDesc from #thetable
for update

open cr
fetch next from cr into @ServiceStartDate,@ServiceDesc

while @@FETCH_STATUS=0

begin
      if @ServiceDesc<>@sd
      set @rank=@rank + 1
UPDATE #thetable
SET Ranking=@rank
WHERE current of cr

set @sd=@ServiceDesc
fetch next from cr into @ServiceStartDate,@ServiceDesc
end
close cr
deallocate cr
Hi mplomin,

Thank you so much for your help.  It very close what I am looking for.  I am not good with the loop, can you help me with last thing.  Since the real data will have different VisitID, can you help me linking the right VisitID.  I add two rows of data with different VisitID.
----------------------------------------------------------------------------
Here is the temp table that I using for testing:

create table #thetable (VisitID int, ServiceStartDate datetime, ServiceDesc varchar(50))

insert #thetable values (100, '9/27/12 4:34','Hospitalist')
insert #thetable values (100, '9/28/12 16:21', 'Hospitalist')
insert #thetable values (100, '9/29/12 10:42', 'CCU')
insert #thetable values (100, '9/29/12 12:42', 'CCU')
insert #thetable values (100, '10/2/12 0:32','Cardiology Firm')
insert #thetable values (100, '10/2/12 2:17', 'Hospitalist')
insert #thetable values (100, '10/3/12 5:09', 'Hospitalist')
insert #thetable values (100, '10/4/12 6:53', 'Hospitalist')
insert #thetable values (101, '10/1/12 6:53', 'Hospitalist')
insert #thetable values (101, '10/2/12 2:53', 'Hospitalist')
--------------------------------------------------------------------------------
Desire output:
100      9/27/12 4:34      Hospitalist      1
100      9/28/12 16:21      Hospitalist      1
100      9/29/12 10:42      CCU      2
100      9/29/12 12:42      CCU      2
100      10/2/12 0:32      Cardiology Firm      3
100      10/2/12 2:17      Hospitalist      4
100      10/3/12 5:09      Hospitalist      4
100      10/4/12 6:53      Hospitalist      4
101      10/1/12 6:53      Hospitalist      1
101      10/2/12 6:53      Hospitalist      1

--------------------------------------------------------------
Again, thank you so much for your help.
ASKER CERTIFIED SOLUTION
Avatar of mplomin
mplomin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Wow, you are super smart.  Thank you so much for your help.
Thanks mplomin!