use Omniture  select VLR.YearMonth ,VLR.VisitorID ,VLR.ConsultantID ,VLR.Visits ,CL.LevelID ,CL.[Status] ,IfNew = case when  (VLR.YearMonth=startmonth) then ‘1‘ else ‘0‘ end into #VisitsLogin from VisitsLoginRecord VLR  inner join (select *,CONVERT(varchar(6),StartDate,112) as startmonth  from ConsultantInfo) vstime on VLR.ConsultantID=vstime.ConsultantID inner join ConsultantLevel CL on VLR.ConsultantID=CL.ConsultantID and VLR.YearMonth=CL.YearMonthselect COUNT(*) from #VisitsLogin--ALL访问记录与等级记录关联select V.YearMonth,V.VisitorID,V.ConsultantID,v.Visits,V.ifnew,CL.LevelID,CL.[Status]into #temp1from #VisitsLogin Vinner join dbo.[ConsultantLevel] CLon V.ConsultantID=CL.ConsultantIDand V.YearMonth=CL.YearMonth--New访问记录与等级记录关联select V.YearMonth,V.VisitorID,V.ConsultantID,v.Visits,V.ifnew,NCL.LevelID,NCL.[Status]into #tempnew1from #VisitsLogin Vinner join dbo.[ConsultantLevel] NCLon V.ConsultantID=NCL.ConsultantIDand V.YearMonth=NCL.YearMonthand V.ifnew=1--Old访问记录与等级记录关联select V.YearMonth,V.VisitorID,V.ConsultantID,v.Visits,V.ifnew,OCL.LevelID,OCL.[Status]into #tempold1from #VisitsLogin Vinner join dbo.[ConsultantLevel] OCLon V.ConsultantID=OCL.ConsultantIDand V.YearMonth=OCL.YearMonthand V.ifnew<>1--ALL访问顾问人数select YearMonth,COUNT(distinct ConsultantID) as ConNumfrom #temp1group by YearMonth--New访问顾问人数select YearMonth,COUNT(distinct ConsultantID) as ConNumfrom #tempnew1group by YearMonth--Old访问顾问人数select YearMonth,COUNT(distinct ConsultantID) as ConNumfrom #tempold1group by YearMonth--All独立设备访问顾问数select * into #temprank1from (select YearMonth,VisitorID,ConsultantID,ifnew,rank() over (partition by VisitorID order by CONVERT(int,LevelID) desc ) as Ranknom from #temp1) as st where st.Ranknom=1  select YearMonth ,COUNT(distinct ConsultantID) from #temprank1 group by YearMonth--New独立设备访问顾问数select * into #tempnewrank1from (select YearMonth,VisitorID,ConsultantID,ifnew,RANK() over (partition by VisitorID order by CONVERT(int,LevelID) desc ) as RankNum from #temp1 ) as st where st.RankNum=1 and st.ifnew=1  select YearMonth ,COUNT(distinct ConsultantID) from #tempnewrank1 group by YearMonth--Old独立设备访问顾问数select * into #tempoldrank1from (select YearMonth,VisitorID,ConsultantID,ifnew,rank() over (partition by VisitorID order by CONVERT(int,LevelID) desc ) as RankNum from #temp1 ) as st where st.RankNum=1 and st.ifnew<>1 select YearMonth ,COUNT(distinct ConsultantID) from #tempoldrank1 group by YearMonth