Just another day I received following question from my friend and I find it very interesting. I decided to try it out on SQL Server. The question was – “Can we apply Dense_Rank function order by some Column but we should add where filter while assigning rank? ”
Interesting, isn’t it? So i took this as challenge and try to resolve this requrement as puzzle . So i took below small piece of paper and done some pseudo code. Then developed coding to get the final output
--drop table #TEMPCASE
DECLARE @StudentDetail TABLE
(
-- ID int identity(1,1),
StudentCode VARCHAR(10),
SchoolCode VARCHAR(10),
StartDate DATE
)
INSERT INTO @StudentDetail VALUES ('C00001', 'P00001', '1-Sep-2014')
INSERT INTO @StudentDetail VALUES ('C00001', 'P00002', '1-Oct-2014')
INSERT INTO @StudentDetail VALUES ('C00001', 'P00003', '10-Oct-2014')
INSERT INTO @StudentDetail VALUES ('C00001', 'P00004', '25-Oct-2015')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2014')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2014')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00001', '1-Oct-2015')
INSERT INTO @StudentDetail VALUES ('C00002', 'P00002', '1-Nov-2015')
INSERT INTO @StudentDetail VALUES ('C00003', 'P00002', '1-Nov-2015')
select * from @StudentDetail
select ROW_NUMBER() over(order by (StudentCode)) as ID,
ROW_NUMBER() over(partition by StudentCode order by StartDate) as RowID,cast(Null as int) as RankID,*
INTO #TEMPCASE from @StudentDetail
order by 1
--SELECT * FROM #TEMPCASE
declare @i int=1,@count int=(select MAX(RowID) from #TEMPCASE)
declare @RankID int
while @i<=@count
Begin
set @RankID=(select MAX(isnull(RankID,0)) from #TEMPCASE)
--IF exists(SELECT 1 FROM #TEMPCASE where RankID is null)
-- CONTINUE
-- ELSE
-- BREAK
update tt
set RankID=@RankID+t.RankID
from #TEMPCASE tt
join (
select *,DENSE_RANK() over(order by StudentCode) as RankID
from
(
select distinct t1.ID,t1.StudentCode from #TEMPCASE t1
join #TEMPCASE t2
on t1.StudentCode=t2.StudentCode
where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1
and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90
union all
select distinct t2.ID,t2.StudentCode from #TEMPCASE t1
join #TEMPCASE t2
on t1.StudentCode=t2.StudentCode
where t1.RankID is null and t2.RankID is null and t1.RowID=1 and t2.RowID>1
and DATEDIFF(dd,t1.StartDate,t2.StartDate)<=90
union all
select distinct t1.ID,t1.StudentCode from #TEMPCASE t1
where t1.RankID is null and not exists (select 1 from #TEMPCASE t2 where t1.StudentCode=t2.StudentCode
and t2.RankID is null
group by t2.StudentCode having COUNT(*)>1)
) t
) t on tt.ID=t.ID
update t1
set RowID=t.RowID
from #TEMPCASE t1
join(
select ID, StudentCode,ROW_NUMBER() over(PARTITION by StudentCode order by StartDate ) as RowID from #TEMPCASE
where RankID is null
) t
on t.ID=t1.ID
set @i=@i+1
End
SELECT * FROM #TEMPCASE
