SQL SERVER – DENSE_RANK() – ORDER BY SOME COLUMN BUT WE SHOULD ADD WHERE FILTER WHILE ASSIGNING RANK

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  

• email
• facebook
• linkedin
• twitter
• reddit
• google+

Kiran Reddy A

Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL

Get Free Email Updates to your Inbox!

• Rss
• Twitter
• FaceBook
• Google +